ORA-01722:invalid number

项目场景

Oracle表字段为VARCHAR2时,where条件出现NUMBER的匹配,可能会出现该错误。


问题描述

test表结构如下

CREATE TABLE “lzy”.“TEST”
( “NAME” VARCHAR2(255)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “lzy”

数据
在这里插入图片描述
已知字段NAMEVARCHAR2类型

SELECT * FROM TEST WHERE NAME = 1

数据只有1时,不报错;
数据包含非数字,使用上述sql 查询,报错 ORA-01722:invalid number

结论

当表中数据全部为 数字,不会报错;
当表中数据包含 非数字,会报错。


原因分析

带着上面的结论,我们查找原因

查看Oracle的sql执行计划

explain plan for SELECT * FROM TEST WHERE NAME = 1;
select * from TABLE(dbms_xplan.display);

如图,发现了端倪
在这里插入图片描述
oracle会将字段通过to_number函数强转为NUMBER类型,再进行比对过滤

问题就出现在to_number函数这里,非数字强转NUMBER会报错ORA-01722:invalid number


解决方案

方案一(推荐)

老老实实比对正确的字段类型

SELECT * FROM TEST WHERE NAME = '1';

字段是什么类型就比对什么类型

方案二(不推荐)

当表中该字段的数据全部为数字类型的值,强转不会报错,也就可以继续使用该sql查询
但不推荐使用,原因有两点
1.容错性低。一旦数据被污染,线上一直报错,等着扣工资吧
2.效率低。Oracle会先将该字段下的所有数据转成数字类型,进行比对过滤,会损失一部分性能
在这里插入图片描述