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

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

這篇介紹DML記錄語句不支援的資料型別。

Oracle10g新特性——記錄DML錯誤日誌（一）：http://yangtingkun.itpub.net/post/468/479317

Oracle10g新特性——記錄DML錯誤日誌（二）：http://yangtingkun.itpub.net/post/468/479366

 

除了上一篇文章介紹的不支援的操作外，DML記錄錯誤語句還有一些不支援的資料型別，比如：LONG、LONG RAW、BLOG、CLOB、NCLOB、BFILE以及各種物件型別。

Oracle不支援這些型別的原因也很簡單，這些特殊的型別不是包含了大量的記錄，就是需要透過特殊的方法來讀取，因此Oracle沒有辦法在SQL處理的時候將對應列的資訊寫到錯誤記錄表中。

SQL> DROP TABLE T PURGE;

表已刪除。

SQL> DROP TABLE T_ERROR_LOG PURGE;

表已刪除。

SQL> CREATE TABLE T
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  RESUME CLOB);

表已建立。

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

*
第 1 行出現錯誤:
ORA-20069: Unsupported column type(s) found: RESUME
ORA-06512: 在 "SYS.DBMS_ERRLOG", line 233
ORA-06512: 在 line 1

可以看到，由於T表擁有不支援的列，導致建立錯誤記錄表的過程報錯，錯誤提示就是T表中包含了不支援的列。

如果手工新增CLOB欄位到錯誤記錄表：

SQL> ALTER TABLE T DROP (RESUME);

表已更改。

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

PL/SQL 過程已成功完成。

SQL> ALTER TABLE T ADD RESUME CLOB;

表已更改。

SQL> ALTER TABLE T_ERROR_LOG ADD RESUME CLOB;

表已更改。

下面創始執行DML語句：

SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST')
  2  LOG ERRORS INTO T_ERROR_LOG
  3  REJECT LIMIT 1;
LOG ERRORS INTO T_ERROR_LOG
                *
第 2 行出現錯誤:
ORA-38904: LOB 列 "RESUME" 不支援 DML 錯誤事件記錄

SQL> INSERT INTO T VALUES (1, 'A', 'TEST')
  2  LOG ERRORS INTO T_ERROR_LOG
  3  REJECT LIMIT 1;
LOG ERRORS INTO T_ERROR_LOG
                *
第 2 行出現錯誤:
ORA-38904: LOB 列 "RESUME" 不支援 DML 錯誤事件記錄

可以看到，Oracle會直接報錯。而且第二個INSERT語句的執行也會報錯，說明Oracle是在執行之前檢查了錯誤記錄表的資料型別，而不是在執行的時候才去處理。

即使Oracle的DML並不包含不支援列的資料，Oracle也會報錯：

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

已建立 1 行。

SQL> UPDATE T SET NAME = 'B'
  2  WHERE ID = 1
  3  LOG ERRORS INTO T_ERROR_LOG
  4  REJECT LIMIT 1;
LOG ERRORS INTO T_ERROR_LOG
                *
第 3 行出現錯誤:
ORA-38904: LOB 列 "RESUME" 不支援 DML 錯誤事件記錄

下面創始刪除T_ERROR_LOG表中的RESUME列：

SQL> ALTER TABLE T_ERROR_LOG DROP (RESUME);

表已更改。

SQL> UPDATE T SET NAME = 'B'
  2  WHERE ID = 1
  3  LOG ERRORS INTO T_ERROR_LOG
  4  REJECT LIMIT 1;

已更新 1 行。

SQL> UPDATE T SET NAME = LPAD('A', 31, 'A')
  2  WHERE ID = 1
  3  LOG ERRORS INTO T_ERROR_LOG
  4  REJECT LIMIT 1;

已更新0行。

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

已建立0行。

SQL> SELECT COUNT(*) FROM T_ERROR_LOG;

  COUNT(*)
----------
         2

可以看到，刪除錯誤記錄語句所不支援的列後，LOG ERRORS語句反而可以順利執行，而且無論DML語句是否包括哪些不支援列的資料。

不過，對於一個已經在使用的表，是不可能為了新增錯誤記錄表而刪除不支援的列，而後在新增回來的，這沒有關係，完全可以透過其他表中間表來實現：

SQL> DROP TABLE T_ERROR_LOG PURGE;

表已刪除。

SQL> CREATE TABLE T_INTER AS SELECT ID, NAME FROM T;

表已建立。

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

PL/SQL 過程已成功完成。

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

已建立0行。

可以看到，雖然CREATE_ERROR_LOG過程在建立T_ERROR_LOG表時指定的是T_INTER表，但是這個過程實際上只是根據源表來獲取列的資訊，而並沒有在源表和錯誤記錄表之間建立任何的關係，所以只要列滿足條件，任何途徑建立的錯誤記錄表都可以使用。

錯誤記錄表還可以手工建立：

SQL> CREATE TABLE T_ERROR_LOG1 AS SELECT * FROM T_ERROR_LOG
  2  WHERE 1 = 0;

表已建立。

SQL> DROP TABLE T_ERROR_LOG PURGE;

表已刪除。

SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST')
  2  LOG ERRORS INTO T_ERROR_LOG1
  3  REJECT LIMIT 1;

已建立0行。