유용한 내용인 것 같아 테스트한 내용을 올려봅니다.
노하우 게시판 검색해보니 해당 내용이 있긴한데 포커스가 다른 것 같네요^^;
다수 행을 INSERT하거나 UPDATE하는 등의 DML 수행시 수행 중 하나라도 오류가 발생하면 전체가 ROLLBACK됩니다.
100만 건 INSERT 중 99만 9999번에서 오류..; 아니! 단 하나 오류 났는데 전체 ROLLBACK이라니??
물론 전체 ROLLBACK해야하는 경우도 있긴하지만 아닌 경우도 있죠.
그렇다고 한 건씩 LOOP 돌리면서 COMMIT할 수도 없고..
SINGLE TABLE INSERT 문 SYNTAX를 보면
INSERT -> HINT -> SINGLE TABLE -> INSERT_INTO_CLAUSE -> VALUE CLAUSE OR SUBQUERY -> RETURNING_CLAUSE -> ERROR_LOGGING_CLAUSE
테스트할 내용이 ERROR_LOGGING_CLAUSE 부분입니다.
ERROR_LOGGING_CLAUSE의 SYNTAX는
LOG ERRORS
[ INTO [SCHEMA.] TABLE]
[ (SIMPLE_EXPRESSION) ]
[ REJECT LIMIT { INTERGER | UNLIMITED } ]
지원은 10g부터. INSERT이외에도 UPDATE, MERGE 등의 DML에도 적용됩니다.
-----------------------------------------
테스트 내용을 보시죠
-- 테스트 테이블 생성
CREATE TABLE TESTTAB
(COL VARCHAR2(10));
-- 그냥 INSERT해봅니다.
INSERT INTO TESTTAB
SELECT '1' FROM DUAL UNION ALL
SELECT '12' FROM DUAL UNION ALL
SELECT '123' FROM DUAL UNION ALL
SELECT '1234' FROM DUAL UNION ALL
SELECT '12345' FROM DUAL UNION ALL
SELECT '123456' FROM DUAL UNION ALL
SELECT '1234567' FROM DUAL UNION ALL
SELECT '12345678' FROM DUAL UNION ALL
SELECT '123456789' FROM DUAL UNION ALL
SELECT '1234567890' FROM DUAL UNION ALL
SELECT '1234567890A' FROM DUAL UNION ALL
SELECT '1234567890AB' FROM DUAL UNION ALL
SELECT '1234567890ABC' FROM DUAL UNION ALL
SELECT '1234567890ABCD' FROM DUAL UNION ALL
SELECT '1234567890ABCDE' FROM DUAL UNION ALL
SELECT '1234567890ABCDEF' FROM DUAL
-- 오류나네요.
ORA-12899: "USER"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 11, 최대값: 10)
ROLLBACK;
-- 로그용 테이블을 만듭니다.
BEGIN
dbms_errlog.create_error_log('TESTTAB','TESTTAB_ERRLOG');
END;
-- ERROR_LOGGING_CLAUSE를 사용해서 INSERT해봅니다.
INSERT INTO TESTTAB
SELECT '1' FROM DUAL UNION ALL
SELECT '12' FROM DUAL UNION ALL
SELECT '123' FROM DUAL UNION ALL
SELECT '1234' FROM DUAL UNION ALL
SELECT '12345' FROM DUAL UNION ALL
SELECT '123456' FROM DUAL UNION ALL
SELECT '1234567' FROM DUAL UNION ALL
SELECT '12345678' FROM DUAL UNION ALL
SELECT '123456789' FROM DUAL UNION ALL
SELECT '1234567890' FROM DUAL UNION ALL
SELECT '1234567890A' FROM DUAL UNION ALL
SELECT '1234567890AB' FROM DUAL UNION ALL
SELECT '1234567890ABC' FROM DUAL UNION ALL
SELECT '1234567890ABCD' FROM DUAL UNION ALL
SELECT '1234567890ABCDE' FROM DUAL UNION ALL
SELECT '1234567890ABCDEF' FROM DUAL
LOG ERRORS INTO TESTTAB_ERRLOG('테스트 입력') REJECT LIMIT UNLIMITED;
COMMIT;
-- 대상테이블 조회
SELECT * FROM TESTTAB;
COL
1
12
123
1234
12345
123456
1234567
12345678
123456789
1234567890
-- 정상적인 건만 INSERT되었습니다.
-- 에러로그 테이블 조회
SELECT * FROM TESTTAB_ERRLOG;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ COL
12899 ORA-12899: "NETM1"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 11, 최대값: 10) I 테스트 입력 1234567890A
12899 ORA-12899: "NETM1"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 12, 최대값: 10) I 테스트 입력 1234567890AB
12899 ORA-12899: "NETM1"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 13, 최대값: 10) I 테스트 입력 1234567890ABC
12899 ORA-12899: "NETM1"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 14, 최대값: 10) I 테스트 입력 1234567890ABCD
12899 ORA-12899: "NETM1"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 15, 최대값: 10) I 테스트 입력 1234567890ABCDE
12899 ORA-12899: "NETM1"."TESTTAB"."COL" 열에 대한 값이 너무 큼(실제: 16, 최대값: 10) I 테스트 입력 1234567890ABCDEF
-- 에러내역이 보입니다. 별도로 처리해도 되는 경우 해당 데이터만 처리하면 되겠네요.
DROP TABLE TESTTAB;
DROP TABLE TESTTAB_ERRLOG;
다수 건의 ROW에 대한 DML 수행시 일괄 처리하지 않아도 되는 경우라면 ERROR_LOGGING_CLAUSE를 잘 활용하시면 되겠습니다.