msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

How to find where an error was raised in PL/SQL

To eliminate errors in your Oracle PL/SQL programs, it is very important to find the line number on which the error had occurred. The question is how to find that line number.

Before Oracle Database 10g Release 1, the only way to know the line number is to let the exception go unhandled in your PL/SQL code.

In Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This new function returns a formatted string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised.

For example, prior to 10gR1:

SQL> CREATE OR REPLACE PROCEDURE p1
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p1, raising error');
  5     RAISE VALUE_ERROR;
  6  END;
  7  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p2
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p2');
  5     DBMS_OUTPUT.put_line ('calling p1');
  6     p1;
  7  END;
  8  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p3
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p3, calling p2');
  5     p2;
  6  END;
  7  /

Procedure created.

Notice the unhandled VALUE_ERROR exception raised in p1. Now, Let’s call p3:

SQL> BEGIN
  2     DBMS_OUTPUT.put_line ('calling p3');
  3     p3;
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HR.P1", line 5
ORA-06512: at "HR.P2", line 6
ORA-06512: at "HR.P3", line 5
ORA-06512: at line 3

As expected, by not handling the exception, the procedure returns an error and we are able to know where the exception was raised. In this example, the error ORA-06502: PL/SQL: numeric or value error was raised at "HR.P1", line 5.

Starting with 10gR1, you can call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in your exception handler. Let’s use this function in the exception section of procedure p3:

SQL> CREATE OR REPLACE PROCEDURE p3
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p3, calling p2');
  5     p2;
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        DBMS_OUTPUT.put_line ('Error stack from p3:');
 10        DBMS_OUTPUT.put_line
 11                         (DBMS_UTILITY.format_error_backtrace);
 12  END;
 13  /

Procedure created.

Let’s call p3:

SQL> set serveroutput on
SQL> BEGIN
  2     DBMS_OUTPUT.put_line ('calling p3');
  3     p3;
  4  END;
  5  /
calling p3
in p3, calling p2
in p2
calling p1
in p1, raising error
Error stack from p3:
ORA-06512: at "HR.P1", line 5
ORA-06512: at "HR.P2", line 6
ORA-06512: at "HR.P3", line 5


PL/SQL procedure successfully completed.

The procedure p3 successfully completed and returned the execution stack at the point where the exception was raised. The first line of the stack is where the exception was raised. In this example, it was at "HR.P1", line 5.

In the above example, the call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE was from the exception section of the outermost procedure in the stack (p3). Another approach is to call the function in the exception section of the block in which the error was raised.

Here is an example to illustrate the second approach:

SQL> CREATE OR REPLACE PROCEDURE p1
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p1, raising error');
  5     RAISE VALUE_ERROR;
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        DBMS_OUTPUT.put_line ('Error stack from p1:');
 10        DBMS_OUTPUT.put_line
 11                         (DBMS_UTILITY.format_error_backtrace);
 12        RAISE;
 13  END;
 14  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p2
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p2');
  5     DBMS_OUTPUT.put_line ('calling p1');
  6     p1;
  7  EXCEPTION
  8     WHEN OTHERS
  9     THEN
 10        RAISE NO_DATA_FOUND;
 11  END;
 12  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p3
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('in p3, calling p2');
  5     p2;
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        DBMS_OUTPUT.put_line ('Error stack from p3:');
 10        DBMS_OUTPUT.put_line
 11                         (DBMS_UTILITY.format_error_backtrace);
 12  END;
 13  /

Procedure created.

SQL> BEGIN
  2     DBMS_OUTPUT.put_line ('calling p3');
  3     p3;
  4  END;
  5  /
calling p3
in p3, calling p2
in p2
calling p1
in p1, raising error
Error stack from p1:
ORA-06512: at "HR.P1", line 5

Error stack from p3:
ORA-06512: at "HR.P2", line 10
ORA-06512: at "HR.P3", line 5


PL/SQL procedure successfully completed.

The function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is a great improvement to PL/SQL and adds a much needed functionality.

Resources:


Filed in Oracle on 25 Jul 06 | Tags: ,


Reader's Comments

  1. |

    Also mention the use of $$PLSQL_UNIT.

  2. |

    Amihay, that will be the subject of another blog post. Thanks.

  3. |

    I’ve always found the line numbers provided by the PL/SQL parser to be a little misleading, whenever I’ve tried to look them up they seem to correspond to the pcode rather than the source code. The biggest problem I’ve found is that the pcode doesn’t include blank lines and in long blocks the line numbers can get seriously out of whack.

    Mind you, I haven’t looked into this seriously since Oracle 8i so it may have changed in more recent versions of the database.

  4. |

    DECLARE CURR_CONS CONSULTANT%ROWTYPE BEGIN SELECT * INTO CURR_CONS FROM CONSULTANT END; / –find the error please