Oracle 2连字符号列中? [英] Oracle 2 hyphens in number column?
问题描述
我有一个Oracle表(版本8i)，我需要将该表迁移到sql server，其中一列是NUMBER数据类型.在其中，它具有如下所示的值:
--1331013400000000000000000000
--1331017903617177360300000000
--1331012863048235233700000000
还有其他
0.9574875526618150
2.51572327044025
-看起来像存储在数字列中的实际值.我不知道破折号是什么意思或它们的含义，但是，当我尝试通过ssma迁移数据时，我得到无法将字符串值放入浮点数"，因此sql server很明显在这种情况下遇到了麻烦我知道.
我确定可以找到一种方法来处理这些奇怪的数据，我只是想知道这意味着什么，它的用途，我用Google搜索了一下，但出人意料地空手而归.有人有任何线索吗?
转储1016:
0.2722718362012630 Typ=2 Len=9: c0,1c,17,48,54,3f,2,1b,1f
--1331013400000000000000000000 Typ=2 Len=4: 32,ea,0,43
0.50761421319797 Typ=2 Len=8: c0,33,4d,f,16,20,62,62
1 Typ=2 Len=2: c1,2
0.9574875526618150 Typ=2 Len=9: c0,60,4b,58,38,1b,3e,52,33
1.11894371713103 Typ=2 Len=9: c1,2,c,5a,2c,48,48,20,4
2.51572327044025 Typ=2 Len=9: c1,3,34,3a,18,1c,5,29,1a
0.0537258905066351 Typ=2 Len=9: c0,6,26,1a,5a,6,7,40,34
0.1851303317535540 Typ=2 Len=9: c0,13,34,1f,22,12,36,38,29
0.0000000000000000000000000000306386 Typ=2 Len=4: b2,1f,40,57
1.6164 Typ=2 Len=4: c1,2,3e,41
0.1289839930864580 Typ=2 Len=9: c0,d,5a,54,64,1f,57,2e,51
0.004721435316336170 Typ=2 Len=9: bf,30,16,2c,36,11,22,3e,47
--1331017903617177360300000000 Typ=2 Len=10: 32,ea,0,16,62,28,1e,18,41,62
--1331012863048235233700000000 Typ=2 Len=10: 32,ea,0,49,26,61,13,42,4e,40
--1331010715609612880500000000 Typ=2 Len=10: 32,ea,0,5e,56,29,5,59,d,60
0.0778391842453491 Typ=2 Len=9: c0,8,4f,28,13,2b,2e,23,5c
--1331010187793684447000000000 Typ=2 Len=10: 32,ea,0,64,e,16,41,11,39,1f
0.8296 Typ=2 Len=3: c0,53,61
--1331015225486314961400000000 Typ=2 Len=10: 32,ea,0,31,4c,35,26,57,5,57
--1331016035469906437500000000 Typ=2 Len=10: 32,ea,0,29,42,37,2,5f,3a,1a
0.3301637612255680 Typ=2 Len=9: c0,22,2,40,4d,d,1a,39,51
0.2666453350398630 Typ=2 Len=9: c0,1b,43,2e,22,33,28,57,1f
0.1581527755812110 Typ=2 Len=9: c0,10,52,35,4e,38,52,16,b
0.8089305937550560 Typ=2 Len=9: c0,51,5a,1f,3c,26,38,6,3d
--1331015006297067350000000000 Typ=2 Len=9: 32,ea,0,33,5f,48,1f,22,42
0.3745318352059930 Typ=2 Len=9: c0,26,2e,20,54,35,6,64,1f
--1331017625157985988000000000 Typ=2 Len=10: 32,ea,0,19,4c,56,16,10,3,15
更新:
这是统计计算器库中的错误.在某些情况下，在计算sdev时使用sqrt时，返回Doubles的函数会生成NaN值.在准备好的语句构造中使用的Oracle驱动程序(oracle14.zip)不会验证数据，但会发送和写入原始字节，最终导致损坏.有趣的是，当我尝试在prep语句中设置NaN值时，MS SQL Server驱动程序没有让我做一个准备好的语句并抛出异常.将其发布为"fyi" ...
这并不意味着什么，也不是为了什么.恐怕您的数据已损坏. --是表中的实际值，但不是数字.如果您可以访问Oracle内部数字表示法，请参见注释1031902.6，或若无此解释，请参见注释.如果它确实是一个负数，那么最后一个十六进制字节应为66.转储它似乎是的数字-用一个减号，而不是两个，这没有意义-给出:
select dump(-1331013400000000000000000000, 1016) from dual;
DUMP(-1331013400000000000000000000,1016)
----------------------------------------
Typ=2 Len=6: 31,58,46,64,43,66
在Oracle中创建无效数字不是一件容易的事(我想您不会期望如此)，但这是我以前使用的一种方法.除了双减号和它们都具有相同的长度以外，线索之一是将转储的值转换回数字不会得到相同的结果:
create table t42(value number);
declare
n number;
begin
dbms_stats.convert_raw_value('32ea004366', n);
insert into t42 (value) values (n);
end;
/
select value from t42;
VALUE
--------------------------------------
-<3:13400000000000000000000
这是来自Oracle 9i，我现在关闭到8i数据库，因此结果可能会有所不同.
不能做to_number(value)当然也是一个大提示；这样做时会有一个隐式的to_char()，因此它试图将文本表示形式转换为数字，从而解释了该错误.有趣的是，to_char()值也不与简单选择的结果匹配.如果对数据执行相同的操作，则会看到相同的错误.
select to_number(value) from t42;
select to_number(value) from t42
*
ERROR at line 1:
ORA-01722: invalid number
select to_char(value) from t42;
TO_CHAR(VALUE)
----------------------------------------
-`003400000000000000000000
除非您知道不良数据来自何处并保持原始状态，否则您可能无法挽回这些值.我认为您最好的办法就是忽略它，或者将其替换为将要迁移的东西-如果该字段为空，那么null是安全的选择，否则我想您必须选择一个神奇的值.
识别和修改受影响的行可以通过一个函数来完成；可能像这样:
create or replace function null_bad_number(value number)
return number deterministic as
tmp_value number;
invalid_number exception;
pragma exception_init(invalid_number, -1722);
begin
select to_number(value) into tmp_value from dual;
return value;
exception
when invalid_number then
return null;
end;
/
具有先前创建的相同无效值和一个有效值:
insert into t42 (value) values (0.9574875526618150);
select * from t42;
VALUE
----------
-`.003E+24
.957487553
update t42 set value = null
where value is not null
and null_bad_number(value) is null;
1 row updated.
select * from t42;
VALUE
----------
.957487553
无论如何都不是理想的选择，但是在这一点上，我认为您只是在尽力而为.您可以删除行而不是对其进行更新，或者将值设置为其他值，这取决于您要如何进行.
您可以尝试让Oracle参与进来，看看他们是否可以弄清楚发生了什么，看看他们是否有任何技巧可以恢复到原始值-这似乎不太可能-但我不确定您会得到很多收益如此旧版本的数据库的支持.
当然，在不知道如何以及何时引入损坏的情况下(也许是通过不可靠的导入，或者通过有缺陷的OCI程序)，您必须质疑该列和其他位置中所有其他数据的有效性.在这种情况下，损坏看起来非常统一-所有无效值似乎都是以相同的方式构造的-所以您可能还可以.通常，将不正确的字节放入内部值的操作可能会误判一个错误但仍然有效的值.它可能看起来正确，或者可能比原始预期值高出几个数量级，而且实际上没有办法告诉您.
I have an Oracle table (version 8i) which I need to migrate to sql server, one of the columns is a NUMBER datatype. In it, it has values like so:
--1331013400000000000000000000
--1331017903617177360300000000
--1331012863048235233700000000
among others
0.9574875526618150
2.51572327044025
The -- look like actual values stored in number column. I don't understand what the dash dash are for or what they mean, however, when I try to migrate data via ssma, i get "can't put string value into a float" so clearly sql server stumbles over this case, as I do.
I'm sure I can figure out a way to deal with this weird data, I'm just wondering what heck this means and what it's for, I Googled but surprisingly came out empty handed. Anyone have any clue?
dump 1016:
0.2722718362012630 Typ=2 Len=9: c0,1c,17,48,54,3f,2,1b,1f
--1331013400000000000000000000 Typ=2 Len=4: 32,ea,0,43
0.50761421319797 Typ=2 Len=8: c0,33,4d,f,16,20,62,62
1 Typ=2 Len=2: c1,2
0.9574875526618150 Typ=2 Len=9: c0,60,4b,58,38,1b,3e,52,33
1.11894371713103 Typ=2 Len=9: c1,2,c,5a,2c,48,48,20,4
2.51572327044025 Typ=2 Len=9: c1,3,34,3a,18,1c,5,29,1a
0.0537258905066351 Typ=2 Len=9: c0,6,26,1a,5a,6,7,40,34
0.1851303317535540 Typ=2 Len=9: c0,13,34,1f,22,12,36,38,29
0.0000000000000000000000000000306386 Typ=2 Len=4: b2,1f,40,57
1.6164 Typ=2 Len=4: c1,2,3e,41
0.1289839930864580 Typ=2 Len=9: c0,d,5a,54,64,1f,57,2e,51
0.004721435316336170 Typ=2 Len=9: bf,30,16,2c,36,11,22,3e,47
--1331017903617177360300000000 Typ=2 Len=10: 32,ea,0,16,62,28,1e,18,41,62
--1331012863048235233700000000 Typ=2 Len=10: 32,ea,0,49,26,61,13,42,4e,40
--1331010715609612880500000000 Typ=2 Len=10: 32,ea,0,5e,56,29,5,59,d,60
0.0778391842453491 Typ=2 Len=9: c0,8,4f,28,13,2b,2e,23,5c
--1331010187793684447000000000 Typ=2 Len=10: 32,ea,0,64,e,16,41,11,39,1f
0.8296 Typ=2 Len=3: c0,53,61
--1331015225486314961400000000 Typ=2 Len=10: 32,ea,0,31,4c,35,26,57,5,57
--1331016035469906437500000000 Typ=2 Len=10: 32,ea,0,29,42,37,2,5f,3a,1a
0.3301637612255680 Typ=2 Len=9: c0,22,2,40,4d,d,1a,39,51
0.2666453350398630 Typ=2 Len=9: c0,1b,43,2e,22,33,28,57,1f
0.1581527755812110 Typ=2 Len=9: c0,10,52,35,4e,38,52,16,b
0.8089305937550560 Typ=2 Len=9: c0,51,5a,1f,3c,26,38,6,3d
--1331015006297067350000000000 Typ=2 Len=9: 32,ea,0,33,5f,48,1f,22,42
0.3745318352059930 Typ=2 Len=9: c0,26,2e,20,54,35,6,64,1f
--1331017625157985988000000000 Typ=2 Len=10: 32,ea,0,19,4c,56,16,10,3,15
Update:
It was a bug in a statistics calc library. Under certain conditions a function returning Doubles generated a NaN value when taking sqrt while calculation sdev. Oracle driver (oracle14.zip) used in prepared statement construction does not validate data, but sends and writes raw bytes, which is what ended up causing the corruption. Interestingly enough, MS SQL Server driver did not let me make a prepared statement and threw an exception when i tried to set a value that was a NaN in a prep statement. Posting this just as "fyi"...
It doesn't mean anything, and it isn't 'for' anything; your data is corrupt, I'm afraid. The -- is an actual value from your table, but it isn't a number. Oracle's internal representation for numbers is covered in note 1031902.6 if you have access to that, or this explains it if you don't. If it was really a negative number then the last hexadecimal byte should be 66. Dumping the number it appears to be - with a single minus sign, not two, which is meaningless - gives:
select dump(-1331013400000000000000000000, 1016) from dual;
DUMP(-1331013400000000000000000000,1016)
----------------------------------------
Typ=2 Len=6: 31,58,46,64,43,66
Creating invalid numbers in Oracle isn't straightforward (I suppose you wouldn't expect it to be), but this is a method I've used before. One of the clues, apart from the double minus sign and that they are all the same length, is that converting the dumped value back into a number doesn't give the same result:
create table t42(value number);
declare
n number;
begin
dbms_stats.convert_raw_value('32ea004366', n);
insert into t42 (value) values (n);
end;
/
select value from t42;
VALUE
--------------------------------------
-<3:13400000000000000000000
This is from Oracle 9i, the closes I have now to an 8i database, so the results may vary a little.
Not being able to do to_number(value) is big clue too of course; there's an implicit to_char() when you do that so it's trying to convert the text representation to a number, which explains the error. The to_char() value doesn't match what a simple select does either, interestingly. You'd see the same error if you did that with your data.
select to_number(value) from t42;
select to_number(value) from t42
*
ERROR at line 1:
ORA-01722: invalid number
select to_char(value) from t42;
TO_CHAR(VALUE)
----------------------------------------
-`003400000000000000000000
Unless you know where the bad data came from and have the original still, you probably can't salvage these values. I think the best you can do is ignore it, or replace it with something that will migrate - if the field is nullable then null would be the safe option, otherwise I guess you'd have to pick a magic value.
Identifying and modifying the affected rows can be done via a function; possibly something like:
create or replace function null_bad_number(value number)
return number deterministic as
tmp_value number;
invalid_number exception;
pragma exception_init(invalid_number, -1722);
begin
select to_number(value) into tmp_value from dual;
return value;
exception
when invalid_number then
return null;
end;
/
With the same invalid value created earlier and one valid value:
insert into t42 (value) values (0.9574875526618150);
select * from t42;
VALUE
----------
-`.003E+24
.957487553
update t42 set value = null
where value is not null
and null_bad_number(value) is null;
1 row updated.
select * from t42;
VALUE
----------
.957487553
Not ideal by any means, but at this point I think you're just salvaging what you can. You could delete the rows rather than updating them, or set the value to something else, it depends how you want to proceed.
You could try to get Oracle involved to see if they can figure out what happened and see if they have any tricks to get back to the original values - which seems unlikely - but I'm not sure you'd get a lot of support for such an old version of the database.
Of course, without knowing how and when the corruption was introduced (via a dodgy import perhaps, or via a buggy OCI program), you have to question the validity of all the other data, both in that column and elsewhere. In this case the corruption looks very uniform - all the invalid values seem to be constructed the same way - so you may be OK. Generally though, something that puts incorrect bytes into an internal value could fluke a wrong, but still valid, value. It could look about right, or it could be orders of magnitude out from the original expected value, and there's really no way to tell.
这篇关于Oracle 2连字符号列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
