对ORA-01722 invalid number的误解
转:http://www.itpub.net/780426.html
一个查询 select to_number(c.name) as srvtype, value as typename from sys_code c where c.srvclass=9 --srvclass为字符型
一直工作得很好,但突然一天返回错误ORA-01722 invalid number。由于条件srvclass字段是varchar2类型,就想当然地以为是ORACLE的bug(恰巧上周刚确认了ORACLE的一个查询bug),将条件改写成c.srvclass='9'后,查询就又能运行了。
事情虽然过去了,可总觉得有点不对劲。首先ORACLE不可能出现这么简单的BUG;其次就算是BUG,返回的错误提示也不应该是 invalid number。按理说,即使ORACLE不能自动完成类型转换而要求写成 srvclass='9',那么对srvclass=9这种写法的错误提示也应该是invalid character。但由于直觉作怪,也就没有深究
正好space6212提出了他对bug解释的疑问,我就从头进行检查,才发现错误的根本原因是:ORACLE将where c.srvclass=9解释为where to_number(c.srvclass)=9
1)以前执行SQL时,ORACLE进行全表扫描,对每行的srvclass都转换为number型进行比较.以前表中的srvclass的取值只有字符0到9,所以没有出错;
2)后来表中加入了新数据,srvclass的取值都是字母串,ORACLE进行全表扫描时,对新行上srvclass的to_number转换当然就返回ORA-01722 invalid number了。
谢谢space6212,让我有机会改正错误
转:http://www.itpub.net/780426.html
一个查询 select to_number(c.name) as srvtype, value as typename from sys_code c where c.srvclass=9 --srvclass为字符型
一直工作得很好,但突然一天返回错误ORA-01722 invalid number。由于条件srvclass字段是varchar2类型,就想当然地以为是ORACLE的bug(恰巧上周刚确认了ORACLE的一个查询bug),将条件改写成c.srvclass='9'后,查询就又能运行了。
事情虽然过去了,可总觉得有点不对劲。首先ORACLE不可能出现这么简单的BUG;其次就算是BUG,返回的错误提示也不应该是 invalid number。按理说,即使ORACLE不能自动完成类型转换而要求写成 srvclass='9',那么对srvclass=9这种写法的错误提示也应该是invalid character。但由于直觉作怪,也就没有深究
正好space6212提出了他对bug解释的疑问,我就从头进行检查,才发现错误的根本原因是:ORACLE将where c.srvclass=9解释为where to_number(c.srvclass)=9
1)以前执行SQL时,ORACLE进行全表扫描,对每行的srvclass都转换为number型进行比较.以前表中的srvclass的取值只有字符0到9,所以没有出错;
2)后来表中加入了新数据,srvclass的取值都是字母串,ORACLE进行全表扫描时,对新行上srvclass的to_number转换当然就返回ORA-01722 invalid number了。
谢谢space6212,让我有机会改正错误