Unix Tip: OUT PARAMETER FROM ORACLE

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

UNIX GURU UNIVERSE
UNIX HOT TIP

Unix Tip 3022 - May 16, 2010

http://www.ugu.com/sui/ugu/show?tip.today

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

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=< type in your Oracle Home Path >
export ORACLE_SID=< Mention the Oracle SID >
export PATH=$ORACLE_HOME:$ORACLE_HOME/bin:$PATH # This Path is set to access the sqlplus executable
dummyvar=`sqlplus -s tcon4iqalib/tcon4iqalib <<end
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