how to get the return value from oracle function in shell script

[rk0000]

I have a shell script like this:

sqlplus -s $USER/$PASS << ENDSQL #1> /dev/null 2>&1
set define off
set head off
whenever sqlerror exit sql.sqlcode
select fn_get_current_date_for_region('USA') from dual; ENDSQL

I want to capture the return value of the oracle function fn_get_current_date_for_region('USA') in the shell script. How can i do this?
Thanks...

 

[sbix]

It depends ... you can put all the instructions in a script and run it like:
x=`script_name`
In the $x var you will have the values echoed by the PL/SQL script

 

[Ogzilal]

Hi,

Just after running your sqlplus, test the standard shell variable $? that contains the value exited from sqlplus.

# sqlplus -s $USER/$PASS << ENDSQL #1> /dev/null 2>&1
set define off
set head off
whenever sqlerror exit sql.sqlcode
select fn_get_current_date_for_region('USA') from dual; ENDSQL
# RETURN_CODE=$?
# if [ $RETURN_CODE -ne 0 ];then print&quot;Error&quot; ; fi

 

[sbix]

Dear rk0000,
do you wish to capture the output of the function or the exit code of sqlplus???

 

[dickiebird]

try this one-liner (untested) :-
usdate=&quot;select fn_get_current_date_for_region('USA') from dual&quot; | sqlplus -s userid/userpasswd

Dickie Bird (-)))