隱式轉換錯誤：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/，如需轉載，請註明出處，否則將追究法律責任。