Shell Programming and Scripting

Hi all

Im trying to call a PL SQl block from a ksh file like this :

sqlplus -s $DB_USERID/$DB_PASSWD@$DB_NAME<<eof
whenever SQLERROR exit 1
var varError VARCHAR2(200);
exec ODAS_BATCH_JOBS_RETRIEVE.retrieve_user_info(:varError);
eof


If there is a error then varError will return a value other than Null....i.e the exception msg.

I wanted to check this condition something like

if(varError != Null) then
DISPLAY_ERROR_MSG


Is this possible thru ksh ??

Not sure if this will help you, I usually only test to see if an exception has occurred and I am not sure how to get the actual SQLCODE to return to the KSH script.

In the exception handler of the procedure / package I am calling I use the following:
RAISE_APPLICATION_ERROR(-20000, 'TEST' ||SQLERRM);

And my sql call looks like this in the KSH script:

sqlplus $ORACLE_USERID <<EOF
WHENEVER SQLERROR EXIT FAILURE
BEGIN
test_proc;
END;
/
EXIT SQL.SQLCODE
EOF

if [ $? -ne 0 ]
then
echo ERROR
fi