<span>ORA-01722: invalid number</span>

—问题

select owner,index_name,DEGREE from DBA_INDEXES where DEGREE>1

*

ERROR at line 1:

ORA-01722: invalid number

–1 获取运行跟踪信息

SQL> set autotrace traceonly

SQL> select owner,index_name,DEGREE from DBA_INDEXES where DEGREE=1 and rownum<2;

Elapsed: 00:00:00.05

Execution Plan

———————————————————-

Plan hash value: 3920975716

——————————————————————————————————-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

——————————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | 120 | 18 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | NESTED LOOPS OUTER | | 1 | 120 | 18 (0)| 00:00:01 |

| 3 | NESTED LOOPS OUTER | | 1 | 117 | 17 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 106 | 16 (0)| 00:00:01 |

| 5 | NESTED LOOPS | | 1 | 102 | 15 (0)| 00:00:01 |

| 6 | NESTED LOOPS OUTER | | 1 | 85 | 14 (0)| 00:00:01 |

| 7 | NESTED LOOPS | | 1 | 81 | 13 (0)| 00:00:01 |

| 8 | NESTED LOOPS OUTER | | 1 | 73 | 12 (0)| 00:00:01 |

| 9 | NESTED LOOPS | | 1 | 65 | 11 (0)| 00:00:01 |

|* 10 | TABLE ACCESS FULL | IND$ | 25 | 750 | 10 (0)| 00:00:01 |

|* 11 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 35 | 1 (0)| 00:00:01 |

|* 12 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |

| 13 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |

|* 14 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |

| 15 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |

|* 16 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |

| 17 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |

|* 18 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |

| 19 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |

|* 20 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |

| 21 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |

|* 22 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |

| 23 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |

|* 24 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |

| 25 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |

|* 26 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |

——————————————————————————————————-

Predicate Information (identified by operation id):

—————————————————

1 – filter(ROWNUM<2)

10 – filter(TO_NUMBER(DECODE(“I”.”DEGREE”,32767,’DEFAULT’,TO_CHAR(NVL(“I”.”DEGREE”,1))))=1

AND BITAND(“I”.”FLAGS”,4096)=0)

11 – filter(BITAND(“O”.”FLAGS”,128)=0)

12 – access(“O”.”OBJ#”=”I”.”OBJ#”)

14 – access(“I”.”INDMETHOD#”=”ITO”.”OBJ#”(+))

16 – access(“I”.”BO#”=”IO”.”OBJ#”)

18 – access(“ITO”.”OWNER#”=”ITU”.”USER#”(+))

20 – access(“U”.”USER#”=”O”.”OWNER#”)

22 – access(“IO”.”OWNER#”=”IU”.”USER#”)

24 – access(“I”.”TS#”=”S”.”TS#”(+) AND “I”.”FILE#”=”S”.”FILE#”(+) AND

“I”.”BLOCK#”=”S”.”BLOCK#”(+))

26 – access(“I”.”TS#”=”TS”.”TS#”(+))

Statistics

———————————————————-

8 recursive calls

0 db block gets

23 consistent gets

0 physical reads

0 redo size

655 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

–2

select TO_NUMBER(DECODE(“I”.”DEGREE”,32767,’DEFAULT’,TO_CHAR(NVL(“I”.”DEGREE”,1)))) from sys.ind$ i –语句測试