Oracle10g新特性——記錄DML錯誤日誌（一）

當一個DML執行的時候，如果遇到了錯誤，則這條語句會整個回滾，就好像沒有執行過。不過對於一個大的DML而言，如果個別資料錯誤而導致整個語句的回滾，會浪費很多的資源和執行時間，從10g開始Oracle支援記錄DML語句的錯誤，而允許語句自動繼續執行。

這篇介紹DML記錄語句的用法。

 

 

看一個插入語句的簡單例子：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(10));

表已建立。

SQL> INSERT INTO T VALUES (1, 'A');

已建立 1 行。

SQL> INSERT INTO T VALUES (1, '1234567890A');
INSERT INTO T VALUES (1, '1234567890A')
                         *
第 1 行出現錯誤:
ORA-12899: 列 "YANGTK"."T"."NAME" 的值太大 (實際值: 11, 最大值: 10)

可以看到，由於插入的列超過了欄位定義的長度，導致了Oracle報錯。

下面建立記錄DML錯誤資訊的記錄表，透過DBMS_ERRLOG包來進行建立，而這個包目前只包括這一個過程：

SQL> DESC DBMS_ERRLOG
PROCEDURE CREATE_ERROR_LOG
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 DML_TABLE_NAME                 VARCHAR2                IN
 ERR_LOG_TABLE_NAME             VARCHAR2                IN     DEFAULT
 ERR_LOG_TABLE_OWNER            VARCHAR2                IN     DEFAULT
 ERR_LOG_TABLE_SPACE            VARCHAR2                IN     DEFAULT
 SKIP_UNSUPPORTED               BOOLEAN                 IN     DEFAULT

利用CREATE_ERROR_LOG來建立T表的DML錯誤記錄表：

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG')

PL/SQL 過程已成功完成。

SQL> DESC T_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)
 ID                                                                         VARCHAR2(4000)
 NAME                                                                       VARCHAR2(4000)

Oracle建立的錯誤記錄表包括錯誤號碼，錯誤資訊，記錄的ROWID資訊，錯誤操作型別，錯誤標籤，以及表中對應的列。

下面利用包含LOG ERROR語句的INSERT語句再次插入資料：

SQL> INSERT INTO T VALUES (1, '1234567890A')
  2  LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT 1;

已建立0行。

SQL> COL ID FORMAT A5
SQL> COL NAME FORMAT A12
SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_OPTYP$, ID, NAME
  2  FROM T_ERROR_LOG;

ORA_ERR_NUMBER$ OR ID    NAME
--------------- -- ----- ------------
          12899 I  1     1234567890A

SQL> SELECT ORA_ERR_MESG$
  2  FROM T_ERROR_LOG;

ORA_ERR_MESG$
---------------------------------------------------------------------------
ORA-12899: 列 "YANGTK"."T"."NAME" 的值太大 (實際值: 11, 最大值: 10)

可以看到，插入成功執行，但是插入記錄為0條。從對應的錯誤資訊表中已經包含了插入的資訊。而且從錯誤資訊表中還可以看到對應的錯誤號和詳細錯誤資訊。

下面詳細介紹一下LOG ERRORS的語法，INTO語句後面跟隨的就是指定的錯誤記錄表的表名。

在INTO語句後面，可以跟隨一個表示式，用來設定本次語句執行的錯誤在錯誤記錄表中對應的TAG：

SQL> INSERT INTO T VALUES (2, '1234567890A')
  2  LOG ERRORS INTO T_ERROR_LOG ('TEST') REJECT LIMIT 1;

已建立0行。

SQL> COL ORA_ERR_TAG$ FORMAT A12
SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_OPTYP$, ORA_ERR_TAG$, ID, NAME
  2  FROM T_ERROR_LOG
  3  WHERE ORA_ERR_TAG$ = 'TEST';

ORA_ERR_NUMBER$ OR ORA_ERR_TAG$ ID    NAME
--------------- -- ------------ ----- ------------
          12899 I  TEST         2     1234567890A

有了這個語句，就可以很輕易的在錯誤記錄表中找到某次操作所對應的所有的錯誤，這對於錯誤記錄表中包含了大量資料，且本次語句產生了多條錯誤資訊的情況十分有幫助。只要這個表示式的值可以轉化為字串型別就可以：

SQL> INSERT INTO T VALUES (2, '1234567890A')
  2  LOG ERRORS INTO T_ERROR_LOG (0) REJECT LIMIT 1;

已建立0行。

SQL> INSERT INTO T VALUES (2, '1234567890A')
  2  LOG ERRORS INTO T_ERROR_LOG (TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'))
  3  REJECT LIMIT 1;

已建立0行。

而REJECT LIMIT則限制語句出錯的數量。

SQL> SELECT LENGTH(TNAME), COUNT(*) FROM TAB
  2  GROUP BY LENGTH(TNAME)
  3  ORDER BY 1;

LENGTH(TNAME)   COUNT(*)
------------- ----------
            1          1
            3          2
            4          2
            5          6
            6          6
            7          2
            8          4
           10          4
           11          4
           12          4
           14          1
           17          1
           18          1
           20          1

已選擇14行。

從查詢結果中可以看到，表名長度超過10的記錄有12個，如果將表名作為NAME列的數值，插入到T表中，將有12條記錄無法成功插入：

SQL> INSERT INTO T
  2  SELECT ROWNUM, TNAME
  3  FROM TAB
  4  LOG ERRORS INTO T_ERROR_LOG
  5  ('REJECT10')
  6  REJECT LIMIT 10;
SELECT ROWNUM, TNAME
               *
第 2 行出現錯誤:
ORA-12899: 列 "YANGTK"."T"."NAME" 的值太大 (實際值: 17, 最大值: 10)

SQL> INSERT INTO T
  2  SELECT ROWNUM, TNAME
  3  FROM TAB
  4  LOG ERRORS INTO T_ERROR_LOG
  5  REJECT LIMIT 12;

已建立27行。

SQL> SELECT COUNT(*) FROM T_ERROR_LOG
  2  WHERE ORA_ERR_TAG$ = 'REJECT10';

  COUNT(*)
----------
        11

可以看到，當設定的REJECT LIMIT的值小於出錯記錄數時，語句會報錯，這時LOG ERRORS語句沒有起到應有的作用，插入語句仍然以報錯結束。而如果將REJECT LIMIT的限制設定大於等於出錯的記錄數，則插入語句就會執行成功。而所有出錯的資訊都會儲存到LOG ERROR對應的表中。

只要指定了LOG ERRORS語句，不管最終插入語句十分成功的執行完成，在錯誤記錄表中都會記錄語句執行過程中遇到的錯誤。比如第一個插入由於出錯數目超過REJECT LIMIT的限制，這時在記錄表中會存在REJECT LIMIT + 1條記錄數，因此這條記錄錯誤導致了整個SQL語句的報錯。

如果不管碰到多少錯誤，都希望語句能繼續執行，則可以設定REJECT LIMIT為UNLIMITED：

SQL> INSERT INTO T
  2  SELECT ROWNUM, TNAME
  3  FROM TAB
  4  LOG ERRORS INTO T_ERROR_LOG
  5  REJECT LIMIT UNLIMITED;

已建立27行。

SQL> ROLLBACK;

回退已完成。

SQL> SELECT COUNT(*) FROM T_ERROR_LOG;

  COUNT(*)
----------
        62

如果執行了回滾操作，可以看到，T_ERROR_LOG表中的記錄並不會減少，這說明Oracle是利用自治事務的方式插入錯誤記錄表的。