Getting Database values into UNIX Shell

How to transfer the values from database tables to variables in shell script. Some of the options are discussed here. Case I

One option is to call the sqlplus and redirect the output to a file and then read the file.


sqlplus -s scott/tiger << _EOF_ > /tmp/database_values.txt
set feedback off
set echo off
set heading off
select tablespace_name from user_tables where table_name='EMPLOYEES';
exit;
_EOF_

Now read the values from the temporary file


cat /tmp/database_values.txt | while read line
do

shell_var1 = $line
done;

Case II

The other option is to directly assign the output to a shell variable. Here pass the SQL commands using a script file.



tsname=`sqlplus -s scott/tiger < get_ts.sql EMPLOYEES`
echo $tsname


The script for get_ts.sql is

set feedback off
set echo off
set heading off
select tablespace_name from user_tables where table_name='&1';
exit;
_EOF_