Search This Blog

Monday, October 13, 2008

Store SQLPLUS output to a variable in Unix Shell Script

In response to one of the question asked, here is the solution of how to call sqlplus and store the output in a variable.
In the script below I am connecting to sqlplus through unix and querying fnd_lookup_values table and storing the output of query in a unix variable. This variable can then be later referred in the code using $.

#Suresh Vaishya:The script connects to sqlplus and stores output of a query into a unix variable.
#The output of query is seperated by '~' to identify different record values.

sqlplus -s $login <set heading off
set feedback off
SELECT lookup_code||'~' FROM fnd_lookup_values WHERE lookup_type ='AGREEMENT LINES' and enabled_flag = 'Y' and Nvl(end_Date_ac
tive,sysdate+1) > sysdate and language='US';
echo 'Output of SQL Query is '
echo $code
echo 'End of Script'

The script output is

Output of SQL Query is
End of Script

Related Post
Calling SQLPLUS from unix


Anonymous said...

Hi Suresh,
I am new to this blog and also to this topic of SQLPLUS and Shell Script.
When I was going thru "Store SQLPLUS output to a variable in Unix Shell Script",I couldnt get the unix variable name in which the output is stored and also location of the variable.
Please tell me that.
This would be helpful incase if we run many scripts immediately one after one and then finally to see the output of all the scripts.

Suresh Vaishya said...

The output is stored in variable code.

Anonymous said...

forget it, i worked it out.

everything between the 2 backticks is executed, making code=**the result of my sql**

clever and just what i needed.


Suresh Vaishya said...

Glad that it worked out for you.


Anonymous said...

Hi ,

I am facing problem when trying to call a procedure from shell script.
It is not getting called from shell script but the same works fine from SQL plus.

There is a out parameter too .
Guess thats creating problem. Do you have any idea about this .

Thanks in advance.

Suresh Vaishya said...

Share piece of that code. ..
Should not have any problem with the out paramter. In the sqlplus section in Unix, you can define as many SQL variables as you need .. but the scope of variable will be limited to that section.

Anonymous said...

Hi ,

The problem got resolved after using single quotes for the parameters.


Anonymous said...


I an trying below but out put is not a single value, but whole query execution. Could you please suggest how to correct it.


DB_VERSION="$(sqlplus "/ as sysdba" << EOF
set heading off feedback off verify off
select version from v\$instance;
print "Database Version is $DB_VERSION"

Output is:

Database Version is
SQL*Plus: Release - Production on Sat Jul 9 21:20:26 2016

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

Copyright (c) All rights reserved. Presented by Suresh Vaishya