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;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - 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.
Let's see what happen when exception handled using dbms_utility.format_error_stack procedure P3.
scott@10gR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - 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