Hi Tom,
I would like to know if there is any way to code exception handlers in such a way as to capture the specific PLSQL or SQL statements that shall cause an exception inside the same PLSQL block for which the exception handler is written.
For example, say below statement inside a PLSQL block,
v_num (a number datatype variable) := <text data>;
this shall cause the obvious exception and this particular statement shall be captured along with the parsed values (the actual value at the right hand side of this statement that was responsible for the exception), and logged from the exception handler.
Similarly, another SQL statement in the same block, during the next run, shall cause some exception and the parsed version of that SQL statement shall be captured and logged from the exception handler.
I know what I am asking is akin to the formal debugging mechanism, but is this something impossible to achieve ?
Regards,
Manohar Mishra.
Using UTL_CALL_STACK you can dig into where the errors (and current call stack) is, for example
SQL> CREATE OR REPLACE PROCEDURE output_call_stack
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line('LexDepth Depth LineNo Name');
5 DBMS_OUTPUT.put_line(
6 '-------- ----- ------ ----');
7
8 FOR the_depth IN REVERSE 1 .. utl_call_stack.dynamic_depth()
9 LOOP
10 DBMS_OUTPUT.put_line(
11 RPAD(utl_call_stack.lexical_depth(the_depth),9)
12 || RPAD(the_depth, 5)
13 || RPAD(TO_CHAR(utl_call_stack.unit_line(the_depth),'99'),8)
14 || utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(the_depth)));
15 END LOOP;
16 END;
17 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE output_err_stack
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line('BakDepth Depth LineNo Name');
5 DBMS_OUTPUT.put_line(
6 '-------- ----- ------ ----');
7
8 FOR the_depth IN REVERSE 1 .. utl_call_stack.backtrace_depth()
9 LOOP
10 DBMS_OUTPUT.put_line(
11 RPAD(the_depth, 5)
12 || RPAD(TO_CHAR(utl_call_stack.backtrace_line(the_depth),'99'),8)
13 || utl_call_stack.backtrace_unit(the_depth));
14 END LOOP;
15 END;
16 /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 PROCEDURE do_stuff;
4 END;
5 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 PROCEDURE do_stuff
4 IS
5 PROCEDURE np1
6 IS
7 PROCEDURE np2
8 IS
9 PROCEDURE np3
10 IS x int;
11 BEGIN
12 output_call_stack;
13 x := 1/0;
14 END;
15 BEGIN
16 np3;
17 END;
18 BEGIN
19 np2;
20 END;
21 BEGIN
22 np1;
23 exception
24 when others then
25 output_err_stack;
26 END;
27 END;
28 /
Package body created.
SQL> set serverout on
SQL> BEGIN
2 pkg.do_stuff;
3 END;
4 /
LexDepth Depth LineNo Name
-------- ----- ------ ----
0 6 2 __anonymous_block
1 5 22 PKG.DO_STUFF
2 4 19 PKG.DO_STUFF.NP1
3 3 16 PKG.DO_STUFF.NP1.NP2
4 2 12 PKG.DO_STUFF.NP1.NP2.NP3
0 1 10 OUTPUT_CALL_STACK
BakDepth Depth LineNo Name
-------- ----- ------ ----
4 22 MCDONAC.PKG
3 19 MCDONAC.PKG
2 16 MCDONAC.PKG
1 13 MCDONAC.PKG
PL/SQL procedure successfully completed.
SQL>
SQL>
Thus if you wanted to get the statement in error, you could get the error back trace for depth=1, and use the line/unit name to look in ALL_SOURCE for the appropriate piece of source code.
Further details on UTL_CALL_STACK here
https://blogs.oracle.com/oraclemagazine/sophisticated-call-stack-analysis