ORA-01722: invalid number 一例
来源:互联网 发布:mac扒带软件 编辑:程序博客网 时间:2024/06/04 21:34
今天有同事询问ORA-01722: invalid number的错误,简单做如下测试:
RYAN@RACTSRT_1> create table tst1(id varchar2(10)); ---id字段时varchar2类型
Table created.
RYAN@RACTSRT_1> insert into tst1 values(18); ---转换为字符‘18’
1 row created.
RYAN@RACTSRT_1> select * from tst1;
ID
----------
18
RYAN@RACTSRT_1> select * from tst1 where id=18; ---where条件用的是number类型查询
ID
----------
18
RYAN@RACTSRT_1> insert into tst1 values('moon'); ---插入字符'moon'
1 row created.
RYAN@RACTSRT_1> select * from tst1 where id=18; --这里再次用number类型去查询会报错
ERROR:
ORA-01722: invalid number
no rows selected
RYAN@RACTSRT_1> select * from tst1 where id='18'; ---用字符类型去查询不会报错
ID
----------
18
RYAN@RACTSRT_1> create index ind_id_tst1 on tst1(id); ---建立一个索引
Index created.
RYAN@RACTSRT_1> set autotrace on;
ERROR:
ORA-01722: invalid number
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2178032767
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 7 3 (0) 00:00:01
* 1 TABLE ACCESS FULL TST1 1 7 3 (0) 00:00:01 --全表扫描
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=18) -------隐式转换id列下的所有值去匹配数字类型18,因为有一个‘moon’值无法转换为数字类型所以报错
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
521 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RYAN@RACTSRT_1> select * from tst1 where id='18';
ID
----------
18
Execution Plan
----------------------------------------------------------
Plan hash value: 3055109003
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 4 1 (0) 00:00:01
* 1 INDEX RANGE SCAN IND_ID_TST1 1 4 1 (0) 00:00:01 ---注意这里走索引扫
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"='18') ---不再有隐式转换
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 0
- ORA-01722: invalid number 一例
- ORA-01722 Invalid Number
- ORA-01722 Invalid Number
- ORA-01722: invalid number
- ORA-01722 invalid number
- ora-01722:invalid number
- ORA-01722: invalid number
- ORA-01722:invalid number
- ORA-01722: invalid number
- Oracle-ORA-01722 invalid number错误
- ORA-01722: invalid number" During Upgrade/Downgrade
- Oracle-ORA-01722 invalid number 错误
- ORA-01722 invalid number错误原理解
- Oracle-ORA-01722 invalid number错误
- Oracle-ORA-01722 invalid number错误
- ORA-01722 invalid number错误
- java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
- ORA-01465: invalid hex number
- OCP-1Z0-051-2015-42题
- shell中的grep,awk,sed,cut,sort
- 面向对象六大原则
- Android系统中数据库应用
- vim使用命令大集合
- ORA-01722: invalid number 一例
- 十分钟搭建Windows版Python开发环境(Eclipse+PyDev)
- java异常——异常分类+声明已检查异常+如何抛出异常+自定义异常类
- but no declaration can be found for element 'aop:aspectj-autoproxy'.
- ASP.NET Web API 应用教程(一) ——数据流使用
- 正向遍历删除元素STL
- 深入理解Java:注解(Annotation)--注解处理器
- struts2中Action参数传递之DTO
- mac osx下使用wireshark,报错There are no interfaces on which a capture can be done,osxwireshark