오라클자바커뮤니티에서
설립한 개발자실무교육6년차 오엔제이프로그래밍 실무교육센터
아마 여러분들은 1000 만건의 데이터를 변경 하다가 900만 번째 중복 키 때문에 오류가 나거나 insert as select로 1000개의 데이터를 저장 중 999번째 “insert value too large” 오류가 났을 때 난감했던 적이 있었을 겁니다.
10g Release2의 New Feature인 DML error Logging을 이용하면 DML에서의 에러 내용을로깅하여 확인 할 수 있습니다. 아래의 예를 통해 이해 하도록 하죠…
-------------------------------------------------------
1. 다음과 같은 실습 테이블을 만듭니다.
-------------------------------------------------------
create table DMLTEST
(pkey varchar2(100) primary key,
f1 varchar2(1),
f2 varchar2(10) not null
)
--------------------------------------------------------------------
2. DML Error Log를 설정하지 않은 상태에서 아래와 같은 PL/SQL 블록을 만들어 실행 해 봅니다. 당연히 아래와 같이 값이 너무 크다고 오류가 나타나겠죠^^
--------------------------------------------------------------------
declare i number;
begin
i := 0;
while i <= 10 loop
insert into DMLTEST (pkey, f1, f2)
values (i, i, i);
i := i+1;
end loop;
end;
/
ORA-12899: "SCOTT"."DMLTEST"."F1" 열에 대한 값이 너무 큼(실제: 2, 최대값: 1)
ORA-06512: 줄 5에서
--------------------------------------------------------------------
3. DML Error Logging을 위해서는 로깅용 테이블을 만들어야 하는데 자동 or 수동 두가지의 방법이 있는데 본 예제에서는 오라클 Package를 이용하여 자동으로 만들어 보도록 하겠습니다.
--------------------------------------------------------------------
begin
dbms_errlog.create_error_log('DMLTEST','ERROR_LOG_DMLEL') ;
end;
/
-------------------------------------------------------------------
4. 만들어진 ERROR_LOG_DMLEL 테이블의 description은 다음과 같습니다.
-------------------------------------------------------------------
SQL> desc error_log_의디
========================================================
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
PKEY VARCHAR2(4000)
F1 VARCHAR2(4000)
F2 VARCHAR2(4000)
-------------------------------------------------------------------
5. 이제 DML Error Logging을 위한 준비는 되었으며 2번 실습에서 오류가 생긴 PL/SQL 블록을 다시 실행해 보도록 하는데… 주의할 점은 INSERT 다음에 error logging 구를 적절히 삽입해야 합니다.
--------------------------------------------------------------------
declare i number;
begin
i := 0;
while i <= 10 loop
insert into DMLTEST (pkey, f1, f2)
values (i, i, i)
LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;
i := i+1;
end loop;
end;
/
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
6. DML 후 ERROR_LOG_DMLEL을 조회 합니다.
--------------------------------------------------------------------
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, f1 from error_log_dmlel
SQL> /
NUM$ ORA_ERR_MESG$ ORA_ERR_ROWID$ TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
12899 ORA-12899: value too large for column "BULKLOAD"." I 10 10
DMLEL"."F1" (actual: 2, maximum: 1)
위에서 ORA_ERR_ROWID 컬럼이 NULL로 있는 이유는 DELETE나 UPDATE의 경우 데이터의 ROWID가 지정되어 있지만 INSERT의 경우 입력시 오류가 생겼으므로 ROWID가 없는 것입니다.
--------------------------------------------------------------------
7. 이제 DML Error Logging의 재미있는 특징을 보게 되는데… Transaction을 RollBack 하더라도 error_log_의디 테이블에 있는 DML 오류 내용은 사라지지 않습니다.
--------------------------------------------------------------------
SQL> rollback ;
Rollback complete.
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, f1
from error_log_의디
NUM$ ORA_ERR_MESG$ ORA_ERR_ROWID$ TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
12899 ORA-12899: value too large for column "BULKLOAD"." I 10 10
DMLEL"."F1" (actual: 2, maximum: 1)
--------------------------------------------------------------------
8. DML 후 ERROR_LOG_DMLEL을 조회 합니다.
--------------------------------------------------------------------
정리하면 다음과 같습니다.
1. Execute DML statement.
2. Check error logging table for errors.
3. If no errors found in the error logging table, commit. Else, roll back.
4. You can now pass information on exactly which rows failed to your application or debug log.
===================================================================
다음은 LOG ERROR의 사용법 입니다.
LOG ERRORS [INTO schema.table] [ (simple_expression) [ REJECT LIMIT {integer|UNLIMITED} ]
Specifics:
• Note that the name of the error logging table ("INTO table") is optional. If a table name is not specified, the default is the same as the default table name created by DBMS_ERRLOG.CREATE_ERROR_LOG, which is 'ERR$_' || substr(table_name,1,25) .
• While the "REJECT LIMIT" clause is technically optional, the default reject limit is zero, so the error logging clause is ineffective if a reject limit is not specified.
• The "simple_expression" subclause allows you to specify a statement tag, which will be logged in the ORA_ERR_TAG$ field of the error logging table, to identify which statement caused the error. More on this below.
(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링,
Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#, ASP.Net) www.onjprogramming.co.kr
아마 여러분들은 1000 만건의 데이터를 변경 하다가 900만 번째 중복 키 때문에 오류가 나거나 insert as select로 1000개의 데이터를 저장 중 999번째 “insert value too large” 오류가 났을 때 난감했던 적이 있었을 겁니다.
10g Release2의 New Feature인 DML error Logging을 이용하면 DML에서의 에러 내용을로깅하여 확인 할 수 있습니다. 아래의 예를 통해 이해 하도록 하죠…
-------------------------------------------------------
1. 다음과 같은 실습 테이블을 만듭니다.
-------------------------------------------------------
create table DMLTEST
(pkey varchar2(100) primary key,
f1 varchar2(1),
f2 varchar2(10) not null
)
--------------------------------------------------------------------
2. DML Error Log를 설정하지 않은 상태에서 아래와 같은 PL/SQL 블록을 만들어 실행 해 봅니다. 당연히 아래와 같이 값이 너무 크다고 오류가 나타나겠죠^^
--------------------------------------------------------------------
declare i number;
begin
i := 0;
while i <= 10 loop
insert into DMLTEST (pkey, f1, f2)
values (i, i, i);
i := i+1;
end loop;
end;
/
ORA-12899: "SCOTT"."DMLTEST"."F1" 열에 대한 값이 너무 큼(실제: 2, 최대값: 1)
ORA-06512: 줄 5에서
--------------------------------------------------------------------
3. DML Error Logging을 위해서는 로깅용 테이블을 만들어야 하는데 자동 or 수동 두가지의 방법이 있는데 본 예제에서는 오라클 Package를 이용하여 자동으로 만들어 보도록 하겠습니다.
--------------------------------------------------------------------
begin
dbms_errlog.create_error_log('DMLTEST','ERROR_LOG_DMLEL') ;
end;
/
-------------------------------------------------------------------
4. 만들어진 ERROR_LOG_DMLEL 테이블의 description은 다음과 같습니다.
-------------------------------------------------------------------
SQL> desc error_log_의디
========================================================
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
PKEY VARCHAR2(4000)
F1 VARCHAR2(4000)
F2 VARCHAR2(4000)
-------------------------------------------------------------------
5. 이제 DML Error Logging을 위한 준비는 되었으며 2번 실습에서 오류가 생긴 PL/SQL 블록을 다시 실행해 보도록 하는데… 주의할 점은 INSERT 다음에 error logging 구를 적절히 삽입해야 합니다.
--------------------------------------------------------------------
declare i number;
begin
i := 0;
while i <= 10 loop
insert into DMLTEST (pkey, f1, f2)
values (i, i, i)
LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;
i := i+1;
end loop;
end;
/
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
6. DML 후 ERROR_LOG_DMLEL을 조회 합니다.
--------------------------------------------------------------------
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, f1 from error_log_dmlel
SQL> /
NUM$ ORA_ERR_MESG$ ORA_ERR_ROWID$ TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
12899 ORA-12899: value too large for column "BULKLOAD"." I 10 10
DMLEL"."F1" (actual: 2, maximum: 1)
위에서 ORA_ERR_ROWID 컬럼이 NULL로 있는 이유는 DELETE나 UPDATE의 경우 데이터의 ROWID가 지정되어 있지만 INSERT의 경우 입력시 오류가 생겼으므로 ROWID가 없는 것입니다.
--------------------------------------------------------------------
7. 이제 DML Error Logging의 재미있는 특징을 보게 되는데… Transaction을 RollBack 하더라도 error_log_의디 테이블에 있는 DML 오류 내용은 사라지지 않습니다.
--------------------------------------------------------------------
SQL> rollback ;
Rollback complete.
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, f1
from error_log_의디
NUM$ ORA_ERR_MESG$ ORA_ERR_ROWID$ TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
12899 ORA-12899: value too large for column "BULKLOAD"." I 10 10
DMLEL"."F1" (actual: 2, maximum: 1)
--------------------------------------------------------------------
8. DML 후 ERROR_LOG_DMLEL을 조회 합니다.
--------------------------------------------------------------------
정리하면 다음과 같습니다.
1. Execute DML statement.
2. Check error logging table for errors.
3. If no errors found in the error logging table, commit. Else, roll back.
4. You can now pass information on exactly which rows failed to your application or debug log.
===================================================================
다음은 LOG ERROR의 사용법 입니다.
LOG ERRORS [INTO schema.table] [ (simple_expression) [ REJECT LIMIT {integer|UNLIMITED} ]
Specifics:
• Note that the name of the error logging table ("INTO table") is optional. If a table name is not specified, the default is the same as the default table name created by DBMS_ERRLOG.CREATE_ERROR_LOG, which is 'ERR$_' || substr(table_name,1,25) .
• While the "REJECT LIMIT" clause is technically optional, the default reject limit is zero, so the error logging clause is ineffective if a reject limit is not specified.
• The "simple_expression" subclause allows you to specify a statement tag, which will be logged in the ORA_ERR_TAG$ field of the error logging table, to identify which statement caused the error. More on this below.
댓글 없음:
댓글 쓰기