隐式转换错误：ORA-01722: invalid number

oracle数据库有to_number和to_char的隐式转换。

实验如下：

SYS@192.168.80.100:1521/orcl > select * from test;

        ID NAME
---------- --------------------
         1 haohao
         1 1

SYS@192.168.80.100:1521/orcl > desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(2)
 NAME                                               VARCHAR2(20)

打开执行计划，并执行一个有where条件的select语句如下：

SYS@192.168.80.100:1521/orcl > set autot trace exp
SYS@192.168.80.100:1521/orcl > select * from test where name=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    25 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("NAME")=1)

从上面的执行计划可以看出Oracle数据库对name列进行了to_number的隐式转换，但是这条select语句会报错如下：

SYS@192.168.80.100:1521/orcl > select * from test where name=1;
select * from test where name=1
                         *
ERROR at line 1:
ORA-01722: invalid number

但是给条件的值加上单引号就不会报错
 SYS@192.168.80.100:1521/orcl > select * from test where name='1';

        ID NAME
---------- --------------------
         1 1

原因：因为test的name列中有有数字也有字符，oracle处理where name=1的时候是对name进行to_number的隐式转换，但是name列有个值是“haohao”，由此而报错

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29117696/viewspace-1084239/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29117696/viewspace-1084239/