ORA-01722: invalid number 一例

今天有同事询问ORA-01722: invalid number的错误，简单做如下测试：

RYAN@RACTSRT_1> create table tst1(id varchar2(10)); －－－id字段时varchar2类型

Table created.

RYAN@RACTSRT_1> insert into tst1 values(18);  －－－转换为字符‘18’

1 row created.

RYAN@RACTSRT_1> select * from tst1;

ID
----------
18

RYAN@RACTSRT_1> select * from tst1 where id=18;  －－－where条件用的是number类型查询

ID
----------
18

RYAN@RACTSRT_1> insert into tst1 values('moon'); －－－插入字符'moon'

1 row created.

RYAN@RACTSRT_1> select * from tst1 where id=18;  －－这里再次用number类型去查询会报错
ERROR:
ORA-01722: invalid number



no rows selected

RYAN@RACTSRT_1>  select * from tst1 where id='18'; －－－用字符类型去查询不会报错

ID
----------
18


RYAN@RACTSRT_1> create index ind_id_tst1 on tst1(id); －－－建立一个索引

Index created.

RYAN@RACTSRT_1> set autotrace on;

RYAN@RACTSRT_1> select * from tst1 where id=18;
ERROR:
ORA-01722: invalid number

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2178032767

--------------------------------------------------------------------------
 Id   Operation          Name  Rows   Bytes  Cost (%CPU) Time     
--------------------------------------------------------------------------
   0  SELECT STATEMENT             1      7      3   (0) 00:00:01 
*  1   TABLE ACCESS FULL TST1      1      7      3   (0) 00:00:01  －－全表扫描
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("ID")=18)     －－－－－－－隐式转换id列下的所有值去匹配数字类型18，因为有一个‘moon’值无法转换为数字类型所以报错


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


RYAN@RACTSRT_1> select * from tst1 where id='18';

ID
----------
18


Execution Plan
----------------------------------------------------------
Plan hash value: 3055109003

--------------------------------------------------------------------------------
 Id   Operation         Name         Rows   Bytes  Cost (%CPU) Time     
--------------------------------------------------------------------------------
   0  SELECT STATEMENT                   1      4      1   (0) 00:00:01 
*  1   INDEX RANGE SCAN IND_ID_TST1      1      4      1   (0) 00:00:01 －－－注意这里走索引扫
--------------------------------------------------------------------------------

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

   1 - access("ID"='18')  －－－不再有隐式转换


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed