Since dbms_utility.format_error_backtrace has been available from Oracle 10g this isn’t another what it ishow to use it post. Instead I will be taking this opportunity to discuss a nuance of dbms_utility.format_error_backtrace that you may not be aware of.
dbms_utility.format_error_backtrace reports the call stack back to the exception or the last raise statement which if you are not aware of could catch you out.
A quick word on the examples that follow, I am not proposing you should use this functionality in its raw format as I have done here. I believe that for production code calls to dbms_utility.format_error_backtrace should be encapsulated within your exception handling package.
The following code creates 3 procedures. procedure a calls b and b calls c. Within procedure c an exception is raised. There is no intermediate exception handling and the exception passes back to procedure a which makes a call to dbms_utility.format_errror_backtrace.
create or replace procedure a is begin dbms_output.put_line('In procedure a'); b(); exception when no_data_found then dbms_output.put_line(dbms_utility.format_error_backtrace); end a; / create or replace procedure b is begin dbms_output.put_line('In procedure b'); c(); end b; / create or replace procedure c is begin dbms_output.put_line('In procedure c'); RAISE NO_DATA_FOUND; end c; /
When procedure a is run, the following results are observed:
begin a(); end; In procedure a In procedure b In procedure c ORA-06512: at "HR.C", line 5 ORA-06512: at "HR.B", line 5 ORA-06512: at "HR.A", line 5
The error stack shows the exception started on line 5 (line 24) . Excellent, when things go wrong we can track the problem down to the line number! However procedure b needs to change and the developer, Gary Careless makes the required enhancements, one of the changes is the introduction of a new exception handling block that includes a RAISE statement.
The amended version of procedure b is now:
create or replace procedure b is begin dbms_output.put_line('In procedure b'); c(); -- lots of new functionality here exception -- new exception handler for the new functionality when others then -- do something here and re-raise the exception RAISE; end b; /
Now when procedure a is run again we see the following:
begin a(); end; In procedure a In procedure b In procedure c ORA-06512: at "HR.B", line 11 ORA-06512: at "HR.A", line 5
Oh no! we have lost the line number where the exception occurred. Instead of displaying that the exception was raised by line 5 of procedure c as it did in the first example, the callstack reports the exception location is now at the the location of the RAISE statement within procedure b!
To get the accurate output from dbms_utility.format_error_backtrace you need to call it
- In the exception section that first raised the exception
- Call it from the outermost PL/SQL block and ensure that the intermediate routines do not raisere raise the exception.
The location of exception handling within an application is a project wide decision which should be agreed before development begins. However if your error handling package includes a call to dbms_utility.format_error_backtrace then you should keep this nuance in mind.
2 thoughts on “DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and the perils of the RAISE statement”
I like your article, however when I ran this in 12.2.x, I didn’t see the same results.
This is the output after B had been modified.
In procedure a
In procedure b
In procedure c
ORA-06512: at “SYS.B”, line 11
ORA-06512: at “SYS.C”, line 5
ORA-06512: at “SYS.B”, line 5
ORA-06512: at “SYS.A”, line 5
Thanks Steve for getting in touch.
I too have just tried this on a 12.2 instance and get the same output as you have shown. Unfortunately I did not put the version of the database I executed this against. From the dim and distant memory of this time, I think this post was inspired by something I hit and I think (and stress think!) remember reading this article https://blogs.oracle.com/oraclemagazine/tracing-lines which was the catalyst for this post.
If you can shed more light on this or if there is a bug in the demo code please let me know.