Set shell variables from SQLPLUS query results

UNIX for Advanced & Expert Users
1

Hi All,

I needed to get the result of two sqlplus queris into shell variables.

After days of looking for the ultimate solution to this problem.. i found this...

sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ",
$1);}' | read VAR1 VAR2
set head off pagesize 0 echo off verify off feedback off
select name from v$database
/
select log_mode from v$database
/
EOF

from here...

I did not understand the logic used in the above peice of code. As far I was concerned, my objective was achieved. But then I saw perils of under-understanding! Knowledge shouldn't be a mission, it should be a journey!

The problem I am facing now is that, I now need to get just one value out of the sqlplus connection. So, i just simply tried..

sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1);' | read VAR1
set head off pagesize 0 echo off verify off feedback off
select name from v$database
/
EOF

But to no avail.. anybody willing to help me out on this, will be heartfully appreciated.

Thanks!
Pranav

2

Try this:

var=$(sqlplus  -s USER/PASS@DB <<EOF | tail -1
set feedback off;
select name from v\$database;
EOF
)
3

Or:

dbname=$(sqlplus -s '/ as sysdba'<<\!
set pages 0 feed off
select name from v$database;
!
)

With recent bash (>=3 IIRC):

printf -vdbname 'set pages 0 feed off\nselect name from v$database;' | 
  sqlplus -s '/ as sysdba'