oracle TO_CHAR(number) 函数返回 ORA-01722: 无效数字
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/19273610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TO_CHAR(number) Function returns ORA-01722: invalid number
提问by ramesh
Query:Select To_Number(qty) From my_table Where Id=12345;
Output:ORA-01722: invalid number
01722. 00000 - "invalid number"
查询:Select To_Number(qty) From my_table Where Id=12345;
输出:ORA-01722: invalid number
01722. 00000 - "invalid number"
Query: Select qty From my_table Where Id=12345;
Output: 0.00080
查询:Select qty From my_table Where Id=12345;
输出:0.00080
Query:Select To_Number(0.00080) From Dual;
Output:0.00080
(no error)
查询:Select To_Number(0.00080) From Dual;
输出:(0.00080
无错误)
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.
这是我在 Oracle 中面临的一个奇怪的情况。任何人都可以建议为什么会发生?列qty
是NUMBER
类型。因此很难想象它包含无效数字,但它发生了。
我想澄清一下,尽管我们在同一列中有数千条记录,但它发生在列中的特定值上。
添加更多:如果我使用TO_CHAR(qty)
函数会出现同样的错误。该qty
列的NUMBER
类型不是VARCHAR2
。事实上,我们正在使用SUM(qty)
显示错误的函数。因此,我进行了解剖,发现这一行是罪魁祸首。
回答by Justin Cave
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.
我假设qty
被定义为varchar2
中my_table
-否则,就没有目的,通过调用服务to_number
。如果这个假设是正确的,我会打赌表中还有一些其他行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.
SQL 是一种基于集合的语言,因此 Oracle(或任何其他数据库)可以完全自由地以它认为合适的任何顺序评估事物。这意味着 Oracleto_number(qty)
在应用id=12345
谓词之前完全可以自由地评估表达式。如果 Oracle 碰巧遇到qty
无法将值转换为数字的行,则会抛出错误。
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
也有可能在特定行中存在一些非数字数据,而这些数据id = 12345
恰好没有显示(例如控制字符)。您可以通过运行查询来检查
SELECT dump(qty, 1016)
FROM my_table
WHERE id = 12345
(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.
(如果您想要十进制而不是十六进制,请使用 1010 作为 的第二个参数dump
)并检查数据中是否有任何意外。
回答by Alex Poole
The only way I can see you could get the results you've shown, given that qty
really is a number
field, is if it holds corrupt data (which is why there has been scepticism about that assumption). I'm also assuming your client is formatting the value with a leading zero, but is not forcing the trailing zero, which wouldn't normally appear; you can of course force it with to_char(.0008, '0.00000')
, but you don't appear to be doing that; still, the leading zero makes me wonder.
鉴于这qty
确实是一个number
领域,我认为您可以获得您所展示的结果的唯一方法是它是否包含损坏的数据(这就是为什么对该假设持怀疑态度的原因)。我还假设您的客户正在使用前导零格式化该值,但没有强制使用尾随零,这通常不会出现;你当然可以用 强制它to_char(.0008, '0.00000')
,但你似乎没有这样做;仍然,前导零让我想知道。
Anyway, to demonstrate corruption you can force an invalid value into the field via PL/SQL - don't try this with real data or a table you care about:
无论如何,为了证明损坏,您可以通过 PL/SQL 将无效值强加到字段中 - 不要尝试使用真实数据或您关心的表:
create table t42(qty number);
table T42 created.
declare
n number;
begin
dbms_stats.convert_raw_value('bf0901', n);
insert into t42 (qty) values (n);
end;
/
anonymous block completed
select qty from t42;
QTY
----------
.00080
select to_number(qty) from t42;
Error starting at line : 12 in command -
select to_number(qty) from t42
Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
Note the plain query shows the number as expected - though with a trailing zero, and no leading zero - and running it through to_number()
throws ORA-01722. Apart from the leading zero, that is what you've shown.
请注意,普通查询按预期显示数字 - 尽管带有尾随零,并且没有前导零 - 并且运行它to_number()
会抛出 ORA-01722。除了前导零之外,这就是您所展示的。
It also fails with to_char()
, as in your question title:
它也失败了to_char()
,如您的问题标题所示:
select to_char(qty) from t42;
Error starting at line : 13 in command -
select to_char(qty) from t42
Error report -
SQL Error: ORA-01722: invalid number
... which makes sense; your to_number()
is doing an implicit conversion, so it's really to_number(to_char(qty))
, and it's the implicit to_char()
that actually generates the error, I think.
...这是有道理的;你to_number()
正在做一个隐式转换,所以它真的to_number(to_char(qty))
,而且它是隐式to_char()
实际上产生了错误,我认为。
Your comments suggest you have a process that is loading and removing data. It would be interesting to see exactly what that is doing, and if it could be introducing corruption. This sort of effect can be achieved through OCI as the database will trust that the data it's passed is valid, as it does in the PL/SQL example above. There are bug reports suggesting imp
can also cause corruption. So the details of your load process might be important, as might the exact database version and platform.
您的评论表明您有一个正在加载和删除数据的过程。看看它到底在做什么,以及它是否会引入腐败,将会很有趣。这种效果可以通过 OCI 实现,因为数据库会相信它传递的数据是有效的,就像在上面的 PL/SQL 示例中一样。有错误报告表明imp
也可能导致损坏。因此,加载过程的详细信息可能很重要,确切的数据库版本和平台也很重要。
回答by Henry
I encountered the nearly same problem. And I found the mysterious number behaved differently from the normal number after dump()
. For example, assuming my qty=500 (datatype: number(30,2)) , then:
我遇到了几乎相同的问题。我发现神秘数字在dump()
. 例如,假设我的 qty=500 (datatype: number(30,2)) ,那么:
select dump(qty) from my_table where Id=12345;
Typ=2 Len=3: 194,6,1
select dump(500.00) from dual;
Typ=2 Len=2: 194,6
If we know how number datatype be stored (if not, plz visit http://translate.google.com/translate?langpair=zh-CN%7Cen&hl=zh-CN&ie=UTF8&u=http%3A//www.eygle.com/archives/2005/12/how_oracle_stor.html) , we can find that there is a tailing zero (the last extra "1" in Typ=2 Len=3: 194,6,1
) in the mysterious number.
如果我们知道如何存储数字数据类型(如果不知道,请访问http://translate.google.com/translate?langpair=zh-CN%7Cen&hl=zh-CN&ie=UTF8&u=http%3A//www.eygle.com /archives/2005/12/how_oracle_stor.html) ,我们可以发现Typ=2 Len=3: 194,6,1
神秘数字中有一个尾随零(最后一个额外的“1” )。
So I made a trick to eliminate the tailing zero, and it works for the problem.
所以我做了一个消除拖尾零的技巧,它对问题有效。
select dump(trunc(qty+0.001,2)) from my_table where Id=12345;
Typ=2 Len=2: 194,6
Hope someone to explain the deep mechanism.
希望有人解释深层机制。
回答by BWS
try this:
尝试这个:
Select To_Number(trim(qty)) From my_table Where Id=12345;