Thursday, May 23, 2013

dbms_utility.format_error_stack in Oracle 10g

In Oracle database 10g, Oracle added format_error_backtrace which can and should be called from exception handler. It displays the call stack at the point where exception was raised.
Let's see what happen when exception handled using dbms_utility.format_error_stack procedure P3.

scott@10gR2> select * from v$version;

Oracle Database 10g Enterprise Edition Release - 64bi
PL/SQL Release - Production
CORE      Production
TNS for Solaris: Version - Production
NLSRTL Version - Production

scott@10gR2> create or replace procedure p3 as
  2  begin
  3     p2;
  4  exception
  5             when others then
  6             dbms_output.put_line (' calling format error stack from P3 ');
  7             dbms_output.put_line (dbms_utility.FORMAT_ERROR_BACKTRACE);
  8  end;
  9  /

Procedure created.

And now when I run the procedure P3, I will see the following output.

scott@10gR2> exec p3;
raising error at p1
 calling format error stack from P3
ORA-06512: at "scott.P1", line 4
ORA-06512: at "scott.P2", line 3
ORA-06512: at "scott.P3", line 3

PL/SQL procedure successfully completed.

The information that had previously been available only through an unhandled exception is now retrievable from within the PL/SQL code.

No comments:

Post a Comment