具有"NULL"的数字溢出；列(0,3) [英] numeric overflow with "NULL" column (0,3)
问题描述
我有一个表(Oracle 12.1.0.2.0)，当我汇总所获得的值时在其中
I have a table (Oracle 12.1.0.2.0) where when I sum up the values I get
ORA-01426: numeric overflow
01426. 00000 - "numeric overflow"
*Cause: Evaluation of an value expression causes an overflow/underflow.
*Action: Reduce the operands.
当我在SQL-Developer中检查值时，我在"NULL"列中看到了，这对于函数求和而言应该没有问题.
When I check the values in SQL-Developer I see in the column "NULL" which should be no issue for the function sum.
当我对此表使用dump(col1)时，结果为
When I use dump(col1) for this table the result is
Typ=2 Len=2: 0,3
对于具有NULL的列，这不应该为NULL吗?
shouldn't this be NULL for columns with NULL?
还检查不为空"没有过滤行.
Also checks 'is not null' didn't filter rows.
导出数据仅导出第一行的一部分，并在此0,3第一次出现后停止.
Exporting the data only exported a part of the first row and stopped after the first occurence of this 0,3.
SQL Navigator显示错误"作为此列的值.
SQL Navigator showed "error" as value for this column.
表已创建为select.现在，它已被截断并重新填充，并且NULL行为NULL并进行汇总.
Table was created as select. Now it is truncated and refilled and NULL rows are NULL and summing up works.
该列的内容是什么?为什么SQL-Developer显示NULL而不是错误?
What was the content of that column? And why SQL-Developer shows NULL instead of an error?
推荐答案
The column value wasn't null, it was corrupt. The internal representation used for numbers is described in the documentation, or various other places like this.
第一个字节是指数，它可以-只是-为零，但后面不能只有3.我认为您可以获得的最接近的是-9.8 * x10 ^ 125
The first byte is the exponent, and it can - just - be zero, but not with only a 3 following it. The closest I think you can get is 0,3,102 for -9.8*x10^125
从文档中:
NUMBER数据类型存储零以及正和负的固定数字，其绝对值从1.0 x 10 ^ -130到但不包括1.0 x 10 ^ 126.
The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10^-130 to but not including 1.0 x 10^126.
因此，让我们看一下如何存储最末端的内容:
So lets look at how some at the extreme ends are stored:
with t (n) as (
select 1 * power(10, -130) from dual
union all select 1 * power(10, 125) from dual
union all select -1 * power(10, -130) from dual
union all select -1 * power(10, 125) from dual
union all select -9.7 * power(10, 125) from dual
union all select -9.8 * power(10, 125) from dual
union all select -9.85 * power(10, 125) from dual
union all select -9.9 * power(10, 125) from dual
)
select n, dump(n) d1, dump(n, 1016) d2 from t
N D1 D2
----------- ------------------------------ ------------------------------
1.000E-130 Typ=2 Len=2: 128,2 Typ=2 Len=2: 80,2
1.000E+125 Typ=2 Len=2: 255,11 Typ=2 Len=2: ff,b
-1.000E-130 Typ=2 Len=3: 127,100,102 Typ=2 Len=3: 7f,64,66
-1.000E+125 Typ=2 Len=3: 0,91,102 Typ=2 Len=3: 0,5b,66
-9.700E+125 Typ=2 Len=3: 0,4,102 Typ=2 Len=3: 0,4,66
-9.800E+125 Typ=2 Len=3: 0,3,102 Typ=2 Len=3: 0,3,66
-9.850E+125 Typ=2 Len=4: 0,3,51,102 Typ=2 Len=4: 0,3,33,66
-9.900E+125 Typ=2 Len=3: 0,2,102 Typ=2 Len=3: 0,2,66
如果您指定的算术表达式的绝对值大于或等于1.0 x 10126，则Oracle返回错误.
If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error.
select 1 * power(10, 126) from dual;
ORA-01426: numeric overflow
您转储的0,3值的末尾没有102表示负数，但是如果它为正数，则第一个字节将至少为128.
Your dumped value of 0,3 doesn't have the 102 at the end denoting a negative number, but if it was positive the first byte would be at least 128.
有一些实例被OCI程序误处理而损坏，甚至旧版导入也是如此.不知道数据最初是如何创建的，您可能永远不会确切知道出了什么问题，什么时候出现，或者最初应该是什么值.
There have been instances of numbers being corrupted by OCI programs mishandling them, and even legacy import doing the same. Without knowing how the data was originally created you'll probably never know exactly what went wrong, or when, or what the value was originally supposed to be.
奇怪的是，SQL Developer在结果网格中显示空值(如果您作为脚本查询，似乎中止了)；在SQL * Plus中，即使您set null为固定字符串，它也不会显示任何值. SQL Developer或JDBC驱动程序可能只是默默地吞噬了无法从内部表示形式转换的情况.
It's odd that SQL Developer shows null in the results grid (it seems abort if you query as a script); in SQL*Plus it shows no value even if you set null to a fixed string. SQL Developer, or the JDBC driver, may just be silently swallowing the inability to convert from internal representation.
这篇关于具有"NULL"的数字溢出；列(0,3)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
