萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle ORA-01722 Invalid Number 錯誤根因與解決方案

錯誤重現場景


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 定義為 NUMBER,但表中存在非數字字串 (如 'ABC')。
  2. Oracle 隱性轉換:class_code = 22 會嘗試將 class_code 轉為數字比較。
  3. 遇到非數字值時拋出 ORA-01722。
  4. 另一庫無髒資料,故正常執行。

解決方案

方案 1:修正 SQL 寫法 (推薦)


AND class_code = '22'  -- 以字串比較,避免隱性轉換
-- 或
AND TO_CHAR(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 = '...';
COMMIT;

方案 3:加上檢查約束防止未來髒資料


ALTER TABLE code_value_t ADD CONSTRAINT ck_class_code_num 
CHECK (REGEXP_LIKE(class_code, '^\d+$'));

最佳實踐原則

  • 永遠顯式指定字面值型別:數字用 22,字串用 '22',日期用 DATE '2024-01-01'。
  • 避免隱性轉換:WHERE num_col = 'str'、WHERE date_col = 'str' 皆可能報錯或導致索引失效。
  • 資料建模階段:數值業務欄位嚴格定義 NUMBER,拒絕 VARCHAR2 存儲數字。
  • ETL/遷移階段:以 REGEXP_LIKE、VALIDATE_CONVERSION (12.2+) 預檢資料品質。
copyright © 萬盛學電腦網 all rights reserved