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?

Noelyn

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.

Spunky

Answer 2

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

Milieu

Better use Dbms_Output.PUT, as the error stack/trace already contain a new line character. – Emilyemina

Answer 3

Or try DBMS_UTILITY.FORMAT_CALL_STACK

Disembowel

Answer 4

Or you could use DBMS_DEBUG.PRINT_BACKTRACE

Glean