exception handling - Oracle PL/SQL: how to get the stack trace, package name and procedure name

Question

Sometimes the exception returns something like: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

It's not so readable since it doesn't report neither the table, the column and the value it tried to write.

it would be useful to get the current procedure name at the moment the Exception happened or is catched.

How can I obtain that?

Answer 1

You probably want DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure p1
  2  is
  3  begin
  4    raise_application_error( -20001, 'Error 1', true );
  5* end;
SQL> /

Procedure created.

SQL> create or replace procedure p2
  2  as
  3  begin
  4    null;
  5    p1;
  6  end;
  7  /

Procedure created.

SQL> begin
  2    p2;
  3  exception
  4    when others then
  5      dbms_output.put_line( dbms_utility.format_error_backtrace );
  6  end;
  7  /
ORA-06512: at "SCOTT.P1", line 4
ORA-06512: at "SCOTT.P2", line 5
ORA-06512: at
line 2


PL/SQL procedure successfully completed.