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

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

It's not so readable sinc开发者_开发百科e 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?

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.

I use the combination of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. (Improving the answer of Justin Cave)

when others then
  Dbms_Output.put_line ( DBMS_UTILITY.FORMAT_ERROR_STACK() );
  Dbms_Output.put_line ( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );

This gives the error on the first line and the stack on the following lines: (output from the example given by Justin Cave)

ORA-20001: Error 1
ORA-06512: at "SCOTT.X1", line 4
ORA-06512: at "SCOTT.X2", line 5
ORA-06512: at line 2

Or try DBMS_UTILITY.FORMAT_CALL_STACK

Or you could use DBMS_DEBUG.PRINT_BACKTRACE