How to store result from SQLPlus to a shell variable

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