DML Error Logging 特性

3121321321

最近的项目中发现处理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子句示例

FORALL 之 SAVE EXCEPTIONS 子句应用一例