一個有關"ORA-01722: 無效數字"的小案例解決過程

wxjzqym發表於2013-12-12

    之前接到朋友電話,詢問一個有關ORA-01722問題,在某個sql語句的where條件中使用"列名=數字"時會觸發該錯誤,但是用"列名='數字'"時確沒問題,此列為字元型別。當時第一反應就是即使在第一種情況下oracle會發生自動隱身轉換,但是也不會報此錯誤啊,於是打算模擬一下該錯誤,在模擬過程中就找到了最終的原因,具體操作步驟如下:

1.建立測試表
SQL> create table t(name varchar2(10));

2.插入資料
SQL> insert into t values('123');
SQL> select * from t;

NAME
----------
123

3.執行正常sql並獲取執行計劃
SQL> select * from t where name='123';

NAME
----------
123

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

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

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

   1 - filter("NAME"='123')

注意:執行計劃中的fliter為(NAME='123')

4.模擬錯誤並獲取執行計劃
SQL> insert into t values('abc');
SQL> select * from t;

NAME
----------
123
abc

SQL> select * from t where name=123;
ERROR:
ORA-01722: 無效數字

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

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

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

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

特別注意:執行計劃中的filter為TO_NUMBER(NAME)=123,這裡oracle發生了隱身轉換,但是name列中因為有'abc'這種字元值,
所以to_number('abc')這個操作觸發了ORA-01722的錯誤。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/20801486/viewspace-1063052/,如需轉載,請註明出處,否則將追究法律責任。

相關文章