When an exception is raised, one of the most important piece of information that a developer would like to know is the line of code that raised that exception, prior to Oracle 10g one way to obtain this information by allowing the exception to go unhandled.
test@9iR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
test@9iR2> create or replace procedure p1 as
2 begin
3 dbms_output.put_line ('raising error at p1');
4 raise no_data_found;
5 end;
6 /
Procedure created.
test@9iR2> create or replace procedure p2 as
2 begin
3 p1;
4 end;
5 /
Procedure created.
test@9iR2> create or replace procedure p3 as
2 begin
3 p2;
4 end;
5 /
Procedure created.
test@9iR2> exec p3;
raising error at p1
BEGIN p3; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TEST.P1", line 4
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512: at line 1
Now we have the line number, we can zoom right into the code and fix it, on the other hand we got this information by letting the Exception going unhandled. How ever in many application we work to avoid unhandled exception.
Let's see what happen when exception handled in procedure P3.
test@9iR2> 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_stack);
8 end;
9 /
Procedure created.
Notice that I call dbms_utility.format_error_stack, it returns the full error message.
test@PWTK52> exec p3;
raising error at p1
calling format error stack from P3
ORA-01403: no data found
PL/SQL procedure successfully completed.
dbms_utility.format_error_stack - does not show the full error stack with line numbers.
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.
Nice tutorial.
ReplyDeleteThank You
Good One. Very much useful.
ReplyDeleteGood way....
ReplyDeletenaveen kottur
Very good Article !!!
ReplyDeletethanks. Nice 2 know
ReplyDeleteShame it only works top-down rather than bottom up. If you put the exception handling in the bottom level procedure (p3) rather than p1, all you get is "ORA-06512: at "scott.P3", line 3". This makes logging to error tables quite difficult: if you just want to write out the error message once, it's easiest to do it at the point of failure i.e. the lowest level procedure and then pass back some sort of user-defined exception to all the calling procedures which tells them that this error has already been handled, so they don't need to write anything out.
ReplyDeleteBut if you call it at the bottom of the tree you don't get the full stack of calls. Calling it at the top is difficult because you have to know where the top is.
Nice example -- praveen.
ReplyDelete.
ReplyDeleteDid anyone notice that when the "DBMS_UTILITY.FORMAT_ERROR_BACKTRACE" is used the original NO_DATA_FOUND error was lost?
You have to use a combination of the 2 calls for the full end-to-end error stack otherwise all you get is "ORA-06512", which is not very useful by itself.
$ oerr ora 6512
06512, 00000, "at %sline %s"
// *Cause: Backtrace message as the stack is unwound by unhandled
// exceptions.
// *Action: Fix the problem causing the exception or write an exception
// handler for this condition. Or you may need to contact your
// application administrator or DBA.
You should also note that you lose the original line when you catch the expectations the through the stack. Because Oracle resets the stack at each caught exception you have to call DBMS_UTILITY.FORMAT_... for each level.
Example; notice below at "calling format error stack from P3" we lost the reference to P1:
SQL> set serveroutput on
SQL>
SQL> create or replace procedure p1 as
2 begin
3 dbms_output.put_line ('raising error at p1');
4 raise no_data_found;
5 end;
6 /
Procedure created.
SQL>
SQL> create or replace procedure p2 as
2 begin
3 p1;
4 exception when others then
5 dbms_output.put_line('calling format error stack from P2');
6 dbms_output.put_line (dbms_utility.format_error_stack);
7
8 dbms_output.put_line('calling format error backtrace stack from P2');
9 dbms_output.put_line (dbms_utility.format_error_backtrace);
10
11 raise;
12 end;
13 /
Procedure created.
SQL>
SQL> create or replace procedure p3 as
2 begin
3 p2;
4 exception when others then
5 dbms_output.put_line('calling format error stack from P3');
6 dbms_output.put_line (dbms_utility.format_error_stack);
7
8 dbms_output.put_line('calling format error backtrace stack from P3');
9 dbms_output.put_line (dbms_utility.format_error_backtrace);
10 end;
11 /
Procedure created.
SQL>
SQL> exec p3;
raising error at p1
calling format error stack from P2
ORA-01403: no data found
calling format error backtrace stack from P2
ORA-06512: at "SCOTT.P1", line 4
ORA-06512: at "SCOTT.P2", line 3
calling format error stack from P3
ORA-01403: no data found
calling format error backtrace stack from P3
ORA-06512: at "SCOTT.P2", line 11
ORA-06512: at "SCOTT.P3", line 3
PL/SQL procedure successfully completed.
.
thanks. Nice 2 know. It is very much useful while debug.
ReplyDelete