Sunday, June 24, 2012

Error Handling in SQL*Plus

If you want to know if there has been an error in a SQL*Plus session, you need to do this explicitly. Here is a UNIX shell script:

ORACLE 11 > cat error1.ksh
sqlplus / << failure
select to_number('A') from dual
/
exit
failure
echo "Return code after failure = $?"
ORACLE 11 >

When you run it, there is a failure in the SQL*Plus session but UNIX is not aware of this:

ORACLE 11 > ./error1.ksh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:41:54 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>   2  select to_number('A') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Return code after failure = 0
ORACLE 11 >

If you wish to tell UNIX there has been an error, you should add whenever sqlerror exit failure to the SQL*Plus session:

ORACLE 11 > cat error2.ksh
sqlplus / << failure
whenever sqlerror exit failure
select to_number('A') from dual
/
exit
failure
echo "Return code after failure = $?"
ORACLE 11 >

Then the return code will be non zero so UNIX will know about the error:

ORACLE 11 > ./error2.ksh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:45:44 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL>   2  select to_number('A') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Return code after failure = 1
ORACLE 11 >

No comments: