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_nameFROM   all_tablesWHERE  owner = 'TEST';OWNER                          TABLE_NAME                     TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------TEST                           DEST                           USERSTEST                           DEST_CHILD                     USERSTEST                           ERR$_DEST                      USERSTEST                           SOURCE                         USERS4 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 destSELECT *FROM   source;SELECT *       *ERROR at line 2:ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")--source 表為NULL的兩行將引起整個insert 語句復原，無論在錯誤之間有多少條語句被成功插入。通過添加DML error logging 子句，則允許我們--對那些有效資料實現成功插入。INSERT INTO destSELECT *FROM   sourceLOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;99998 rows created.--那些未能成功插入的記錄將被記錄在ERR$_DEST中，並且也記錄了錯誤的原因。COLUMN ora_err_mesg$ FORMAT A70SELECT ora_err_number$, ora_err_mesg$FROM   err$_destWHERE  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 destSET    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 destSET    code = DECODE(id, 9, NULL, 10, NULL, code)WHERE  id BETWEEN 1 AND 10LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;8 rows updated.--同樣地，update操作失敗的行以及失敗原因被記錄在ERR$_DEST 表COLUMN ora_err_mesg$ FORMAT A70SELECT ora_err_number$, ora_err_mesg$FROM   err$_destWHERE  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 destWHERE  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 A70SELECT ora_err_number$, ora_err_mesg$FROM   err$_destWHERE  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 destLOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;99996 rows deleted.--下面是Delete操作失敗的日誌以及錯誤原因。COLUMN ora_err_mesg$ FORMAT A69SELECT ora_err_number$, ora_err_mesg$FROM   err$_destWHERE  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 found2 rows selected.

四、後記
        1、DML error logging特性使用了自治事務，因此不論當前的主事務是提交或復原，其產生的錯誤資訊都將記錄在對應的日誌表。
        2、DML error logging使得錯誤處理得以高效實現，儘管如此，如果在操作中，很多表需要DML操作，尤其是資料移轉時，使得每一個表都
                需要建立一個對應的日誌表。做了一個測試，可以將日誌表的一些基表列刪除，保留主要列，日誌依然可以成功記錄以縮小日誌大小。
        3、能否將多張日誌表合并到一張日誌表，然後每一行資料中添加對應的表名以及主鍵等資訊以鑒別錯誤，這樣子的話，僅僅用少量的日誌
                表即可實現記錄多張表上的DML error。這個還沒有來得及測試，This is a question。

五、使用FORALL 的SAVE EXCEPTIONS子句樣本

    FORALL 之 SAVE EXCEPTIONS 子句應用一例