DanBrother的部落格

Handling PL/SQL Error Message with DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

-- Old Way
set serveroutput on size 100000
declare
v_ct number;
begin
    v_ct:=10-'a';
    exception
               when others then
                         DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
/
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

-- New Way (first introduced in 10g)
set serveroutput on size 100000
declare
v_ct number;
begin
     v_ct:=10-'a';
     exception
          when others then
                   DBMS_OUTPUT.PUT_LINE(dbms_utility.FORMAT_ERROR_STACK);                             
                   DBMS_OUTPUT.PUT_LINE(dbms_utility.FORMAT_ERROR_BACKTRACE);
end;
/

SQL> set serveroutput on size 100000
SQL> declare
2        v_ct number;
3        begin
4            v_ct:=10-'a';
5            exception
6                  when others then
7                            DBMS_OUTPUT.PUT_LINE(dbms_utility.FORMAT_ERROR_STACK);
8                            DBMS_OUTPUT.PUT_LINE(dbms_utility.FORMAT_ERROR_BACKTRACE);
9
10     end;
11     /
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: 在 line 4

NOTE:
SQLERRM displays only first 512 bytes of the error whereas DBMS_UTILITY.FORMAT_ERROR_STACK displays entire error message with up to 2000 bytes;

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE displays the call stack at the point where an exception was raised.