Capture both sql result and status code in shell script 2

[akelabanda]

Hi There

I'm running an sql query from within a shell script.

We use a different exit code to denote SUCCESS (99) rather than 0.

The code is something like this (assuming only 1 row is returned) -
-------------------------------------
temp_percent="set pages 0
set head off
whenever sqlerror exit sql.sqlcode;
select name from Test_table where age=100;
/
exit 99"
age=$(print "${temp_percent}"|sqlplus -s scott/tiger@testdb)
-------------------------------------
My problem is when 'age' gets assigned with the value from the query, the shell would return zero for $? rather than the sql exit code.

Is there a way to get both the result and the exit code from the sql query into the shell script.

Many thanks in advance for your responses.

Rajeev

UK's best mobile deals online

http://www.bargainmobileshop.com

 

[Dagon]

How about assigning $? to a variable immediately after the exit from sqlplus. Then you have it saved and can query it as much as you like.

 

[akelabanda]

Hi Dagon

Did think about it. But like to avoid running the query twice. Will have that as an option if nothing else works out.

Thanks
Raj

UK's best mobile deals online

http://www.bargainmobileshop.com

 

[Dagon]

Why are you doing it like that, anyway ? Most people use this approach:

sqlplus -s scott/tiger@testdb << EOF
set pages 0
set head off
whenever sqlerror exit sql.sqlcode;
select name from Test_table where age=100;
/
exit 99
EOF
if [ $? -eq 99 ]
then
...

 

[akelabanda]

Well, some people claim that sqlplus does not return ZERO for SUCCESS always. So, our guys decided they are going to use 99 as our success code. Lot of other stuff depend on the exit status of 99.

Secondly, in this case, I would also like to store the result of the query in a shell variable.

UK's best mobile deals online

http://www.bargainmobileshop.com

 

[Dagon]

This seems to work OK:

var=`sqlplus -s xxxxx/xxxxx@xxxxx << EOF
spool junk.dat
set pages 0
set head off
set feedback off
whenever sqlerror exit sql.sqlcode;
select 'result' from dual
/
spool off
exit 99
EOF
`
if [ $? -eq 99 ]
then
echo $var
else
echo Failed with $?
fi

 

[stefanhei]

Remember that $(...) opens a subshell, so the returnvalue you got is correct, as the subshell terminated without an error. If you like to return the sqlpluscode you have to tell the subshell to do so:

#!/bin/ksh
temp_percent="set pages 0
set head off
whenever sqlerror exit sql.sqlcode;
select name from Test_table where age=100;
/
exit 99"
age=$(print "${temp_percent}"|sqlplus -s scott/tiger@testdb ; return $?)

Stefan

 

[akelabanda]

Brilliant. Both work perfectly well.

Appreciate your helpw ith this.

Thanks a lot.

Regards
Raj

UK's best mobile deals online

http://www.bargainmobileshop.com