TO_CHAR(number)函数返回ORA-01722:无效的数字 [英] TO_CHAR(number) Function returns ORA-01722: invalid number

问题描述

查询:
Select To_Number(qty) From my_table Where Id=12345;
输出:
ORA-01722: invalid number
01722. 00000 - "invalid number"

Query:
Select To_Number(qty) From my_table Where Id=12345;
Output:
ORA-01722: invalid number
01722. 00000 - "invalid number"

查询:Select qty From my_table Where Id=12345;
输出:0.00080

Query: Select qty From my_table Where Id=12345;
Output: 0.00080

查询:
Select To_Number(0.00080) From Dual;
输出:
0.00080(无错误)

Query:
Select To_Number(0.00080) From Dual;
Output:
0.00080 (no error)

这是我在Oracle中面临的一种奇怪情况.有人可以建议为什么会发生吗?列qty是NUMBER类型.因此，很难想象它包含无效数字，但是它确实发生了.
我想澄清的是，尽管我们在同一列中有成千上万的记录，但该列中的特定值确实发生了这种情况.
增加了更多:如果使用TO_CHAR(qty)函数，会出现相同的错误. qty列是NUMBER类型，而不是VARCHAR2.实际上，我们正在使用显示错误的SUM(qty)函数.因此，我进行了解剖，发现这一行是罪魁祸首.

This is a odd situation I am facing in Oracle. Can anybody suggest why it happens? The column qty is NUMBER type. Hence it is very hard to imagine that it contains invalid number, but it happened.
I want to clarify that it happened for the specific value in the column although we have thousands of records in the same column.
Added more: The same error appears if I use TO_CHAR(qty) function. The qty column is NUMBER type not VARCHAR2. In fact we are using SUM(qty) function which showed error. Hence I went for a dissection and found this row being the culprit.

推荐答案

我假设在my_table中将qty定义为varchar2，否则，调用.如果这个假设是正确的，我敢打赌，表中还有其他行，其中qty中包含非数字数据.

I'm assuming that qty is defined as a varchar2 in my_table-- otherwise, there would be no purpose served by calling to_number. If that assumption is correct, I'll wager that there is some other row in the table where qty has non-numeric data in it.

SQL是一种基于集合的语言，因此Oracle(或任何其他数据库)可以完全自由地按其认为合适的顺序评估事物.这意味着Oracle在应用id=12345谓词之前可以完全自由地评估to_number(qty)表达式.如果Oracle碰巧遇到无法将qty值转换为数字的行，则会引发错误.

SQL is a set-based language so Oracle (or any other database) is perfectly free to evaluate things in whatever order it sees fit. That means that Oracle is perfectly free to evaluate the to_number(qty) expression before applying the id=12345 predicate. If Oracle happens to encounter a row where the qty value cannot be converted to a number, it will throw an error.

在id = 12345所在的特定行中也可能存在一些非数字数据，而这些数字恰好不在显示(例如，控制字符).您可以通过运行查询来检查

It is also possible that there is some non-numeric data in the particular row where id = 12345 that happens not to be displaying (control characters for example). You can check that by running the query

SELECT dump(qty, 1016) 
  FROM my_table
 WHERE id = 12345

(如果要使用十进制而不是十六进制，请使用1010作为dump的第二个参数)，并检查数据中是否有意外内容.

(if you want decimal rather than hexadecimal, use 1010 as the second parameter to dump) and checking to see whether there is anything unexpected in the data.

这篇关于TO_CHAR(number)函数返回ORA-01722:无效的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！