Oracle DML容錯處理（1）

標籤：comm   ignore   body   ora   自動產生   user   example   case   問題   

Oracle dml操作過程中可能出現鍵重複或者資料類型不一致等問題，一般進行資料處理時候需要對這些可能出現的錯誤提前考慮，避免更新失敗。Oralce給出了一些其他解決方案，以在不同情境下使用。

1、ignore_row_on_dupkey_index HINT

Oracle 11.2.0.1 版本中心加入的3個提示CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, RETRY_ON_ROW_CHANGE，與其他提示不同，特別之處在於存在"語義效果（semantic effect）"。

在 insert into tablea ...select * from tbl中，如果存在唯一約束，會導致整個insert操作失敗。使用IGNORE_ROW_ON_DUPKEY_INDEX提示，會忽略唯一約束衝突，復原當前行，繼續完成其他行的插入。

樣本：

資料準備：

create table emp1(empno number primary key,ename varchar2(50));

insert into emp1(empno,ename) select empno,ename from emp;

commit;

emp1表存在empno主鍵.

再次插入：

insert into emp1(empno,ename) select empno,ename from emp;

提示錯誤：

ORA-00001: 違反唯一約束條件 (SCOTT.SYS_C0013035)

使用HINT：

insert /*+ignore_row_on_dupkey_index(emp1,SYS_C0013035)*/into emp1(empno,ename) select empno,ename from emp;

提示：插入0行；

SYS_C0013035:建立主鍵時oracle自動產生的索引。

2、使用dbms_errlog包

說明：10g後可用，不支援LONG, CLOB, BLOB, BFILE, ADT資料類型

建立錯誤記錄檔表

begin

dbms_errlog.create_error_log(dml_table_name => ‘EMP1‘,

err_log_table_name => ‘T_ERR_LOG‘,

err_log_table_owner => user,

err_log_table_space => ‘users‘,

skip_unsupported => true);

end;

參數說明：

Parameter	Description
dml_table_name	The name of the DML table to base the error logging table on. The name can be fully qualified (for example, emp, scott.emp, "EMP", "SCOTT"."EMP"). If a name component is enclosed in double quotes, it will not be upper cased.
err_log_table_name	The name of the error logging table you will create. The default is the first 25 characters in the name of the DML table prefixed with‘ERR$_‘. Examples are the following: dml_table_name: ‘EMP‘, err_log_table_name: ‘ERR$_EMP‘ dml_table_name: ‘"Emp2"‘, err_log_table_name: ‘ERR$_Emp2‘
err_log_table_owner	The name of the owner of the error logging table. You can specify the owner indml_table_name. Otherwise, the schema of the current connected user is used.
err_log_table_space	The tablespace the error logging table will be created in. If not specified, the default tablespace for the user owning the DML error logging table will be used.
skip_unsupported	When set to TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table. When set to FALSE, an unsupported column type will cause the procedure to terminate. The default is FALSE.

 

對於不支援的資料類型可以使用最後一個參數控制，如果為true，不支援類型欄位將不會進入錯誤記錄檔表。如果是false，在遇到不支援類型欄位時執行包會報錯。

各參數預設值如下：

DBMS_ERRLOG.CREATE_ERROR_LOG (

dml_table_name IN VARCHAR2,

err_log_table_name IN VARCHAR2 := NULL,

err_log_table_owner IN VARCHAR2 := NULL,

err_log_table_space IN VARCHAR2 := NULL,

skip_unsupported IN BOOLEAN := FALSE);

注意：再次執行該包會報錯，執行前須確認錯誤記錄表不存在。

dml使用錯誤記錄檔表

insert into emp1(empno,ename) select empno,ename from emp log errors into t_err_log reject limit unlimited;

注意紅色字型部分。Limimt後面可以是具體數字，表示容錯行數

文法：

LOG ERRORS [INTO [schema.]table] [(‘simple_expression‘)] [REJECT LIMIT integer|UNLIMITED]

simple_expression：用來標記t_err_log表ora_err_tag$欄位資訊

Update、merge和delete也可以使用該方法。

更新完檢查t_err_log失敗記錄及錯誤原因。

Oracle DML容錯處理（1）