PL/SQL 批量 UPDATE 報「值超過列精度」如何快速定位問題行

現象描述

SELECT ... FOR UPDATE 粘貼幾百行數據執行時報錯：ORA-01438: value larger than specified precision allowed for this column，不知哪行哪列超標。

快速定位三板斧

1. 臨時擴大列精度（最暴力）

ALTER TABLE your_table MODIFY (problem_col NUMBER(38,10)); -- 先擴大
-- 執行 UPDATE
-- 成功後再縮回原精度（若業務允許）

適合一次性修復上線腳本，開發環境首選。

2. PL/SQL 逐行捕獲異常（精準定位）

DECLARE
    CURSOR c_data IS SELECT rowid, col1, col2, ... FROM your_table WHERE ...;
    v_err_msg VARCHAR2(4000);
BEGIN
    FOR rec IN c_data LOOP
        BEGIN
            UPDATE your_table SET col1 = rec.col1, col2 = rec.col2 ... WHERE rowid = rec.rowid;
        EXCEPTION
            WHEN OTHERS THEN
                v_err_msg := SQLERRM;
                DBMS_OUTPUT.PUT_LINE('Rowid: ' || rec.rowid || ' Error: ' || v_err_msg);
                -- 可選：記錄到日誌表
        END;
    END LOOP;
END;
/

輸出會精確顯示哪個 ROWID 導致超精度。

3. 正則掃描數據文件（無數據庫環境）

將幾百行數據導出為 CSV，用 Python/PowerShell 掃描：

import re, csv
with open('data.csv') as f:
    reader = csv.DictReader(f)
    for i, row in enumerate(reader):
        for col, val in row.items():
            if re.match(r'^\d{15,}$', val):  # 整數位超 14 位
                print(f'Row {i} Col {col} value {val} may overflow NUMBER(14,2)')

預防措施

• 建表時根據業務上限預留 20% 精度冗餘（如金額用 NUMBER(18,4) 而非 NUMBER(10,2)）。
• ETL 流程中加入數據質量檢查節點，入庫前校驗長度、精度、非空、外鍵關聯。
• 使用 DBMS_SQL 動態綁定變量，由驅動程序自動檢查類型溢出。