ORA-01722: invalid number

create table test_num(a varchar2(10),b varchar2(10));
insert into test_num values('a','b');
select * from test_num where a=1;

ORA-01722: invalid number
select * from test_num where a='1';
no rows selected

同时需要注意，"invalid number"的产生还依赖于执行计划，如下：

SQL> create table tbl(x varchar2(1),y varchar2(1));

 

Table created.

 

SQL> insert into tbl values('A','X');

 

1 row created.

 

SQL> insert into tbl values('B','1');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select /*+ ordered_predicates */ * from tbl where y = 1 and x = 'B';
select /*+ ordered_predicates */ * from tbl where y = 1 and x = 'B'
                                                  *
ERROR at line 1:
ORA-01722: invalid number

 

SQL> select /*+ ordered_predicates */ * from tbl where x = 'B' and y = 1;

 

X                                                            Y
------------------------------------------------------------ -
B                                                            1

还是一个教训，做你该做的，不要依赖于Oracle会那么聪明。