执行同样的语句在第一个数据库 ( 11.2.0.1.0 )报错ORA-01722: invalid number,第二个数据库执行成功(11.2.0.2.0)
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> SELECT GW.GW_DM, GW.GW_MCFROM ADP.QX_GW GW, ADP.QX_USER_GW U
2 WHERE GW.GW_DM = U.GW_DM AND gw.gw_mc LIKE '%管理%'AND U.USERID = 1233960112;
ERROR:
ORA-01722: invalid number
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3896149506
-------------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |68 |12 (0)| 00:00:01 |
| 1 |NESTED LOOPS
|
| | |
|
|
| 2 | NESTED LOOPS
|
| 1 |68 |12 (0)| 00:00:01 |
|*3 |TABLE ACCESS FULL | QX_USER_GW | 2 |56 |10 (0)| 00:00:01 |
|*4 |INDEX UNIQUE SCAN | PK_QX_GW | 1 | | 0 (0)| 00:00:01 |
|*5 | TABLE ACCESS BY INDEX ROWID| QX_GW| 1 |40 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("U"."USERID")=24403960112)
4 - access("GW"."GW_DM"="U"."GW_DM")
5 - filter("GW"."GW_MC" LIKE '%管理%')
Statistics
----------------------------------------------------------
4recursive calls
6db block gets
30consistent gets
0physical reads
0redo size
627bytes sent via SQL*Net to client
520bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
SQL>
===================================================
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT GW.GW_DM, GW.GW_MCFROM ADP.QX_GW GW, ADP.QX_USER_GW U
2 WHERE GW.GW_DM = U.GW_DM AND gw.gw_mc LIKE '%科管理岗'AND U.USERID = 1233960112;
GW_DM
GW_MC
---------------- --------------------------------------------------------------------------------
12341241230002管理九科管理岗
Execution Plan
----------------------------------------------------------
Plan hash value: 1446211536
-------------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |70 |24 (0)| 00:00:01 |
| 1 |NESTED LOOPS
|
| | |
|
|
| 2 | NESTED LOOPS
|
| 1 |70 |24 (0)| 00:00:01 |
|*3 |TABLE ACCESS FULL | QX_USER_GW | 3 |84 |21 (0)| 00:00:01 |
|*4 |INDEX UNIQUE SCAN | PK_QX_GW | 1 | | 0 (0)| 00:00:01 |
|*5 | TABLE ACCESS BY INDEX ROWID| QX_GW| 1 |42 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("U"."USERID")=24403960112)
4 - access("GW"."GW_DM"="U"."GW_DM")
5 - filter("GW"."GW_MC" LIKE '%科管理岗')
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
76consistent gets
65physical reads
0redo size
626bytes sent via SQL*Net to client
520bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
SQL>
|