`
qingfengxia
  • 浏览: 89335 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

<![CDATA[ORA-01722 invalid number]]>

阅读更多

ORA-01722

From Oracle FAQ

Jump to: navigation, search
<!---->

ORA-01722: invalid number

Contents

[hide]

What causes this error?

An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' thru '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.

There are numerous situations where this conversion may occur. A numeric column may be the object of an INSERT or an UPDATE statement. Or, a numeric column may appear as part of a WHERE clause. It is even possible for this error to appear when there are no numeric columns appearing explicitly in the statement!

Examples

Here are some examples:

SQL> select to_number('3434,3333.000') from dual;
ERROR:
ORA-01722: invalid number
no rows selected

The above statement throws the error message, because it has found a character, in this case, a comma and the default format for TO_NUMBER does not contain a comma.

The same error can occur when you use arithmetic functions on strings:

SQL> select 'abc' - 124 from dual;
ERROR:
ORA-01722: invalid number
no rows selected

The error can occur when you add dates with string values:

SQL> select '01-JUN-01' - 'abc' from dual;
ERROR:
ORA-01722: invalid number
no rows selected

How to fix it

The fix depends upon the exact expression which caused the problem. The following guide lists the possible SQL expressions which can give this error, with their most likely cause. When addressing this error, keep in mind that it can indicate a simple keystroke problem with the query, or a deeper problem with the query logic, or even the presence of bad data in the database itself.

When doing an INSERT INTO ... VALUES (...)

One of the data items you are trying to insert is an invalid number. Locate and correct it.

If all of the numbers appear to be valid, then you probably have your columns out of order, and an item in the VALUES clause is being inserted into a NUMBER column instead of the expected VARCHAR2 column. This can happen when a table has columns added or removed.

You are doing an INSERT or UPDATE, with a sub query supplying the values. Obviously, the preceding considerations apply here as well. What makes this more complicated is that the offending character string is hidden as a row in a table. The fix is to identify the row (or rows) which has the non-numeric string, and either change the data (if it is in error) or add something to the sub query to avoid selecting it. The problem is in identifying the exact row.

Assuming that the errant datum is an alphabetic character, one can use the following query:

        SELECT ...  WHERE UPPER(col) != LOWER(col)

where col is the column with the bad data.

When doing a SELECT, rather than an INSERT or UPDATE

In this case, there is probably an implicit conversion happening between some predicate in the WHERE clause. Check for a numeric column being compared to a character column.

If you are using the to_number function, make sure the format mask fits all possible character strings in the table.

If you know that a column contains both valid numbers and character strings, make sure that all rows which do not contain valid numbers are being excluded in the WHERE clause.

Other Rare Situations

To expand on the previous comment, if you have a column in a table which contains both valid numbers and character strings, it is just barely possible to get an ORA-01722 even if no character strings are being returned by your query. Example: two tables must be joined. In table A, the column is VARCHAR2, and in table B it is NUMBER. Table A also has non-numeric data in that column in some rows, and has a type column to make it obvious which rows are which. It is possible for the optimizer to choose an access plan in which the join is attempted before the filtering, which will cause the ORA-01772. The fix is to add a hint which changes the plan enough to bypass the rows causing the error.

Doing an explicit conversion can sometimes make things worse. For example, '+17', '-17', & ' 17' all convert successfully implicitly. The last one will raise the error if the 'S99' mask is used in the to_number function.

A field containing only spaces will raise this error. One fix is to replace the spaces with nulls or zeroes.

If you are querying a view rather than a table, any of the above could apply, and be hidden from sight. The fix is to add a predicate to the WHERE clause which excludes the troublesome rows.

评论

相关推荐

    微信小程序支付(已调试通过).rar

    微信小程序支付JSAPI交易类型 已通过测试 返回例子如下: &lt;xml&gt;&lt;return_code&gt;&lt;![CDATA[SUCCESS]]&gt;&lt;/return_code&gt; &lt;return_msg&gt;&lt;![CDATA[OK]]&gt;&lt;/return_msg&gt; &lt;result_code&gt;&lt;![CDATA[SUCCESS]]&gt;&lt;/result_code&gt;

    Mybatis 中的&amp;lt;![CDATA[ ]]&amp;gt;浅析

    [CDATA[ ]]&gt; 用于包含 Mybatis 的标签(如 `&lt;if&gt;`、`&lt;where&gt;`、`&lt;choose&gt;` 等),否则这些标签将不会被解析。 实例如下:&lt;select id="allUserInfo" parameterType="java.util.HashMap" resultMap="userInfo1"&gt; &lt;!...

    JAVA对象转换成XML(CDATA)

    [CDATA[ 文本内容 ]]&gt;`,其中"文本内容"部分不能包含字符串"]]&gt;",以防止提前结束CDATA段。同时,CDATA段不能嵌套,也就是说,一个CDATA段内部不能再有另一个CDATA段。 要将Java对象转换成XML并包含CDATA,我们可以...

    xml规范 中文版

    - 注释以`&lt;!--`开始,以`--&gt;`结束,用于添加非显示的信息,方便代码阅读。 **2.6 处理指令** - 处理指令以`&lt;?`开始,以`?&gt;`结束,主要用于指示XML处理器执行特定任务,例如调用XSLT转换。 **2.7 CDATA段** - ...

    sitemesh教程

    -- 在这里定义装饰器 --&gt; &lt;/decorators&gt; ``` ##### 4. 可选配置 - 创建可选配置文件`[web-app]/WEB-INF/sitemesh.xml`。 - 文件内容示例: ```xml &lt;sitemesh&gt; &lt;property name="decorators-file" value="/WEB-...

    微信开发文档

    [CDATA[toUser]]&gt;&lt;/ToUserName&gt; &lt;FromUserName&gt;&lt;![CDATA[fromUser]]&gt;&lt;/FromUserName&gt; &lt;CreateTime&gt;1348831860&lt;/CreateTime&gt; &lt;MsgType&gt;&lt;![CDATA[text]]&gt;&lt;/MsgType&gt; &lt;Content&gt;&lt;![CDATA[this is a test]]&gt;&lt;/...

    微信时间开发源码

    [CDATA[%s]]&gt;&lt;/ToUserName&gt; &lt;FromUserName&gt;&lt;![CDATA[%s]]&gt;&lt;/FromUserName&gt; &lt;CreateTime&gt;%s&lt;/CreateTime&gt; &lt;MsgType&gt;&lt;![CDATA[%s]]&gt;&lt;/MsgType&gt; &lt;Content&gt;&lt;![CDATA[%s]]&gt;&lt;/Content&gt; &lt;FuncFlag&gt;0&lt;/FuncFlag&gt; &lt;/xml...

    一个简单的xml文件

    ATTLIST 电话 类别 CDATA "移动电话"&gt; ]&gt; &lt;联系人列表&gt; &lt;说明&gt;&content;&lt;/说明&gt; &lt;联系人&gt; &lt;姓名&gt;刘保文&lt;/姓名&gt; &lt;ID&gt;201003101115&lt;/ID&gt; &lt;公司&gt;腾飞汇通有限公司&lt;/公司&gt; &lt;电话 类别="移动电话"&gt;18993380967&lt;/...

    省市区表,xml省市区

    自己将数据导入到xml中的,希望有需要的朋友可以自己下载,格式为&lt;!DOCTYPE PCAroot[ &lt;!ELEMENT PCAroot (Province*)&gt; &lt;!ELEMENT Province (City*)&gt; &lt;!ELEMENT City (Area*)&gt; &lt;!ELEMENT Area (#PCDATA)&gt; &lt;!...

    上传xml文档

    &lt;preconditions&gt;&lt;![CDATA[1.使用udp_bi用户在PLSQL正常登陆UAT数据库&lt;br&gt; (UAT数据库IP: 10.0.13.178)&lt;br&gt; 2.数据库脚本已下载至本地环境]]&gt;&lt;/preconditions&gt;&lt;importance&gt;&lt;![CDATA[2]]&gt;&lt;/importance&gt;&lt;steps&gt; &lt;step&gt;

    JS动态可控制左右滚动的图片

    [CDATA[//&gt;&lt;!-- var scrollPic_02 = new ScrollPic(); scrollPic_02.scrollContId = "ISL_Cont_1"; //内容容器ID scrollPic_02.arrLeftId = "LeftArr";//左箭头ID scrollPic_02.arrRightId = "RightArr"; //...

    xml文档类型定义资料

    -- 内部DTD定义 --&gt; ]&gt; &lt;document&gt; &lt;!-- 文档内容 --&gt; &lt;/document&gt; ``` - 外部DTD:引用外部的.DTD文件,分为私有和公开两种。引用私有DTD的例子: ```xml &lt;!DOCTYPE document SYSTEM "myDTD.dtd"&gt; &lt;document&gt; &lt;!-...

    集成springboot报表

    Map&lt;String, Object&gt; parameters = new HashMap&lt;&gt;(); parameters.put("REPORT_DATA_SOURCE", dataSource); // 数据源 JasperPrint jasperPrint = JasperFillManager.fillReport(compiledReportStream, parameters);...

    第二节 DTD快速入门-01

    &lt;body&gt;Don't forget me this weekend&lt;/body&gt; &lt;/note&gt; ``` 在上面的示例中,我们定义了一个名为 `note` 的根元素,该元素包含四个子元素:`to`、`from`、`heading` 和 `body`。每个子元素的声明都使用 `ELEMENT` ...

    XML 技术概述文档

    [CDATA[This is some arbitrary text &lt;within&gt; a CDATA!]]&gt; ``` 元素可以有属性,属性用来提供额外的信息。例如: ```xml &lt;article title="A Tutorial on XML 1.0"&gt; ``` 这里的 `title` 就是元素 `article` 的一个...

    FLEX动态树 动态图表

    -- 通过Bindable注释绑定变量 --&gt; &lt;fx:Script&gt; &lt;![CDATA[ [Bindable] [Embed("../png/internet.png")] // 自定义图标路径 private var myFolderClosedIcon:Class; [Bindable] [Embed("../png/phone.png")] ...

    xml实现留言簿xml+asp+xml+css

    [CDATA[留言内容]]&gt;&lt;/content&gt; &lt;/message&gt; &lt;/messages&gt; &lt;/gbook&gt; ``` 4. **DTD定义**: - 用于定义XML文档的结构和约束条件。 - 例如,`&lt;gbook&gt;` 元素包含了 `&lt;info&gt;` 和 `&lt;messages&gt;` 两个子元素。 ```xml...

    snoics-reptile 网页爬虫2.0

    [^&lt;&gt;]*["'\s][^&lt;&gt;]*&gt;]]&gt;&lt;/value&gt; &lt;/property&gt; &lt;property name="replaceRegexUrl"&gt; &lt;!-- 替换可能包含URL的字符串 --&gt; &lt;value&gt;&lt;![CDATA[&lt;[^!&lt;&gt;]*\shref\s*=\s*["']?reptile-replace-string["'\s][^&lt;&gt;]*&gt;]...

Global site tag (gtag.js) - Google Analytics