Wednesday, March 5, 2008

How to pass a value from Oracle to Unix shell environment variable?

Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.
Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0

{{SELECT STMT}};
exit
EOF`


For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" << EOF
> set echo off verify off feed off termout off pages 0
> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8
> from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes
> from dba_data_files
> group by tablespace_name) tot,
> dba_tablespaces tbs
> where tot.tablespace_name = tbs.tablespace_name
> and fre.tablespace_name(+) = tbs.tablespace_name
> group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
> /
> exit
> EOF`

-bash-3.00$ echo $DB_USED
4189795.79

This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.


2 comments:

anil said...

Thanks Sachin, It is usefull post. u cleared my doubt :)

anil said...

Thanks Sachin, It s usefully post.