本文將展示三個導致ORA-01722錯誤的場景，同時給出具體問題的分析過程和原因。

1.問題現象及原因分析
1）模擬兩種常見的錯誤場景
（1）模擬插入資料時報錯
sec@ora10g> create table t1 (x int);

Table created.

sec@ora10g> insert into t1 values (' 1o');
insert into t1 values (' 1o')
                       *
ERROR at line 1:
ORA-01722: invalid number

原因顯然：數字型別的x欄位不允許寫入包含字元的內容（注意' 1o'中最後的內容是小寫字母o）。

2）模擬查詢時報錯
sec@ora10g> create table t2 (x varchar2(10));

Table created.

sec@ora10g> insert into t2 values (' 1o');

1 row created.

sec@ora10g> select x,to_number(x) from t2;
select x,to_number(x) from t2
         *
ERROR at line 1:
ORA-01722: invalid number

此處問題原因也是顯然：存入的包含字元的內容透過to_number顯示轉換是被不允許的。

2）另外一種異常場景（較隱蔽）
在Toad或PL/SQL Developer中執行下面SQL語句，在初始列印的出的部分結果中沒有報錯，但在列印更多資料時就會報ORA-01722錯。
select * from t_bi_table t where t.bi_code = 083

下面內容是Toad在出錯時報的錯誤：
EurekaLog 6.0.15

Application:
-------------------------------------------------------
1.1 Start Date      : Thu, 22 Apr 2010 15:21:38 +0800
1.2 Name/Description: toad.exe - (Toad for Oracle)
1.3 Version Number : 9.7.2.5
1.4 Parameters      :
1.5 Compilation Date: Sat, 11 Oct 2008 00:13:22 +0800
1.6 Up Time         : 1 hour, 56 minutes, 26 seconds

Exception:
-----------------------------------------------------
2.1 Date          : Thu, 22 Apr 2010 17:18:04 +0800
2.2 Address       : 006802FA
2.3 Module Name   : toad.exe - (Toad for Oracle)
2.4 Module Version: 9.7.2.5
2.5 Type          : EOraError
2.6 Message       : ORA-01722:_invalid number
                      .
2.7 ID            : 22DD
2.8 Count         : 1
2.9 Status        : New
2.10 Note         :

Active Controls:
---------------------------------------------------------------------------------------------------------------------------------------
4.1 Form. Class   : TfrmMain
4.2 Form. Text    : Toad for Oracle - [SEC@10.17.183.209 - Editor (select * from t_bi_table t where t.bi_code = 083)]
4.3 Control Class: TEditorToadGrid
4.4 Control Text :

Computer:
-------------------------------------------------------------------------------
5.2 Total Memory : 3070 Mb
5.3 Free Memory   : 1210 Mb
5.4 Total Disk    : 64.71 Gb
5.5 Free Disk     : 11.54 Gb
5.6 System Up Time: 2 hours, 20 minutes, 47 seconds
5.7 Processor     : Intel(R) Core(TM)2 Duo CPU     T8300 @ 2.40GHz
5.8 Display Mode : 1280 x 800, 32 bit
5.9 Display DPI   : 96
5.10 Video Card   : NVIDIA Quadro NVS 135M (driver 7.15.11.7968 - RAM 128 MB)
5.11 Printer      : Canon iR C3080/3480/3580 UFR II (driver 2.20)

Operating System:
---------------------------------------
6.1 Type    : Microsoft Windows Vista
6.2 Build # : 7600
6.3 Update :
6.4 Language: Chinese (Simplified)
6.5 Charset : 134

此時問題的根本原因是bi_code欄位為varchar2型別的，但此處賦值的型別是數字，這裡發生了隱式轉換。

2.關於ORA-01722報錯資訊的解釋性描述
Error: ORA 1722
Text:   invalid number
-------------------------------------------------------------------------------
Cause: The attempted conversion of a character string to a number failed
        because the character string was not a valid numeric literal. Only
        numeric fields or character fields containing numeric data may be used
        in arithmetic functions or expressions. Only numeric fields may be
        added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that
        they contain only numbers, a sign, a decimal point, and the character
        "E" or "e" and retry the operation.

引自MOS：OERR: ORA 1722 "invalid number" [ID 19074.1]

3.小結
透過這個例子我們再一次感受到隱式轉換帶給我們的弊端（隱式轉換無法使用到索引），無論是在程式編碼還日常維護過程中，一定要多多關注隱式轉換帶來的麻煩。
在具體編寫SQL語句時一定要注意欄位型別，如果是varchar2字串型別需要以字串形式賦值，如果是number型別的欄位，注意直接使用數字進行檢索。

Good luck.

secooler
10.04.22

-- The End --

【問題處理】ORA-01722: invalid number

相關文章