UNIX for Dummies Questions & Answers

Hi. I need sime help with a unix script. The script runs a Oracle Procedure which then runs
some child procedures which are autonomous.
When the child procedures fail, the sql error is not passed back to the parent procedure -
i.e all the othert child procedures run.
I want to execute the parent proc using a unix script and capture the fact that a child proc
has failed. I do this by returning a status code of 0 or -1 from the child proc and use that
in teh parent proc to determine a failure. I need to capture this out parameter from
the parent proc and pass it to a Unix variable.
My sample code is below:

#! /usr/bin/ksh
export ORACLE_HOME=/apps/oracle/product/10.2.0.4
export ORACLE_SID=test
cd $ORACLE_HOME/bin
sqlplus user/pwd@$ORACLE_SID << END_OF_SQL
whenever sqlerror exit 1
set serveroutput on;
DECLARE
P_RUNDATE DATE;
v_STATUS DATE;

begin
P_RUNDATE := TO_DATE('$1','DD/MM/YYYY');
PKG_TEST.TEST2(P_RUNDATE => P_RUNDATE,P_STATUS => v_STATUS);
end;
/
exit;
END_OF_SQL
$retcode=$?
echo $retcode
print end

Any help will be appreciated.