Oracle DML 錯誤日誌：10gR2 新特性，讓批次載入不因單筆錯誤中斷

Oracle DML 錯誤日誌：10gR2 新特性，讓批次載入不因單筆錯誤中斷

DML Error Logging 允許 INSERT/UPDATE/DELETE/MERGE 遇到行級錯誤 (型別轉換失敗、約束違反、空值違反) 時，將錯誤行導向錯誤日誌表，正常行繼續執行，事務不回滾。類似 SQL*Loader 的 BAD FILE 機制，但純 SQL 實現。

建立錯誤日誌表

-- 原表
DESC my_test;
-- SID NUMBER, NAME VARCHAR2(20), GENDER INTEGER, AGE NUMBER, HOBBY VARCHAR2(20)

-- 執行套件建立日誌表
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('my_test', 'my_test_bad');

-- 日誌表結構
DESC my_test_bad;
-- ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$, ORA_ERR_TAG$
-- 原表所有欄位 (轉為 VARCHAR2(4000))

ORA_ERR_TAG$ 可存放自定義標記 (如批次號)。

使用 LOG ERRORS 子句

-- 插入含錯誤資料 (AGE 欄位放字串 'age')
INSERT INTO my_test (sid, name, gender, age, hobby)
VALUES (12, 'joy', 2, 'age', 'book,football,run')
LOG ERRORS INTO my_test_bad REJECT LIMIT UNLIMITED;

-- 查詢錯誤日誌
SELECT * FROM my_test_bad;

結果包含：錯誤代碼 (1722)、錯誤訊息、操作類型 (I)、原始行資料。

支援的 DML 操作

UPDATE my_test SET age = 'yyy' WHERE sid = 12 LOG ERRORS INTO my_test_bad;
DELETE FROM my_test WHERE sid = 12 LOG ERRORS INTO my_test_bad;
MERGE INTO ... LOG ERRORS INTO my_test_bad;

關鍵特性

• 無隱含提交：LOG ERRORS 不觸發自動提交。若事務回滾，錯誤日誌仍保留 (獨立事務)。
• REJECT LIMIT：預設 0 (遇錯即停)；設 UNLIMITED 或具體數值允許繼續。
• 不支援：分區表的分區擴展語法、LONG/LOB 欄位、分散式事務、觸發器中直接 DML。

應用場景

• ETL 批次載入：單筆髒資料不阻斷整批次。
• 資料遷移驗證：快速定位問題行。
• 生產環境緊急修正：邊跑邊記錯誤。