【问题处理】ORA-01722: invalid number

本文将展示三个导致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 --

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

转载于:http://blog.itpub.net/519536/viewspace-660522/