PL/SQL Exception Handling

PL/SQL Exception Handling

The following blog post by Tom Kyte (http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html) prompted me to re-evaluate my error handling in the PL/SQL that I’ve written for a large project that I’m working on. Tom feels very strongly about exception handling, and while I don’t disagree, I hadn’t considered the value of the originating line number as all that important. In reading some of the sources cited at the end of this article I got curious about seeing just how hard it would be to save a little more information about the original error, including the original line number. The following are the results of my investigation.

I should note that all of this was done with Oracle 10g and utilizes the DBMS_UTILITY and its “format” functions. Earlier releases won’t be able to make use of some/all(?) of these features, but I guess that’s the price of progress. I offer the following basic tenets of exception handling with a nod to both Tom and Steven Feuerstein for the inspiration:

1) Trap exceptions as close to the point of the error being raised as possible.

2) Never hide an exception. Exceptions should be raised because of errors and errors should always be propogated to the calling function (the client application in Tom speak).

2a) If a WHEN OTHERS is used then it MUST re-raise the exception.

3) Always capture the line number and error message where the exception is raised (this can be hard if you’re following number 1).

4) Handle all exceptions that can be handled. Do the work up front rather than forcing someone to debug your “generic” exceptions later.

The following example is taken straight from the Oracle documentation (1) with the exception of changes to the “Top_With_Logging” procedure. I’ve commented the original code in the exception handler and added calls for each of the three DBMS_UTILITY “format” functions.


CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/

The following will show the creation of each of the test procedures
as shown in the original example:

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 31 09:12:37 2008

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

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

SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
END P0;
/ 2 3 4 5 6

Procedure created.

SQL> l
1 CREATE OR REPLACE PROCEDURE P0 IS
2 e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
3 BEGIN
4 RAISE e_01476;
5 END P0;
6*
SQL> CREATE OR REPLACE PROCEDURE P1 IS
BEGIN
P0();
END P1;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P2 IS
BEGIN
P1();
END P2;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P3 IS
BEGIN
P2();
END P3;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P4 IS
BEGIN P3(); END P4;
/
CREATE OR REPLACE PROCEDURE P5 IS
BEGIN P4(); END P5;
/
SHOW ERRORS 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3
Procedure created.

SQL> 2 3
Procedure created.

SQL>
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Top_Naive IS
BEGIN
P5();
END Top_Naive;
/ 2 3 4 5

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Procedure created.

SQL>

Now we’ll run the first example calling the Top_Naive procedure which will simply call the P5 procedure which will invoke the chain of procedures with the P0 procedure raising an error. The Top_Naive procedure has no exception handling so we see the full chain of events. Notice that the initial exception that occurs in P0 tells us the line number of the offending statement (during the install above I listed the PO procedure to show the line numbers).

SQL> Set ServerOutput On
call Top_Naive()SQL>
2 /
call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3

Now we’ll run the second example and see the bare output of each of the three DBMS_UTILITY “format” functions:

SQL> Set ServerOutput On
SQL> call Top_With_Logging();
----------
FORMAT_CALL_STACK:
----- PL/SQL Call Stack -----
object line object
handle number name
0xee5cbec8 16 procedure TEST.TOP_WITH_LOGGING

----------
FORMAT_ERROR_STACK:
ORA-01476: divisor is equal to zero

----------
FORMAT_ERROR_BACKTRACE:
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512:
at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_WITH_LOGGING", line 6

----------

Call completed.

SQL>

We see that the FORMAT_CALL_STACK function only shows us the information from the call to the FORMAT_CALL_STACK function. The FORMAT_ERROR_STACK call shows us the actual error message message only, and the FORMAT_ERROR_BACKTRACE call shows us the full execution path with originating line number. Note that we still need the FORMAT_ERROR_STACK call in order to get the original error message.

Another way to achieve the same thing would be to capture the exception at the source. The following shows a different take on handling the exception and being able to capture the line number and error message. This example simply outputs the messages using DBMS_OUTPUT, but in reality this could be logged to an error table:

CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise; -- Note that we still raise an exception!
END P0;
/

SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise;
END P0;
/ 2 3 4 5 6 7 8 9 10 11

Procedure created.

SQL> call Top_Naive();
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4

call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 9
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "CPPTEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3

SQL>

Now we see that the error message and line number are output and then the exception is propogated back to the initial invoking procedure. This provides an easy way to trap all exception information and provides the possiblity of capturing the entire exception path. This may not be as important for an interactive system, but can be invaluable for a “batch” system running in an automated fashion.

Sources:

(1) Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2),
Part Number B14258-01, Section on DBMS_UTILITY

(2) http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html