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);
-- 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...');
if [ $? -eq 1 ] ; then
echo Script failed...
# here you can send email, do anything else
mail -s "Error occured" firstname.lastname@example.org <<EOF
Error occured running SQL Script....
# this is OK
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 $?
mail -s "Error occured" email@example.com <<EOF and
EOF is text of the message…