My colleague asked me for script that executes SQL*Plus and check whether some problem occured. Here is a solution…

This is sample script:

  sqlplus scott/tiger@yourdb <<EOF >/dev/null WHENEVER SQLERROR EXIT 1 DECLARE x VARCHAR2(20); BEGIN   -- this statement pass OK   SELECT * INTO x FROM DUAL;   -- this statement causes error, remove it when you want to see message 'Ok'   RAISE_APPLICATION_ERROR(-20000, 'Script failed...'); END; / EOF

 if [ $? -eq 1 ] ; then   echo Script failed...   # here you can send email, do anything else   mail -s "Error occured" tom@test.com <<EOF Error occured running SQL Script....   EOF else   # this is OK   echo Ok fi 

Script is easy to understand. On beginning of the SQL*Plus script you use WHENEVER SQLERROR EXIT <code> and this code you check later using shell’s built in variable $?

Parameter -s of mail contains subject of email, between row mail -s "Error occured" tom@test.com <<EOF and EOF is text of the message…