Unix Tip: OUT PARAMETER FROM ORACLE

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

UNIX GURU UNIVERSE
UNIX HOT TIP

Unix Tip 3412 - June 13, 2011

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