PL/SQL Breadcrumbs

Steven Feuerstein — Thu, 09 Nov 2006 18:55:00 GMT

When an exception is raised, one of the most important pieces of information a programmer would love to get hold of is the line of code that caused the exception to be raised. Before Oracle 10g came long, the only way to get this information was to allow the exception to go unhandled and then view the error stack. The following chain of program calls shows you what I mean.

CREATE OR REPLACE PROCEDURE my_procedure_1 IS

BEGIN

DBMS_OUTPUT.put_line ('running my_procedure_1');

RAISE NO_DATA_FOUND;

END;

CREATE OR REPLACE PROCEDURE my_procedure_2 IS

l_str VARCHAR2(30) := 'calling my_procedure_1';

BEGIN

DBMS_OUTPUT.put_line (l_str);

my_procedure_1;

END;

CREATE OR REPLACE PROCEDURE my_procedure_3 IS

BEGIN

DBMS_OUTPUT.put_line ('calling my_procedure_2');

my_procedure_2;

END;

Procedure my_procedure_3 calls my_procedure_2 calls my_procedure_1, at which point my_procedure_1 raises the NO_DATA_FOUND exception. Notice that there is no error handling in any of the procedures, most significantly lacking in the top-level my_procedure_3 procedure. If I run my_procedure_3, I will then see the following results:

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "SCOTT.MY_PROCEDURE_1", line 4

ORA-06512: at "SCOTT.MY_PROCEDURE_2", line 6

ORA-06512: at "SCOTT.MY_PROCEDURE_3", line 4

ORA-06512: at line 3

We see immediately that the error was raised on line 7 of my_procedure_1.

Glad to have the info, but I would really like to avoid letting my exception go unhandled. Instead, I want to log this information from within PL/SQL – and sad to say, prior to Oracle 10g, this was not possible. You can see this in the code below:

I add an exception section to the my_procedure_3 procedure used above:

CREATE OR REPLACE PROCEDURE my_procedure_3

BEGIN

DBMS_OUTPUT.put_line ('calling my_procedure_2');

my_procedure_2;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (

DBMS_UTILITY.FORMAT_ERROR_STACK);

END;

Notice that I call DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM. Oracle recommends that we use this built-in packaged function because it will return the full error message, while SQLERRM may truncate the error message.

When I run this code, I see the following output:

calling my_procedure_2

calling my_procedure_1

running my_procedure_1

ORA-01403: no data found

In other words, DBMS_UTILITY.FORMAT_ERROR_STACK does not show us the full error stack with line numbers. I only see the error message; I get the same behavior with SQLERRM.

Very frustrating!

Fortunately, in Oracle 10g, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides precisely this functionality: it displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the exception was raised.

Let's go back to that my_procedure_3 procedure and replace the call to FORMAT_ERROR_STACK with FORMAT_ERROR_BACKTRACE.

CREATE OR REPLACE PROCEDURE my_procedure_3

BEGIN

DBMS_OUTPUT.put_line ('calling my_procedure_2');

my_procedure_2;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error stack at top level:');

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;

And now when I run my_procedure_3, I will see the following output:

calling my_procedure_2

calling my_procedure_1

running my_procedure_1

Error stack at top level:

ORA-06512: at "SCOTT.MY_PROCEDURE_1", line 4

ORA-06512: at "SCOTT.MY_PROCEDURE_2", line 6

ORA-06512: at "SCOTT.MY_PROCEDURE_3", line 4

Fantastic! The line number can now be obtained within my PL/SQL block and stored along with the rest of my error log information.

And, just so we all understand my choice for the title of this blog entry: the backtrace is like a trail of breadcrumbs leading back to the line that raised the error. 'Nuff said.

I consider DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to be one of the most useful enhancements in Oracle 10g. Here are my recommendations for its use:

Call this function whenever you have trapped an exception in a WHEN clause. Grab that backtrace information and store in your error log (database table, file, whatever).
It is especially important to call the function before you re-raise an exception in your exception handler (or raise another exception). Once you raise an exception, the backtrace string will only trace back to this last raise, and not the original error.
Best of all: create a standard error logging package or procedure that all developers use to log an error, and have that central module call the backtrace function.

Steven Feuerstein's Blog

PL/SQL Breadcrumbs