Return code from sqlplus session in Shell script

if you execute a sqlplus block inside a shell script and you need to perform different actions based on its return code. However no metter wheather the sqlplus session terminates with success or failures, its return code always equals 0 which means a successful result. It can be demostrated as:

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" >> ${LogFile} << EOF
startup mount restrict;
exit;
EOF
rc=$?
echo returncode=$rc

the output:

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 11 11:32:41 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/u01/app/oracle/admin/STDBY10G/pfile/spfileSTDBY10G.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> Disconnected

returncode=0

Solution

“whenever sqlerror exit sql.sqlcode” can be used to terminate a sqlplus session and return an error code, similarly, “whenever oserror exit oscode” returns OS error code. These options are valid since Oracle 9i.
An example:

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" >> ${LogFile} << EOF
whenever oserror exit oscode
whenever sqlerror exit sql.sqlcode
startup mount restrict;
exit;
EOF
echo returncode=$?

the output:


SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 11 11:43:57 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> SQL> ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/u01/app/oracle/admin/STDBY10G/pfile/spfileSTDBY10G.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Disconnected
Returncode=54

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: