DML Error Logging 特性
最近的项目中发现处理DML Error 时，逐条逐条处理1千多条的数据从临时表 insert 到正式表需要差不多1分钟的时间，性能相当低下，而Oracle 10g中的DML error logging对于DML异常处理性能卓著。原本打算写篇关于这个特性的文章，正好有经典篇章，于是乎，索性翻译供大家参考，有不尽完美之处，请大家拍砖。 缺省情况下，一个DML命令失败的时候，在侦测到错误之前，不论成功处理了多少条记录，都将将使得整个语句回滚。在使用DML error log之前，针对单行处理首选的办法是使用批量SQL FORALL 的SAVE EXCEPTIONS子句。而在Oracle 10g R2时，DML error log特性使得该问题得以解决。通过为大多数INSERT,UPDATE,MERGE,DELETE语句添加适当的LOG ERRORS子句，不论处理过程中是否出现错误，都可以使整个语句成功执行。这篇文章描述了DML ERROR LOGGING操作特性，并针对每一种情形给出示例。 一、语法 对于INSERT, UPDATE, MERGE 以及 DELETE 语句都使用相同的语法 LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED] 可选的INTO子句允许指定error logging table 的名字。如果省略它，则记录日志的表名的将以"ERR$_"前缀加上基表名来表示。 simple_expression表达式可以用于指定一个标记，更方便去判断错误。simple_expression能够为一个字符串或任意能转换成字符串的函数 REJECT LIMIT 通常用于判断当前语句所允许出现的最大错误数。缺省值是0，最大值则是使用UNLIMITED关键字。对于并行DML操作而言，REJECT LIMIT 会应用到每个并行服务器。 二、使用限制 下列情形使得DML error logging 特性失效 延迟约束特性 Direct-path INSERT 或MERGE 引起违反唯一约束或唯一索引 UPDATE 或 MERGE 引起违反唯一约束或唯一索引 除此之外，对于LONG,LOB,以及对象类型也不被支持。即使是一个包含这些列的表被作为错误日志记录目标表。 三、示例 下面的代码创建表并填充数据用于演示。
-- Create and populate a source table.
CREATE TABLE source
(
id NUMBER( 10 ) NOT NULL
,code VARCHAR2( 10 )
,description VARCHAR2( 50 )
,CONSTRAINT source_pk PRIMARY KEY( id )
);
DECLARE
TYPE t_tab IS TABLE OF source%ROWTYPE;
l_tab t_tab := t_tab( );
BEGIN
FOR i IN 1 .. 100000
LOOP
l_tab.EXTEND;
l_tab( l_tab.LAST ).id := i;
l_tab( l_tab.LAST ).code := TO_CHAR( i );
l_tab( l_tab.LAST ).description := 'Description for ' || TO_CHAR( i );
END LOOP;
-- For a possible error condition.
l_tab( 1000 ).code := NULL;
l_tab( 10000 ).code := NULL;
FORALL i IN l_tab.FIRST .. l_tab.LAST
INSERT INTO source
VALUES l_tab( i );
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);
-- Create a destination table.
CREATE TABLE dest
(
id NUMBER( 10 ) NOT NULL
,code VARCHAR2( 10 ) NOT NULL
,description VARCHAR2( 50 )
,CONSTRAINT dest_pk PRIMARY KEY( id )
);
-- Create a dependant of the destination table.
CREATE TABLE dest_child
(
id NUMBER
,dest_id NUMBER
,CONSTRAINT child_pk PRIMARY KEY( id )
,CONSTRAINT dest_child_dest_fk FOREIGN KEY( dest_id ) REFERENCES dest( id )
); 注意，code列在source 表中是可选，而在dest 表中是强制的 一旦基表创建之后，如果需要使用DML error logging 特性，则必须为该基表创建一个日志表用于记录基于该表上的DML错误。错误日志表能够 手动创建或者通过包中的CREATE_ERROR_LOG存储过程来创建。如下所示：
-- Create the error logging table.
BEGIN
DBMS_ERRLOG.create_error_log( dml_table_name => 'dest' );
END;
/
pl/SQL procedure successfully completed.
--缺省情况下，创建的日志表基于当前schema。日志表的所有者以及日志名字，表空间名字也可以单独指定。缺省的日志表的名字基于基表并以
--"ERR$_"前缀开头。
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'TEST';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST DEST USERS
TEST DEST_CHILD USERS
TEST ERR$_DEST USERS
TEST SOURCE USERS
4 rows selected.
--日志表的结构以及数据类型和所允许的最大长度依赖于基表，如下所示：
SQL> DESC err$_dest
Name Null? Type
--------------------------------- -------- --------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
ID VARCHAR2(4000)
CODE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)1、INSERT 操作 在前面创建演示表时，对于source表来说，其code 列可以为NULL，而dest表的code则不允许为NULL。在填充source表时，设置了两行为NULL的记录。 如果我们尝试从source 表复制数据到dest条，将获得下列错误信息
INSERT INTO dest
SELECT *
FROM source;
SELECT *
*
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
--source 表为NULL的两行将引起整个insert 语句回滚，无论在错误之间有多少条语句被成功插入。通过添加DML error logging 子句，则允许我们
--对那些有效数据实现成功插入。
INSERT INTO dest
SELECT *
FROM source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;
99998 rows created.
--那些未能成功插入的记录将被记录在ERR$_DEST中，并且也记录了错误的原因。
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'INSERT';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
2 rows selected.2、UPDATE 操作 下面的代码将尝试去更新1-10行的code列，其中8行的code值设置为自身，而第9与第10行设置为NULL。
UPDATE dest
SET code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE id BETWEEN 1 AND 10;
*
ERROR at line 2:
ORA-01407: cannot update ("TEST"."DEST"."CODE") to NULL
--如我们所期待的那样，语句由于code列不允许为NULL而导致操作失败。同样，通过添加DML erorr logging子句允许我们完成有效记录的操作
UPDATE dest
SET code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE id BETWEEN 1 AND 10
LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;
8 rows updated.
--同样地，update操作失败的行以及失败原因被记录在ERR$_DEST 表
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'UPDATE';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")3、MERGE 操作 下面的代码从dest表删除一些行，然后尝试从source 表合并数据到dest表
DELETE FROM dest
WHERE id > 50000;
MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code = b.code,
a.description = b.description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (b.id, b.code, b.description);
*
ERROR at line 9:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
--merge操作同样由于not null约束导致导致操作失败并且回滚。
--下面为其添加DML error logging 允许merge操作完成
MERGE INTO dest a
USING source b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.code = b.code,
a.description = b.description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (b.id, b.code, b.description)
LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;
99998 rows merged.
--更新操作失败的行以及失败原因同样被记录在ERR$_DEST 表中
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'MERGE';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
2 rows selected.4、DELETE 操作 DEST_CHILD 表有一个到dest表的外键约束，因此如果我们基于DEST表添加一些数据到dest_child，然后从dest删除记录将产生错误。
INSERT INTO dest_child (id, dest_id) VALUES (1, 100);
INSERT INTO dest_child (id, dest_id) VALUES (2, 101);
DELETE FROM dest;
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated - child record found
--对于Delete操作，同样可以添加DML error logging子句来记录错误使得整个语句成功执行 。
DELETE FROM dest
LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;
99996 rows deleted.
--下面是Delete操作失败的日志以及错误原因。
COLUMN ora_err_mesg$ FORMAT A69
SELECT ora_err_number$, ora_err_mesg$
FROM err$_dest
WHERE ora_err_tag$ = 'DELETE';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------------------
2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
child record found
2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
child record found
2 rows selected.四、后记 1、DML error logging特性使用了自治事务，因此不论当前的主事务是提交或回滚，其产生的错误信息都将记录在对应的日志表。 2、DML error logging使得错误处理得以高效实现，尽管如此，如果在操作中，很多表需要DML操作，尤其是数据迁移时，使得每一个表都 需要创建一个对应的日志表。做了一个测试，可以将日志表的一些基表列删除，保留主要列，日志依然可以成功记录以缩小日志大小。 3、能否将多张日志表合并到一张日志表，然后每一行数据中添加对应的表名以及主键等信息以鉴别错误，这样子的话，仅仅用少量的日志 表即可实现记录多张表上的DML error。这个还没有来得及测试，This is a question。
五、使用FORALL 的SAVE EXCEPTIONS子句示例
腾讯云开发者
