Storing the value of a single query to a Variable - What am i doing wrong?

Shell Programming and Scripting
1

I tried the below :

 
#!/bin/ksh
testvar=$(
sqlplus $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
COMMIT;
EXIT;
EOF)
echo "testvar="$testvar

The output that I am getting is :

 
> sh example.sh
+ sh example.sh
testvar=

Honestly, what am I doing wrong ?? Kindly help!

Cheers.

2

try

#!/bin/ksh
testvar=`sqlplus $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
COMMIT;
EXIT;
EOF`
echo "testvar="$testvar
3

COMMIT and EXIT are not needed. You need the commit statement to "confirm" data manipulation statements like INSERT, UPDATE, DELETE,...
SQL*Plus should be called in silent mode so you do not get its banner in your output.
The end of your here document should be the only characters in that line, put the closing brace in the next line.
You specify a shell in the shebang but call the script using sh. To my knowledge the shebang is ignored when you call a script like this.

testvar=$(
sqlplus -s $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
EOF
)
echo "testvar="$testvar
5

Many thanks for all replies folks.

But i finally figured out what was wrong. The correct code is :

 
#!/bin/ksh
testvar=$(
sqlplus $user/$pass << EOF 
set pagesize 0 feedback off verify off heading off echo off;
whenever sqlerror exit 1
SELECT 1+1 FROM DUAl;
COMMIT;
EXIT;
EOF)
echo "testvar=$testvar"

I should have enclosed the

$testvar

in double quotes, as in

"$testvar"

Again many thanks for all the inputs.

Cheers.:b: