Tip for the day: OUT PARAMETER FROM ORACLE

Ever wanted to execute an Oracle Procedure which contains an out parameter, and use the value returned from the out parameter in the shell variable.

Try out this. The procedure test1 adds two nos. and returns output into the out parameter.

export ORACLE_HOME=<>
export ORACLE_SID=<>
export PATH=$ORACLE_HOME:$ORACLE_HOME/bin:$PATH # This Path
is set to access the sqlplus executable
dummyvar=`sqlplus -s tcon4iqalib/tcon4iqalib <
set pagesize 0 feedback off ver off heading off echo off
serverout on
variable verr_mesg number
exec test1(4,5,:VERR_MESG)
print verr_mesg
exit;
end`
echo " Error is " $dummyvar
echo " Result is " $dummyvar
echo $dummyvar
#end of shell script