Skip to Main Content
  • Questions
  • Logging exception causing PSLQL or SQL statements inside PLSQL exception handler

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manohar.

Asked: October 27, 2020 - 5:11 am UTC

Answered by: Connor McDonald - Last updated: October 28, 2020 - 5:57 am UTC

Category: PL/SQL - Version: 19c

Viewed 100+ times

You Asked

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.

and we said...

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


More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.