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:
