topic Re: Store DB value in Operating System - HP-UX

Store DB value

Prabhu_7 — Mon, 16 Jun 2003 16:07:22 GMT

i'm doing lot of process in my shell script and
in middle of my script, i need to check value of a
column in database table and depends on the value i need to
proceed with my shell script process....

for eg...

Myshell.sh

...
..
...
...

Sqlplus .... check.sql (helps to fetch value from database and store it in Local_Var)

If Local_Var = 'A'
Do 'Task A'
Elsif Local_Var = 'B'
Do 'Task B'
fi.

******* How to store database column value in Local_Var ?

Re: Store DB value

Hai Nguyen_1 — Mon, 16 Jun 2003 16:14:07 GMT

You can try this

Local_Var=`sqlplus .... check.sql`

Note that the quotes are back-quotes.

Hai

Re: Store DB value

Umapathy S — Mon, 16 Jun 2003 16:20:03 GMT

Prabhu,
You can also store/write/redirect the value in a temp file and access the value from there in your shell script.

HTH,
Umapathy

Re: Store DB value

Prabhu_7 — Mon, 16 Jun 2003 16:32:35 GMT

I know to store it in temp file and access from there....is there no way to store directly into a variable ???

Re: Store DB value

Umapathy S — Mon, 16 Jun 2003 16:54:28 GMT

Prabhu,
The shell script understand its own environment. The sql file you create can be understood and executed only by database clients which is a different process altogether.

You can do, provided the sqlplus shoud write to stdout and you can grep/cut/awk that output to get the exact value in the local_var like previously mentioned.

HTH,
Umapathy

Re: Store DB value

Prabhu_7 — Tue, 17 Jun 2003 14:56:19 GMT

Do you have a sample code to do the same ?

Re: Store DB value

Umapathy S — Tue, 17 Jun 2003 15:08:36 GMT

Prabhu,

I dont work in Oracle. But I work in Informix. Follwing is a sql file which selects a value from a table.

$ cat mytemp.sql
select field1 from table_a where field2=1111;

Here is the part of shell script which actually does the job.

$ mytemp.sh
local_var=`dbaccess db1 mytemp.sql`
next_var=`echo $local_var|awk '{ print $2 }'`
echo "here from the shell" $next_var

where dbaccess is the equivalent of sqlplus.

HTH,
Umapathy