咨询ORA-01722: invalid number错误间歇性出现的原因

问题背景 #

我们的SQL查询使用条件result_value <> 0，其中result_value是Oracle EBS数据库的VARCHAR类型列。该查询多数时候正常运行，但会随机抛出错误：

ORA-01722: invalid number

将条件修改为result_value <> TO_CHAR('0')后，原本报错的场景下查询可正常执行。同时我们通过以下逻辑生成了alphanumeric列，用于标记result_value是否为数值型字符串：

CASE WHEN regexp_like(result_value , '^-?\d+(\.\d+)?$(E(\+|-)\d+)?$') 
     THEN 'numeric' 
     ELSE 'alpha' 
END alphanumeric

结果显示存在标记为alpha的非数值型result_value记录。

核心原因 #

• 隐式类型转换的执行计划随机性：当执行result_value <> 0时，Oracle会触发隐式类型转换——将VARCHAR类型的result_value转为数值类型，再和数字0比较。但Oracle的优化器会根据数据分布、统计信息、系统负载等因素动态选择执行计划：
  • 如果优化器选择先通过过滤条件排除非数值行，再做类型转换，查询就能正常运行；
  • 如果优化器选择先对全表做类型转换再过滤，一旦遇到无法转成数字的result_value（即alphanumeric列为alpha的行），就会抛出ORA-01722错误。
    这种执行计划的不确定性，导致了报错的“随机性”。
• 字符串比较避免类型转换：修改为result_value <> TO_CHAR('0')后，直接进行字符串层面的等值比较，不需要做任何类型转换，因此无论result_value是数值型字符串还是非数值内容，都能正常执行，不会触发转换错误。

内容的提问来源于stack exchange，提问作者Dito