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

问题:

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?

回答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.

回答2:

Or try DBMS_UTILITY.FORMAT_CALL_STACK

回答3:

Or you could use DBMS_DEBUG.PRINT_BACKTRACE

回答4:

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