How to store result from SQLPlus to a shell variab

Question

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

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

Answer 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 :)

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

Answer 4

As commands within $() are executed in a subshell, make sure you have everything you need for the invocation of sqlplus exported. Right now you obviously stumbled into the PATH issue.