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…