​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Some useful PL/SQL Error handling techniques

 

As of Oracle 10 R2 Oracle introduced a couple of new utilities to aid in the logging of PL/SQL errors.

 

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

This tells you on what line number  the most recent exception was raised on

 

DBMS_UTILITY.FORMAT_CALL_STACK

This tells you the execution call stack (i.e Procedure A called B called C etc…).

 

DBMS_UTILITY.FORMAT_ERROR_STACK

Returns the current Oracle error message (sometimes as a single string, and sometimes as a stack of messages, if the exception has been re-raised one or more times). Oracle recommends you use this instead of SQLERRM, which may truncate your error message.

 

 

Now for an example of how you might use them.

 

 

1  BEGIN

  2     EXECUTE IMMEDIATE 'idontexist';

  3  EXCEPTION

  4     WHEN OTHERS THEN

  5        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);

  6        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

  7        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);

  8        RAISE;

  9* END;

SQL> /

ORA-00900: invalid SQL statement

 

ORA-06512: at line 2

 

----- PL/SQL Call Stack -----

  object      line  object

  handle    number

name

15F4A240         7  anonymous block

 

BEGIN

*

ERROR at line 1:

ORA-00900: invalid SQL statement

ORA-06512: at line 8

 

 

So we can see that it correctly reported that our initial error occurred at line 2 . Note that we need the call to format_errror_stack otherwise we would not get the ORA-00900: invalid SQL statement line before our ORA-06512: at line 2 line.

 

If you want to log these to a n error logging table you have a bit more work to do as each stack can often contain multiple records.  You will also likely want to use the PRAGMA AUTONOMOUS TRANSATION directive to ensure the errors get logged regardless of what happens in your main PL/SQL code.

 

e.g

 

 

CREATE TABLE error_log

(

   ERROR_CODE      INTEGER

 error_message   VARCHAR2 (4000)

 backtrace       CLOB

 callstack       CLOB

 created_on      DATE

 created_by      VARCHAR2 (30)

)

 

CREATE OR REPLACE PROCEDURE record_error
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_code   PLS_INTEGER := SQLCODE;
   l_mesg  VARCHAR2(32767) := SQLERRM; 
BEGIN
   INSERT INTO error_log (error_code
                        error_message
                        backtrace
                        callstack
                        created_on
                        created_by)
        VALUES (l_code
              l_mesg
              sys.DBMS_UTILITY.format_error_backtrace
              sys.DBMS_UTILITY.format_call_stack
              ,  SYSDATE
              ,  USER);
 
   COMMIT;
END;

 

And use it something like

.

.

.

 

EXCEPTION
   WHEN OTHERS
   THEN
      record_error();
      RAISE;