Set shell variables from SQLPLUS query results

pranavagarwal · #1 (**permalink**) 09-01-2008

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...

Code:

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..

Code:

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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Oracle Query results to be stored in variables	ashokjaysiv	Shell Programming and Scripting	4	03-16-2009 10:45 AM
SQLPLUS unable to execte query automatically from solaris script	jyotisree	SUN Solaris	2	08-11-2008 06:03 AM
SQLPLUS and update statements using bind variables	Nomaad	UNIX for Advanced & Expert Users	3	08-19-2007 03:55 AM
sql query results in unix shell script	skyineyes	UNIX for Dummies Questions & Answers	1	06-20-2007 10:56 AM
How to pass Shell variables to sqlplus use them as parameters	Jtrinh	Shell Programming and Scripting	1	07-13-2005 04:15 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode	Rate This Thread:


	Powered by