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屋!