ITPub博客

首页 > 数据库 > Oracle > ORACLE 表char字段混合存储数字和字母类似数据时按数字的where条件查询报错ORA-01722

ORACLE 表char字段混合存储数字和字母类似数据时按数字的where条件查询报错ORA-01722

原创 Oracle 作者:清风艾艾 时间:2019-01-28 09:32:11 0 删除 编辑

    ORACLE 数据库,创建有char字段列的数据表:create table tbl_c (id char(1));

对该表插入字符'1'、数字2,执行查询:

select * from  tbl_c;SQL语句能够正常执行,

select * from  tbl_c where id=1;SQL语句能够 正常 执行;

表中插入字符'A’,

select * from  tbl_c;SQL语句能够 正常 执行

select * from  tbl_c where id=1;SQL语句查询时报错 ORA-01722: invalid number 

删除插入的字符数据'A'后,查询恢复正常;

    实验如下:

1、创建实验表

SQL> create table tbl_c (id char(1));

Table created.


2、插入实验 正常 数据

SQL> insert into tbl_c values('1');

1 row created.

SQL> insert into tbl_c values(2);

1 row created.

SQL> commit;

Commit complete.


3、测试查询

SQL> select * from tbl_c;

I

-

1

2

SQL>  select * from  tbl_c where id=1;

I

-

1

SQL> 


4、插入影响CBO条件查询解析执行的数据

SQL> insert into tbl_c values('A');

1 row created.

SQL> commit;

Commit complete.

SQL> 


5、测试查询,where条件查询数字查询出现异常

SQL> select * from  tbl_c where id='A';

I

-

A

SQL> select * from  tbl_c where id='2';

I

-

2

SQL> 

SQL> select * from  tbl_c where id=1;

ERROR:

ORA-01722: invalid number

no rows selected

    关于这种现状,ORACLE MOS文档 Doc ID 1059215.1 有相关说明:

CAUSE

The problem is due to the way Oracle handles datatype conversions.
The statement being executed is made to compare the VARCHAR database field with a numeric literal value.
In this case, the database does an implicit conversion applying the TO_NUMBER function to the first column, in order to be able to perform the comparison between comparable values.
If this column contains non-numeric values and one of them is retrieved and the condition is evaluated, the ORA-1722 error is issued.

SOLUTION

The proposed solution is to avoid the implicit conversion. This may be achieved in two ways:
A) Modify the data to assure no invalid numeric values are stored in the column, either updating invalid column values or modifying the column datatype to enforce the presence of only numeric values. This implies the application remains unchanged.
This solution implies a modification in the database model, or to add restrictions not necessarily applicable to the table column involved.

OR

B) Modify the application to ensure the comparison is done between two VARCHAR values. This may be easily achieved surrounding the variable value with quotes:

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

请登录后发表评论 登录
全部评论
个人喜欢IT行业,目前从事数据库工作,包括Oracle、mysql、mongodb、sqlserver等数据库的维护,喜欢专研开发技术,尤其对java程序的开发感兴趣。工作经历上,在中国联通系统集成公司、中公网医疗信息技术有限公司做过数据库技术支持;目前在海量数据,负责华东区oracle、mysql、mongodb的维护工作。

注册时间:2015-01-30

  • 博文量
    222
  • 访问量
    391307