Oracle PL/SQL: how to get the stack trace, package name and procedure name
Solution 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.
Solution 2
Or try DBMS_UTILITY.FORMAT_CALL_STACK
Solution 3
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
Solution 4
Or you could use DBMS_DEBUG.PRINT_BACKTRACE
Revious
Updated on September 16, 2020Comments
-
Revious over 3 years
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?