Oracle ORA-01722「無效數字」錯誤根因分析與解決方案

錯誤現場

SELECT code_id code, code_value name
FROM code_value_t t
WHERE code_id LIKE '0_'
  AND class_code = 22  -- 此處報 ORA-01722
ORDER BY code_id;

同一 SQL、同一表結構、同版本數據庫，一庫正常、一庫報錯。

根因深度剖析

1. 隱式類型轉換： class_code 定義為 VARCHAR2，但 SQL 中使用數字字面量 22。Oracle 將執行 TO_NUMBER(class_code) 進行比較。
2. 髒數據存在： 報錯庫的 code_value_t 表中存在非數字字符的 class_code 值 (如 'ABC'、' 22 ' 含空格)。
3. 執行計劃差異： 正常庫可能先應用 code_id LIKE '0_' 過濾掉髒數據，或走索引訪問避開全表掃描；報錯庫因統計信息/參數差異選擇全表掃描，導致隱式轉換作用於髒數據行。

解決方案 (按推薦順序)

1. 修正 SQL 字面量類型 (最快、零風險)

AND class_code = '22'  -- 加單引號，避免隱式轉換

2. 清理髒數據 (治本)

-- 定位非數字行
SELECT rowid, class_code FROM code_value_t
WHERE NOT REGEXP_LIKE(class_code, '^\d+$');

-- 刪除或修正
DELETE FROM code_value_t WHERE rowid = '...';

3. 創建函數索引 (不改 SQL 情況下)

CREATE INDEX idx_code_value_t_class_num ON code_value_t
  (CASE WHEN REGEXP_LIKE(class_code, '^\d+$') THEN TO_NUMBER(class_code) END);

優化器可利用該索引跳過非數字行。

4. 修改表結構 (長遠)

ALTER TABLE code_value_t MODIFY class_code NUMBER(10);  -- 需先清理數據

最佳實踐總結

• 永遠不要依賴隱式類型轉換，字面量與列類型保持一致。
• 建表階段為業務鍵列添加 CHECK (REGEXP_LIKE(col, '^\d+$')) 約束。
• 定期跑 DBMS_STATS 並檢查 USER_TAB_COL_STATISTICS 的 LOW_VALUE/HIGH_VALUE 發現異常值。
• 開發規範：WHERE 子句比較 VARCHAR2 列時強制加引號，Code Review 必檢項。