오라클 에러 테이블

oerr 명령어에서 사용하는 oraus.mgs 파일로 오라클 에러 테이블을 생성해보자.

아래와 같이 디렉터리와 익스터널 테이블을 생성하자.

-- 1-1
DROP DIRECTORY dir_mesg;
CREATE OR REPLACE DIRECTORY dir_mesg AS 'C:\app\ORA12CR2\product\12.2.0\dbhome_1\rdbms\mesg';

-- 1-2
DROP TABLE ext_oraus PURGE;

CREATE TABLE ext_oraus (line NUMBER, text VARCHAR2(4000))
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY dir_mesg
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        NOBADFILE NOLOGFILE NODISCARDFILE
        FIELDS TERMINATED BY '!@' (
            line    RECNUM
          , text    POSITION(1:4000))
    )
    LOCATION ('oraus.msg')
) REJECT LIMIT UNLIMITED;

oraus.mgs 파일을 파싱하기 위해 아래와 같이 패키지를 생성하자.

-- 2-1
CREATE OR REPLACE PACKAGE pg_oerr
IS
    TYPE t_oerr_rc IS RECORD (code NUMBER, message VARCHAR2 (4000));
    TYPE t_oerr_nt IS TABLE OF t_oerr_rc;
    TYPE t_oerr_text_rc IS RECORD (code NUMBER, line NUMBER, text VARCHAR2 (4000));
    TYPE t_oerr_text_nt IS TABLE OF t_oerr_text_rc;
    FUNCTION fn_oerr RETURN t_oerr_nt PIPELINED;
    FUNCTION fn_oerr_text RETURN t_oerr_text_nt PIPELINED;
END pg_oerr;
/

-- 2-2
CREATE OR REPLACE PACKAGE BODY pg_oerr
IS
    FUNCTION fn_oerr RETURN t_oerr_nt PIPELINED
    IS
        l_oerr    t_oerr_rc;
    BEGIN
        FOR c1 IN (SELECT TO_NUMBER (REGEXP_SUBSTR (text, '^[0-9]+')) AS code
                        , REGEXP_SUBSTR (text, '"(.+)"', 1, 1, 'i', 1) AS message
                     FROM ext_oraus
                    WHERE REGEXP_LIKE (text, '^[0-9].+'))
        LOOP
            l_oerr := c1;
            PIPE ROW (l_oerr);
        END LOOP;
    END fn_oerr;

    FUNCTION fn_oerr_text RETURN t_oerr_text_nt PIPELINED
    IS
        l_oerr_text    t_oerr_text_rc;
    BEGIN
        FOR c1 IN (SELECT TO_NUMBER (REGEXP_SUBSTR (text, '^[0-9]+')) AS code
                        , SUBSTR (text, 4) AS text
                     FROM ext_oraus
                    WHERE REGEXP_LIKE (text, '^[0-9].+')
                       OR REGEXP_LIKE (text, '^//.+'))
        LOOP
            CASE
                WHEN c1.code IS NOT NULL
                THEN
                    l_oerr_text.code := c1.code;
                    l_oerr_text.line := 0;
                ELSE
                    l_oerr_text.line := l_oerr_text.line + 1;
                    l_oerr_text.text := c1.text;
                    PIPE ROW (l_oerr_text);
            END CASE;
        END LOOP;
    END fn_oerr_text;
END pg_oerr;
/

조회 성능을 위해 테이블과 인덱스를 생성하자.

-- 3
DROP TABLE t_oerr PURGE;
DROP TABLE t_oerr_text PURGE;

CREATE TABLE t_oerr AS SELECT * FROM TABLE (pg_oerr.fn_oerr);
CREATE TABLE t_oerr_text AS SELECT * FROM TABLE (pg_oerr.fn_oerr_text);

ALTER TABLE t_oerr ADD CONSTRAINT t_oerr_pk PRIMARY KEY (code);
ALTER TABLE t_oerr_text ADD CONSTRAINT t_oerr_text_pk PRIMARY KEY (code, line);

아래와 같이 t_oerr 테이블에서 에러 메시지, t_oerr_text 테이블에서 에러의 원인 및 권고사항을 조회할 수 있다.

-- 4-1
SELECT message FROM t_oerr WHERE code = 1;

MESSAGE
----------------------------------
unique constraint (%s.%s) violated

1개의 행이 선택되었습니다.

-- 4-2
SELECT text FROM t_oerr_text WHERE code = 1 ORDER BY line;

TEXT
--------------------------------------------------------------------------
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
        For Trusted Oracle configured in DBMS MAC mode, you may see
        this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.

4 행이 선택되었습니다.