测试环境：10.2.0.4
ORA-01722: invalid number 错误场景，不同字符类型关联导致报错：
模拟如下：
创建tmp_ys测试表：
10:29:10 SQL> create table tmp_ys(id number(20),account varchar2(20),name varchar2(20));
Table created.
Elapsed: 00:00:00.01
插入测试数据：
10:30:07 SQL> insert into tmp_ys(id,account,name) values(2,'12b','b');
1 row created.
Elapsed: 00:00:00.00
10:30:12 SQL> insert into tmp_ys(id,account,name) values(3,'12c','c');
1 row created.
Elapsed: 00:00:00.00
10:30:17 SQL> insert into tmp_ys(id,account,name) values(1,'12a','a');
1 row created.
Elapsed: 00:00:00.01
10:30:23 SQL> insert into tmp_ys(id,account,name) values(4,'12d','d');
1 row created.
Elapsed: 00:00:00.01
10:30:29 SQL> insert into tmp_ys(id,account,name) values(5,'12e','e');
1 row created.
Elapsed: 00:00:00.00
10:30:34 SQL> commit
10:30:35 2 ;
Commit complete.
Elapsed: 00:00:00.01
10:30:36 SQL> select * from tmp_ys
10:30:42 2 ;
ID ACCOUNT NAME
---------- -------------------- --------------------
2 12b b
3 12c c
1 12a a
4 12d d
5 12e e
Elapsed: 00:00:00.01
创建tmp_ys_o测试表：
10:30:43 SQL> create table tmp_ys_o(id varchar2(20),account varchar2(20),name varchar2(20));
Table created.
Elapsed: 00:00:00.01
插入数据：
10:31:00 SQL> insert into tmp_ys_o(id,account,name) values('1','12a','a');
1 row created.
Elapsed: 00:00:00.00
10:31:07 SQL> insert into tmp_ys_o(id,account,name) values('2','12b','b');
1 row created.
Elapsed: 00:00:00.00
10:31:38 SQL> insert into tmp_ys_o(id,account,name) values('3','12q','q');
1 row created.
Elapsed: 00:00:00.00
10:31:45 SQL> insert into tmp_ys_o(id,account,name) values('4','12r','r');
1 row created.
Elapsed: 00:00:00.00
10:31:51 SQL> insert into tmp_ys_o(id,account,name) values('5','12g','f');
1 row created.
Elapsed: 00:00:00.01
10:32:01 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
10:32:08 SQL> select * from tmp_ys_o;
ID ACCOUNT NAME
-------------------- -------------------- --------------------
1 12a a
2 12b b
3 12q q
4 12r r
5 12g f
Elapsed: 00:00:00.01
10:32:20 SQL> select * from tmp_ys s,tmp_ys_o o where s.id=o.id and s.account='12a';
ID ACCOUNT NAME ID
---------- -------------------- -------------------- --------------------
ACCOUNT NAME
-------------------- --------------------
1 12a a 1
12a a
Elapsed: 00:00:00.01
我们进行关联查询：
10:34:13 SQL> set autot traceonly
10:34:19 SQL> select * from tmp_ys s,tmp_ys_o o where s.id=o.id and s.account='12a';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1989059049
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 73 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TMP_YS | 1 | 37 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TMP_YS_O | 5 | 180 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."ID"=TO_NUMBER("O"."ID"))
2 - filter("S"."ACCOUNT"='12a')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
837 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这里需要注意下，我的tmp_ys表中的id为number类型，而tmp_ys_o表中的id 为varchar2类型，为什么这里能够正常查询呢？我们观察下表中的数据就明白了，两表的id数据都是数字值，所以在进行对id进行to_number(id)转换还是数字，所以能够正常得执行。下面我们模拟下 ORA-01722: invalid number：
在测试表tmp_ys_o总对id插入一条字符值：
10:59:19 SQL> insert into tmp_ys_o(id,account,name) values('o','12t','t');
1 row created.
Elapsed: 00:00:00.00
11:01:06 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
我们再次执行查询语句：
11:01:11 SQL> select * from tmp_ys s,tmp_ys_o o where s.id=o.id and s.account='12a';
ERROR:
ORA-01722: invalid number
no rows selected
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1989059049
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 73 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TMP_YS | 1 | 37 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TMP_YS_O | 6 | 216 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."ID"=TO_NUMBER("O"."ID"))
2 - filter("S"."ACCOUNT"='12a')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
838 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以发现出现ORA-01722: invalid number
错误。
我们在测试下对tmp_ys表中的id进行to_char（id）转换是否报错：
SQL> select * from tmp_ys s,tmp_ys_o o where to_char(s.id)=o.id and s.account='12a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1989059049
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 73 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TMP_YS | 1 | 37 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TMP_YS_O | 6 | 216 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."ID"=TO_CHAR("S"."ID"))
2 - filter("S"."ACCOUNT"='12a')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
837 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
小结：从上面的测试来看，如果对字符值数据进行to_number()转换，那么将会出现ora-01722错误，因为对数字值进行to_number转换还是数字，所以不会出现错误。还需要注意点就是隐式转换会带来无法走索引的情况，这也是隐式转换的一个弊端吧！需要特别注意！
