There are many notes with extensive review on this topic at Oracle support’s metalink website. Please see the list of references at the end of this blog. In this blog, I am only focused on how to evaluate the execution result of a PL/SQL procedure within a shell script. One practical use of this is to check the status of an application, as many of the applications primarily call PL/SQL procedures to interact with Oracle database.
I found the following two methods that are useful.
1. Use “whenever sqlerror exit 1” to check if there is a problem:
#!/usr/bin/sh
sqlplus scott/tiger@${ORACLE_SID} << EOF
whenever sqlerror exit 1
exec ‘some PLSQL procedure’
EOF
ABC=$?
if [[ $ABC = 1 ]];
then
‘do something and send out an email…’
else
‘do something else…’
fi
This method is simple. But many things could set exit status to 1. For example, you may have problem with SQL*Plus, database user account permission, etc. But if you knew the only thing that could go wrong is the execution of PLSQL procedure, this is still a viable choice.
2. Use nested block along with sql.sqlcode. The SQLCODE returns code of the most recent operation in SQL*Plus. You have a lot more control on what exit status you want to return to shell script. Here is a example:
(Note: even for the exceptions that are Oracle predefined exceptions, the following code can still capture the value of SQLCODE. Tested in 10gR2.)
#!/usr/bin/sh
sqlplus -s scott/tiger@${ORACLE_SID} << EOF
set serverout on
variable set_flag number;
DECLARE
sql_code number := 0 ;
my_errm VARCHAR2(32000);
BEGIN
BEGIN
‘some PLSQL procedure’;
EXCEPTION
WHEN OTHERS THEN
sql_code := SQLCODE;
my_errm := SQLERRM;
END;
dbms_output.put_line('Error message is: 'my_errm);
IF sqlcode !=0 THEN
:set_flag := 4;
END IF;
END;
/
exit :set_flag
EOF
#It is good idea to capture the exit status by assigning it to a variable, in case you need to use it multiple time later on in the script.
ABC=$?
if [[ $ABC = 1 ]]; then
your SQL*Plus failed to run successfully for some reason, do something such as sending an email, write to log file, etc.…
elif [[ $ABC = 4 ]]; then
the PLSQL procedure failed, do something about this, send email, write to log file, etc.….
Else
there is no problem…
fi
References:
(1) metalink note # 351015.1 How To Pass a Parameter From A UNIX Shell Script To A PLSQL Function Or Procedure.
(2) metalink note # 400195.1 How to Integrate the Shell, SQLPlus Scripts and PLSQL in any Permutation?
(3) metalink note# 73788.1 Example PL/SQL: How to Pass Status from PL/SQL Script to Calling Shell Script
(4) metalink note # 6841.1 Using a PL/SQL Variable to Set the EXIT code of a SQL script
(5) http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/13_elems049.htm
(6) Oracle magazine: On Avoiding Termination By Steven Feuerstein
http://www.oracle.com/technology/oramag/oracle/09-mar/o29plsql.html
