오라클 에러 테이블

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- 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 파일을 파싱하기 위해 아래와 같이 패키지를 생성하자.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

-- 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; /

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

1 2 3 4 5 6 7 8 9

-- 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 테이블에서 에러의 원인 및 권고사항을 조회할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- 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 행이 선택되었습니다.