昨天嘗試使用BEFORE觸發器記錄或修正匯入過程中出現的資料型別超長的錯誤，結果沒成功，這裡研究一下這個問題。

獲取導致匯入失敗的資料http://yangtingkun.itpub.net/post/468/461401

首先建立測試用表：

SQL> CREATE TABLE T_IMP (ID NUMBER, NAME VARCHAR2(30));

表已建立。

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

已建立 1 行。

SQL> INSERT INTO T_IMP VALUES (2, 'B');

已建立 1 行。

SQL> INSERT INTO T_IMP VALUES (1000, 'C');

已建立 1 行。

SQL> INSERT INTO T_IMP VALUES (3, 'D');

已建立 1 行。

SQL> COMMIT;

提交完成。

下面匯出資料：

E:\>EXP YANGTK/YANGTK FILE=T_IMP.DMP TABLES=T_IMP

Export: Release 9.2.0.4.0 - Production on 星期四 5月 8 23:28:26 2008

連線到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將匯出指定的表透過常規路徑 ...
. . 正在匯出表 T_IMP 4 行被匯出
在沒有警告的情況下成功終止匯出。

為了構造錯誤，下面清空表的資料，並修改表中ID列的精度，使得剛才匯出的資料無法成功的匯入：

SQL> TRUNCATE TABLE T_IMP;

表被截斷。

SQL> ALTER TABLE T_IMP MODIFY ID NUMBER(3);

表已更改。

下面執行匯入操作：

E:\>IMP YANGTK/YANGTK FILE=T_IMP.DMP TABLES=T_IMP IGNORE=Y

Import: Release 9.2.0.4.0 - Production on 星期四 5月 8 23:30:05 2008

連線到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

經由常規路徑匯出由EXPORT:V09.02.00建立的檔案
已經完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的匯入
. 正在將YANGTK的物件匯入到 YANGTK
. . 正在匯入表 "T_IMP"
IMP-00058: 遇到 ORACLE 錯誤 1438
ORA-01438: 值大於此列指定的允許精確度
IMP-00028: 上一個表的部分匯入已回退: 回退 2 行
成功終止匯入，但出現警告。

錯誤已經重現，下面可以建立日誌表和BEFORE觸發器了：

SQL> CREATE TABLE T_IMP_BAK (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> CREATE OR REPLACE TRIGGER T_IMP
2 BEFORE INSERT ON T_IMP
3 FOR EACH ROW
4 DECLARE
5   PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7   INSERT INTO T_IMP_BAK VALUES (:NEW.ID, :NEW.NAME);
8   COMMIT;
9 END;
10 /

觸發器已建立

建立BEFORE觸發器，且設定觸發器為自治事務，是為了在錯誤發生之前，將導致錯誤產生的資料插入到日誌表中，且插入的記錄不會隨著匯入的失敗而回滾。

再次執行匯入操作：

E:\>IMP YANGTK/YANGTK FILE=T_IMP.DMP TABLES=T_IMP IGNORE=Y

Import: Release 9.2.0.4.0 - Production on 星期四 5月 8 23:33:30 2008

連線到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

經由常規路徑匯出由EXPORT:V09.02.00建立的檔案
已經完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的匯入
. 正在將YANGTK的物件匯入到 YANGTK
. . 正在匯入表 "T_IMP"
IMP-00058: 遇到 ORACLE 錯誤 1438
ORA-01438: 值大於此列指定的允許精確度
IMP-00028: 上一個表的部分匯入已回退: 回退 2 行
成功終止匯入，但出現警告。

檢查日誌表的記錄：

SQL> SELECT * FROM T_IMP_BAK;

        ID NAME
---------- ------------------------------
         1 A
         2 B

發現預期的導致錯誤發生的記錄並未寫入到日誌表中。

導致這種情況出現的可能性有很多中，比如BEFORE觸發器並不是想象中的那樣真的在插入之前觸發；IMP工具的特殊性導致自治事務也被回滾；錯誤並非發生在插入過程中，而是發生在插入之前；Oracle的bug導致的問題等等。

下面繼續驗證到底是何種原因導致了當前的問題：

SQL> CREATE OR REPLACE TRIGGER T_IMP
2 BEFORE INSERT ON T_IMP
3 FOR EACH ROW
4 DECLARE
5   PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7   INSERT INTO T_IMP_BAK VALUES (:NEW.ID, :NEW.NAME);
8   COMMIT;
9   IF :NEW.ID > 999 THEN
10    :NEW.ID := 999;
11   END IF;
12 END;
13 /

觸發器已建立

SQL> INSERT INTO T_IMP VALUES (1000, 'C');
INSERT INTO T_IMP VALUES (1000, 'C')
*
第 1 行出現錯誤:
ORA-01438: 值大於此列指定的允許精確度

修改觸發器，在插入日誌後，修改要插入到目標表的值，使得超過精度限制的資料縮小到可以正常插入的範圍內，並嘗試使用SQL語句來執行插入操作。

結果發現，錯誤依舊。難道這個錯誤的產生真的不是在插入的時候，而是在插入之前就會數值進行了判斷。

嘗試修改列的精度，使得插入資料的原值可以順利插入：

SQL> ALTER TABLE T_IMP MODIFY ID NUMBER(4);

表已更改。

SQL> INSERT INTO T_IMP VALUES (1000, 'C');

已建立 1 行。

SQL> SELECT * FROM T_IMP;

ID NAME
---------- ------------------------------
999 C

SQL> SELECT * FROM T_IMP_BAK;

        ID NAME
---------- ------------------------------
         1 A
         2 B
      1000 C

現在已經可以確定，Oracle會先判斷修改的值是否滿足表欄位的精度設定，然後才會呼叫BEFORE觸發器，接著就是SQL語句本身的執行，最後呼叫AFTER觸發器。

看來利用BEFORE觸發器修改超過表欄位精度的資料來實現資料正常匯入的方式是行不通的。BEFORE觸發器的修改只能針對那些本身就滿足欄位限制的資料。

BEFORE觸發器修正資料錯誤

相關文章