大数字段奇怪行为

Question

有现有的表名为temptable，列largenumber是一个数字字段，没有精确设置：

largenumber NUMBER;

查询：

select largenumber from temptable;

它返回：

-51524845525550100000000000000000000

但如果我做了

column largenumber format 999999999999999999999999999999999999999

然后

select largenumber from temptable;

它返回：

-51524845525550:100000000000000000000

为什么有个冒号？

为了进行测试，我取了数字，删除冒号，并将其插入到另一个表temptable2中，并执行相同的列largenumber格式，选择返回没有冒号的数字：

select largenumber from temptable2;

它返回：

-51524845525550100000000000000000000

所以冒号不在这里。

那么，在原始数字字段中有什么可能会导致冒号呢？

在原始行中，如果我执行select并尝试执行任何TO_CHAR、替换、强制转换或连接到文本，它将给出数字转换错误。

例如，试图生成一个csv：

select '"' || largenumber || '",'
FROM temptable;

将导致：

ORA-01722 (“无效数字”)错误发生在试图将字符串转换为数字时，而该字符串不能转换为有效数字

。

Answer 1

在一条评论中(作为对我的一个问题的回应)，您共享了关于违规值的dump(largenumber)返回

Typ=2 Len=8: 45,50,56,53,52,48,46,48

从一开始，这就意味着存储在磁盘上的数据是无效的(它不是number数据类型值的有效表示)。Typ=2是正确的，对于数据类型number是正确的。长度(8个字节)是正确的(我们都可以数到8才能看到这一点)。

错误的是字节本身。而且，我们只需要检查第一个和最后一个字节就可以看到这一点。

第一个字节是45。它编码你的数字的符号和指数。第一个位(1或0)表示符号:1表示正，0表示否定。45小于128，所以第一个字节中的第一个位是0，所以这个数字是负数。(到目前为止，这与您所知道的有关预期值的内容相匹配。)

但是，对于负数，最后一个字节是总是魔术值102。总是。在你最初问题下的另一条评论中，Connor McDonald询问你的平台--但这是与平台无关的--这是甲骨文如何编码在任何平台上永久存储的数字。因此，我们已经知道您得到的dump值告诉我们该值无效。

实际上，Connor在同一条评论中给出了该数字的正确表示(根据Oracle的内部数字表示方案)。实际上，最后一个字节是错误的:您的dump显示了48个字节，但应该是102个。

你怎么能解决这个问题？如果是一次性的，只需使用update语句将值替换为正确的值，然后继续前进。如果您的表有主键，那么我们将其命名为id，然后为该行查找id，然后

update {your_table} set largenumber = -50...... where id = {that_id};

问题是，您的表中可能有多少这样的腐败值？如果它只是一个，你可以耸耸肩；但如果它是很多(甚至是“一把”)，你可能想知道他们是如何在第一时间到达那里的。

在大多数情况下，数据库将拒绝无效的值；例如，不能简单地将'abc'插入number列。但是，有一些方法可以让以可重复的方式获取坏数据；甚至是故意的。因此，您必须研究如何插入坏值(用于插入的进程)。

对于以可重复的方式在number列中插入坏数据的简单方法，您可以在Oracle论坛上看到这个线程：https://community.oracle.com/tech/developers/discussion/3903746/detecting-invalid-values-in-the-db

请注意，我当时才刚开始学习Oracle (不到两个月)，所以我可能在这个线程中说了一些愚蠢的话；但是插入坏数据的方法在那里有详细的描述，并进行了测试。这只说明了一种可能(而且是可信的！)在表中插入无效内容的方法；在特定情况下是如何发生的，您必须自己调查。