TOMÁŠ HUBÁLEK BLOG: BAVTE SE PŘIMĚŘENĚ…

How to execute sqlplus from shell script and check whether some error occured.

Written By: Tomáš Hubálek - Oct• 22•04

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…

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

Your email address will not be published. Required fields are marked *