2013年4月30日 星期二

使用DBMS_UTILITY.format_error_backtrace

DBMS_UTILITY.format_error_backtrace

For Oracle 10g以上的EXCEPTION TRACE~

Sample from book :
------------------------PK spec-----------------------------
CREATE OR REPLACE PACKAGE EEP.ERRORTEST
AS
    PROCEDURE proc1;
    PROCEDURE proc2;
    PROCEDURE proc3;
END;



------------------------PK Body-----------------------------


CREATE OR REPLACE PACKAGE BODY EEP.ERRORTEST
AS
    PROCEDURE proc1
    IS
    BEGIN
        DBMs_output.put_line('running proc1');
        RAISE NO_DATA_FOUND;
    ND;
    PROCEDURE proc2
    IS
        l_str VARCHAR2 (30) := 'calling proc1';
    BEGIN
        DBMs_output.put_line(l_str);
        proc1;
    END;
    PROCEDURE proc3
    IS
    BEGIN
        DBMs_output.put_line('calling proc2');
        proc2;
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMs_output.put_line('Error Stack at Top Level');
            DBMs_output.put_line(DBMS_UTILITY.format_error_backtrace);
    END;  
END;

------------------------EXCEUTE-----------------------------
BEGIN
    DBMS_OUTPUT.put_line('proc3→proc2→proc1');
    ERRORTEST.proc3;
END;


------------------------OUTPUT-----------------------------
proc3→proc2→proc1
calling proc2
calling proc1
running proc1
Error Stack at Top Level
ORA-06512: at "EEP.ERRORTEST", line 7
ORA-06512: at "EEP.ERRORTEST", line 14
ORA-06512: at "EEP.ERRORTEST", line 20