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

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: