PL/SQL 상세 에러 저장 하기

문의 : PL/SQL 에러 출력시, DEPTH 있는 에러 코드를 찍고 싶다.
    일반 ) SQLERRM 를 통해서 에러 로그 출력 혹은 저장 
    상세 ) DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 를 통해서 에러 상세 로그 출력 혹은 저장 
    비고 ) 아래 출처와 조금 다르게, log_prc 라고 상세 로그 저장을 위한 별도 PROCEDURE 를 생성함

참고 출처 : https://hrjeong.tistory.com/48

-- 에러관리를 위한 LOG_PRC 생성 
-- 우선 일반적인 SQLERRM 만 저장하도록 컴파일 
CREATE OR REPLACE PROCEDURE LOG_PRC
IS
V_ERROR VARCHAR2(1000); 
PRAGMA AUTONOMOUS_TRANSACTION ; -- 호출 PKGE 와 별도 TRNASACTION 으로 관리
BEGIN
        
V_ERROR := '1: LOG_PRC ZONE !!!';
V_ERROR := V_ERROR||CHR(10)||' 2:'||SQLERRM;
--V_ERROR := V_ERROR||CHR(10)||' 3:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

INSERT INTO CHK_PL_LOG  ( CHK_DT , CHK_LOG ) VALUES ( SYSDATE, V_ERROR ) ;

COMMIT ; 
END LOG_PRC;

-- PKG2 선언부 
CREATE OR REPLACE PACKAGE PKG2
IS
    PROCEDURE P3 (i_p1 IN VARCHAR2);
END PKG2;


CREATE OR REPLACE PACKAGE BODY PKG2
IS
    -- F1
    FUNCTION F1 (i_p1 IN VARCHAR2)
        RETURN NUMBER
    IS
    BEGIN
        RETURN TO_NUMBER (i_p1);
    END F1;
 
    -- P1
    PROCEDURE P1 (i_p1 IN VARCHAR2)
    IS
        l_v1    NUMBER;
    BEGIN
        l_v1 := F1 (i_p1);
        DBMS_OUTPUT.PUT_LINE (l_v1);
    END P1;
 
 
    -- P3
    PROCEDURE P3 (i_p1 IN VARCHAR2)
    IS
    BEGIN
        P1 (i_p1);
    EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ($$PLSQL_UNIT || ': ' || $$PLSQL_LINE);
          LOG_PRC ;   -- 에러관리를 위한 LOG_PRC 호출 
    END P3;
END PKG2;


SQL> set serveroutput on
SQL> set linesize 180
SQL> col CHK_LOG for a120
SQL> TRUNCATE table CHK_PL_LOG ;

Table truncated.

SQL> EXEC pkg2.p3 ('X');
PKG2: 28

PL/SQL procedure successfully completed.

-- SQLERRM 사용 시, 기본 에러만 확인 됨 
SQL> SELECT * FROM CHK_PL_LOG ;

CHK_DT    CHK_LOG
--------- ------------------------------------------------------------------------------------------------------------------------
21-DEC-22 1: LOG_PRC ZONE !!!
           2:ORA-06502: PL/SQL: numeric or value error: character to number conversion error


SQL>



-- 에러관리를 위한 LOG_PRC 생성 
-- 우선 상세 에러 관리를 위해 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 사용하는 것으로 재컴파일
CREATE OR REPLACE PROCEDURE LOG_PRC
IS
V_ERROR VARCHAR2(1000); 
PRAGMA AUTONOMOUS_TRANSACTION ; -- 호출 PKGE 와 별도 TRNASACTION 으로 관리
BEGIN
        
V_ERROR := '1: LOG_PRC ZONE !!!';
--V_ERROR := V_ERROR||CHR(10)||' 2:'||SQLERRM;
V_ERROR := V_ERROR||CHR(10)||' 3:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

INSERT INTO CHK_PL_LOG  ( CHK_DT , CHK_LOG ) VALUES ( SYSDATE, V_ERROR ) ;

COMMIT ; 
END LOG_PRC;

SQL> TRUNCATE table CHK_PL_LOG ;

Table truncated.

SQL>  EXEC pkg2.p3 ('X');
PKG2: 28

PL/SQL procedure successfully completed.

-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 사용 시, 상세 에러 확인 가능 
SQL> SELECT * FROM CHK_PL_LOG ;

CHK_DT    CHK_LOG
--------- ------------------------------------------------------------------------------------------------------------------------
21-DEC-22 1: LOG_PRC ZONE !!!
           3:ORA-06512: at "SCOTT.PKG2", line 8
          ORA-06512: at "SCOTT.PKG2", line 16
          ORA-06512: at "SCOTT.PKG2", line 25


SQL>