Need to Capture Sql Failures

Hi guys,

I am inserting and updating records in Oracle database using shell Script based on business logic.

Now Whenever the insert or Update fails, i want to capture it.

i tried to capture the return code ($?), but it is not reflecting it.

####################################################################
##  Insertion of  Traking ID
####################################################################

sqlplus -s PASS/USERID@DB << EOF
+ sqlplus -s PASS/USERID@DB INSERT INTO TRACKING VALUES (sequencer.NEXTVAL,'GME','MEXICO','NA',current_date)
            *
ERROR at line 1:
ORA-00947: not enough values
 
ReturnCode=$?
+ ReturnCode=0
if (( $ReturnCode!=0 ))
        then
        echo "Prevalidation script Failed during insertion in PCM_BATCH_TRACKING table" | mail -s "Prevalidation Script Failure" xxx@xxx.com;
        exit 3;
fi
+ ((  0!=0  ))

I purposely did it inorder to check whether it is capturing or not.

Can somebody help me out.

Cheers!!!!!

Well, the sqlplus command itself has succeeded!

You could use WHENEVER SQLERROR EXIT <code> , e.g.

# sqlplus -s PASS/USERID@DB << EOF
> WHENEVER SQLERROR EXIT SQL.SQLCODE
> SELECT * from notable;
> EOF
SELECT * from notable
              *
ERROR at line 1:
ORA-00942: table or view does not exist


# echo $?
174

I generally work with both solutions:
-----> Use: "WHENEVER SQLERROR EXIT 1" (as commented by Carlo)
-----> Redirect SQLPLus' output to a file and validate the error.
-----> Also use: "sqlplus -S -L" to try to login just once.

sqlOutputFile="./sqlplusExec.log"
# sqlplus -S -L PASS/USERID@DB << EOF > "${sqlOutputFile}"
     WHENEVER SQLERROR EXIT 1
     SELECT * from notable;
EOF
sqlplusRetCode=${?}

countErrors=`egrep -c 'ORA-[0-9]{4}|SP2-|TNS-|Usage' "${sqlOutputFile}"`

if [ ${sqlplusRetCode} -ne 0 -o ${countErrors} -ne 0 ]
then
     echo "Found errors while executing SQLPlus[${sqlplusRetCode}][${countErrors}]: "
     egrep 'ORA-[0-9]{4}|SP2-|TNS-|Usage' "${sqlOutputFile}"
     exit 1
fi

I hope it helps!

Thanks guys...