[오라클, Oracle] DML 문 - DML 에러 로깅

 

DML 문에서 에러가 발생하면 해당 DML 문에 의한 변경 사항이 모두 롤백된다.

99행까지 변경한 후 마지막 1행에서 에러가 발생하면 99행이 모두 롤백되는 것이다.

이런 상황을 피하기 위해 DML 에러 로깅(DML error logging) 기능을 사용할 수 있다.

DML 에러 로깅 기능은 10.2 버전부터 사용할 수 있다.

 

DML 에러 로깅 구문은 아래와 같다.

DML 에러 로깅 기능은 DML 수행 시 에러가 발생하면 에러를 로그 테이블에 기록한 후,

다음 행에 대한 DML을 계속 진행한다.

 

LOG ERRORS [INTO [schema.] table] [(simple_expression)]

[REJECT LIMIT {integer | UNLIMITED}]

 

항목 - 설명

INTO - 에러 로깅 테이블을 지정

simple_expression - 에러 태그로 사용할 값을 지정

REJECT LIMIT - integer로 에러의 한계 값을 지정 (기본값은 0 또는 UNLIMITED)

 

-- 예제 테이블 생성

-- t1 테이블의 PK가 empno이므로 중복된 empno를 삽입하면 에러가 발생한다.

 

DROP TABLE T1 PURGE;

 

CREATE TABLE T1 (EMPNO NUMBER(4), ENAME VARCHAR2(10), SAL NUMBER(7, 2)

, CONSTRAINT T1_PK PRIMARY KEY (EMPNO));

 

INSERT INTO T1 VALUES (7782, 'CLARK', 2450);

COMMIT;

 

DBMS_ERRLOG, CREATE_ERROR_LOG 프로시저로 로그 테이블을 생성할 수 있다.

아래 코드는 E1 테이블을 T1 테이블의 로그 테이블로 생성한다.

 

BEGIN

DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => 'T1'

, ERR_LOG_TABLE_NAME => 'E1');

END;

 

-- 생성된 E1 테이블의 구조

DESC E1;

 

-- 아래 쿼리는 모두 에러가 발생한다.

-- 첫 번째 쿼리는 5자리 정수부를 저장할 수 있는 SAL에 100000을 삽입했기 때문에 에러가 발생했고,

(NUMBER(7,2)로 생성된 열은 정수부 5자리, 소수부 2자리의 숫자 값을 저장할 수 있다.)

두 번째 쿼리는 T1 테이블에 존재하고 있는 EMPNO(7782)를 삽입했기 때문에 에러가 발생했다.

세 번재 쿼리도 두 번째 쿼리와 동일한 원인으로 에러가 발생했다.

 

INSERT INTO T1 VALUES (7839, 'KING', 100000);

 

ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

 

INSERT INTO T1 VALUES (7782, 'CLARK', 2450);

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다

 

INSERT INTO T1 SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다.

 

-- 아래와 같이 에러 로깅 절을 기술하면 에러가 발생하지 않는다. 세 번째 쿼리는 3행 중 EMPNO가 중복된 1행은 무시되고, 중복되지 않은 2행은 정상적으로 삽입된다.

 

INSERT INTO T1 VALUES (7839, 'KING', 100000)

LOG ERRORS INTO E1 ('1') REJECT LIMIT UNLIMITED;

 

INSERT INTO T1 VALUES (7782, 'CLARK', 2450)

LOG ERRORS INTO E1 ('2') REJECT LIMIT UNLIMTED;

 

INSERT INTO T1 SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10

LOG ERRORS INTO E1 ('3') REJECT LIMIT UNLIMTED;

 

-- 아래는 T1 테이블을 조회한 결과

-- KING과 MILLIER가 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

E1 테이블에서 에러에 대한 정보를 확인할 수 있다.

 

SELECT ORA_ERR_MESG$, ORA_ERR_OPTYP$, ORA_ERR_TAG$, EMPNO, SAL FROM E1;

 

-- 로그 테이블은 아래와 같이 직접 삭제해야 한다.

DROP TABLE E1 PURGE;

 

[IGNORE_ROW_ON_DUPKEY_INDEX 힌트]

IGNORE_ROW_ON_DUPKEY_INDEX 힌트를 사용하면 PK 제약 조건이나 UNIQUE 제약 조건에 위배되는 행을 무시할 수 있다.

 

-- 예제 테이블 생성

DROP TABLE T1 PURGE;

CREATE TABLE T1 (C1 NUMBER, CONSTRAINT T1_U1 UNIQUE (C1));

 

INSERT INTO T1 VALUES (1);

COMMIT;

 

-- 아래 쿼리는 T1 테이블의 C1 열에 중복 값인 1인 행을 삽입했기 때문에 에러가 발생한다.

INSERT INTO T1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2;

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_U1)에 위배됩니다.

 

아래와 같이 IGNORE_ROW_O_DUPKEY_INDEX 힌트를 사용하면 에러가 발생하지 않는다.

DML 에러 로깅과 유사하게 동작한다.

 

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (T1 T1_U1) */

INTO T1

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.