Oracle SQL替换WHERE条件值为112时触发ORA-01722无效数字错误

问题涉及的SQL如下：

WITH 
TEST_RESULT_CTE AS
(SELECT R.DSDW_RESULT_ID, 
R.PARAM_VALUE AS TEST_ID
FROM SLIMS_POC_RESULT_DETAIL R
JOIN SLIMS_POC_PARAMETER P ON P.PARAM_ID=R.PARAM_ID
WHERE P.PARAMETER_NAME='TEST_ID' AND P.CATEGORY = 'Result' )

SELECT * FROM
(
SELECT S.SAMPLE_ID, R.DSDW_RESULT_ID, PARA.PARAMETER_NAME as PNAME, R.PARAM_VALUE as PVALUE
FROM SLIMS_POC_RESULT_DETAIL R
JOIN TEST_RESULT_CTE TR ON TR.DSDW_RESULT_ID = R.DSDW_RESULT_ID
JOIN SLIMS_POC_TEST T ON T.TEST_ID = TR.TEST_ID
JOIN SLIMS_POC_SAMPLE S ON S.SAMPLE_ID = T.SAMPLE_ID --AND S.SAMPLE_ID = to_char(113)
JOIN SLIMS_POC_PARAMETER PARA ON PARA.PARAM_ID=R.PARAM_ID AND PARA.CATEGORY='Result'
)
Result_Data
PIVOT
(
  MAX(PVALUE) FOR PNAME IN ( 'TEST_ID', 'RESULT_NAME', 'UNIT', 'RESULT_TEXT', 'VALUE', 'STATUS', 'ENTERED_ON', 'ENTERED_BY', 'RESULT_TYPE' )
) PIVOTED_TAB
WHERE SAMPLE_ID > 111
ORDER BY SAMPLE_ID;

问题现象 #

上述SQL将WHERE子句过滤条件写为SAMPLE_ID > 111时可正常返回结果，将阈值替换为112后执行抛出如下错误：

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

根因分析 #

这个问题本质是隐式类型转换+Oracle执行计划随过滤条件变化共同导致的：

1. 从SQL里的注释--AND S.SAMPLE_ID = to_char(113)可以判断，SLIMS_POC_SAMPLE表的SAMPLE_ID字段是字符串类型（VARCHAR2/CHAR），当你写SAMPLE_ID > 111时，右侧是数值类型，Oracle会自动做隐式类型转换，等价于执行TO_NUMBER(S.SAMPLE_ID) > 111，要把所有参与判断的SAMPLE_ID值转成数字再比较。
2. Oracle会根据过滤阈值的不同选择不同的执行计划，JOIN顺序、谓词推送范围、表访问路径都会变化：
   • 阈值为111时，执行计划可能先筛选出符合条件的小范围SAMPLE_ID再做关联转换，这部分被命中的SAMPLE_ID值全是合法数字，转换不会出错；
   • 阈值改为112时，执行计划调整，会对更大范围的SAMPLE_ID值做TO_NUMBER转换，这部分数据里包含了非数字内容的脏值（比如带字母、特殊符号的SAMPLE_ID），转换失败就抛出ORA-01722错误。
3. 额外风险点：CTE中TEST_ID取自字符串类型的PARAM_VALUE字段，如果关联的SLIMS_POC_TEST.TEST_ID是数值类型，这个JOIN条件也存在同样的隐式转换风险，在特定执行计划下也可能触发同类错误。

修复方案 #

• 统一比较类型，杜绝隐式转换：如果SAMPLE_ID是字符串类型，比较时右侧值也用字符串格式，将条件改为WHERE SAMPLE_ID > '112'即可。注意字符串比较是字典序，如果SAMPLE_ID是长度不固定的数字字符串，字典序和数值序结果可能不一致（比如'99' > '100'在字符串比较下成立），这种场景建议用下一种方案。
• 排查修正脏数据：如果业务规则要求SAMPLE_ID必须为纯数字，先执行查询找出脏数据：SELECT SAMPLE_ID FROM SLIMS_POC_SAMPLE WHERE NOT REGEXP_LIKE(SAMPLE_ID, '^[0-9]+$')，修正不符合规则的SAMPLE_ID值，从根源避免转换失败。
• 显式做数值转换加容错：如果需要按数值大小比较SAMPLE_ID，不要依赖隐式转换，显式写转换逻辑，Oracle 12c及以上版本可以用容错转换语法：WHERE TO_NUMBER(SAMPLE_ID DEFAULT NULL ON CONVERSION ERROR) > 112，遇到无法转数字的脏值直接返回空，不会中断查询；低版本可以用CASE WHEN先判断值是否为纯数字再做转换。
• 统一JOIN条件的字段类型：检查T.TEST_ID = TR.TEST_ID的两边字段类型，如果一个是字符串一个是数字，要么调整表结构统一类型，要么比较时做显式类型转换，消除隐式转换风险。

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