问TO_CHAR( number )函数返回ORA-01722:无效的数字

EN

我假设qty在my_table中被定义为一个varchar2 --否则，调用to_number就没有意义了。如果假设正确，我敢打赌表中还有其他行，其中qty包含非数值型数据。

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

也有可能在id = 12345不显示的特定行中有一些非数字数据(例如，控制字符)。您可以通过运行查询来进行检查

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

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

考虑到qty确实是一个number字段，我能看到您获得所显示的结果的唯一方法是它是否包含损坏的数据(这就是为什么人们对该假设持怀疑态度)。我还假设您的客户机用前导零设置了值的格式，但没有强制尾随零，这通常不会出现；当然，您可以使用to_char(.0008, '0.00000')强制它，但您似乎没有这样做；尽管如此，前导零让我感到疑惑。

无论如何，为了证明损坏，您可以通过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"

注意:普通查询按照预期显示了数字-尽管有一个尾随零，没有前导零-通过to_number()运行它会抛出ORA-01722。除了前导零之外，这就是您所展示的。

对于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

..。这是有道理的；您的to_number()正在进行隐式转换，所以它实际上是to_number(to_char(qty))，而实际上是隐式to_char()生成了错误，我认为。

您的评论表明您有一个加载和删除数据的过程。看看它到底在做什么，以及它是否会带来腐败，这将是一件有趣的事情。这种效果可以通过OCI实现，因为数据库将信任它传递的数据是有效的，正如上面的PL/SQL示例所做的那样。有错误报告表明imp也会导致损坏。因此，加载过程的详细信息可能很重要，确切的数据库版本和平台也是如此。

我遇到了几乎相同的问题。我发现在dump()之后，神秘数字的表现与正常数字不同。例如，假设我的qty=500 (数据类型: 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

如果我们知道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“)。

因此，我做了一个技巧来消除尾部零，它对这个问题有效。

select dump(trunc(qty+0.001,2)) from my_table where Id=12345;

Typ=2 Len=2: 194,6

希望有人能解释一下其中的深层机制。