ORACLE报"ORA-01722:无效数字"的错误

一、错误类型及描述

1、错误类型

 

2、错误描述

a、关联表之后出现“无效数字”

b、日期转为字符类型出现“无效数字”

c、数字乘以空格出现“无效数字”

(需要特别注意空格字符)

d、表名写错出现"无效数字"

 

3、错误原因

a、数字不能与非数字字符外的其他字符关联(NULL除外)

SELECT * FROM (SELECT 1 a FROM DUAL)T1 LEFT JOIN (SELECT 'abd' b,'value' c FROM DUAL)T2 ON T1.A=T2.B

表T1                                      表T2

                 

用T1的A字段与T2的B字段关联就会出现“无效数字”

SELECT * FROM (SELECT 1 a FROM DUAL)T1 LEFT JOIN (SELECT '10' b,'value' c FROM DUAL)T2 ON T1.A=T2.B

当T2的B字段值为 '10'就不会这样的问题

因为ORACLE会自动把数字字符串转为数字，表T2如下

如果不确定某字段是否具有非数字字符串，可以用distinct来查询，有时候要注意NULL值和空格字符，因为看起来是一样的，但是点空格字符会发现有空格

 

b、日期字段原本就是varchar2类型，再用TO_CHAR就会出现错误

c、数字不能乘以数字字符外的其他字符(NULL除外)

SELECT 5*' ' FROM DUAL;

d、表名是ods.formtable,但是写成ods._formtable，不小心在前面多加一横就会出错。

 

二、解决方法

 1、对于a、c种错误原因，当数字与字符串类型的字段进行关联或者乘除操作时，要注意字符类型应为“数字字符”，或者通过case when 把非数字字符的值改为null

SELECT * FROM (SELECT 1 a FROM DUAL)T1 LEFT JOIN (SELECT 'abd' b,'value' c FROM DUAL)T2 ON T1.A=(case when regexp_like (t2.b,'([^.0-9])+') then null else t2.b end)

SELECT 5*(case when regexp_like (' ','([^.0-9])+') then null end)a FROM DUAL;

 

2、注意查看字段原本类型，如果日期字段原本就是字符类型，通过to_char转换成字符类型就会出错，可以通过substr来截取。

3、注意查看表名。