How to store result from SQLPlus to a shell variable

shell unix sqlplus

83,213

Solution 1

Employ backticks:

testvar=`sqlplus foo/bar @test.sql`

or should that be of syntactical eyesore:

testvar=$(sqlplus foo/bar @test.sql)

You clearly know to take the right sql*plus commands to limit superfluous output, yes? :) and of course beware the backticking will collapse the whitespace of the output.

Solution 2

Try this instead:

testvar=`sqlplus -s foo/bar@SCHM <<EOF
set pages 0
set head off
set feed off
@test.sql
exit
EOF`

-s switch will turn off all the header info when sqlplus launches. You also want to turn off the feedback, headers, and pagesize to 0. I am old school so I still use the back ticks :)

Solution 3

The solutions here are all hacks.

Your sql file should look like this...

set termout off
set showmode off
set heading off
set echo off
set timing off
set time off
set feedback 0
set pagesize 0
set embedded ON
set verify OFF

spool courses.sh
SELECT 'term="' || sfrstcr_term_code || '";', 'subj="' || sfrstcr_subj_code || '";' FROM sfrstcr WHERE sfrstcr_pidm = 1234567;
spool off

The following shell script will read and print out the shell environment variables.

while read -r row; do
  eval "$row"
  echo "term=$term"; 
  echo "subj=$subj"; 
done < courses.sh

It's important that all the variables are on one line, as the read command ensures that you can read each DB row per loop.

83,213

Author by

Ankur Gupta

Updated on July 18, 2022

Comments

Ankur Gupta almost 2 years
My requirement is to store the result of an sqlplus operation into a variable in my shell script. I need the result of the following operation which is in my .sh file
```
sqlplus 'user/pwd' @test.sql
```
I have already tried
```
testvar = 'sqlplus 'user/pwd'
@test.sql'
```
but that doesn't work.

EDIT::

I changed it to
```
testvar=sqlplus foo/bar@SCHM @test.sql
```
and it says

SQL*Plus:: not found [No such file or directory]

I tried with
```
testvar=$(sqlplus foo/bar@SCHM
@test.sql)
```
and it gives the same error. When I try without the variable assignment like below
```
sqlplus foo/bar@schm @test.sql
```
it works fine
Roman Cheplyaka over 13 years

"the backticking will collapse the whitespace of the output" -- it won't. It will just remove trailing newlines. Also, there's nothing old-schoolish in using $(), it's rather the other way round.
SourceSeeker over 13 years

It's the other way around: backticks are "old school". The whitespace is not lost on assignment. It is lost on output if the variable isn't quoted.
Jé Queue over 13 years

Yes, you are correct about $() I did actually edit later to add the other option and forgot to switch around.
Ankur Gupta over 13 years

I made it testvar=sqlplus foo/bar@SCHM @test.sql and it says SQL*Plus:: not found [No such file or directory] i tried with testvar=$(sqlplus foo/bar@SCHM @test.sql) and it gives the same error. When I try without the variable assignment like sqlplus foo/bar@schm @test.sql it works fine
Jé Queue over 13 years

PATH and ORACLE_HOME exported?
agonen almost 10 years

should add verify off , in case of passing parameters
agonen almost 10 years

@trenton-d-adams termout off - mean no display to terminal . I think it better to leave it on , unless you use spool command
Nicolas de Fontenay over 9 years

Thanks. I was looking for a syntax like this. I didn't want to use a sql file.