Дефисы Oracle 2 в числовом столбце?

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, тогда 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), вы должны подвергнуть сомнению достоверность всех других данных, как в этом столбце, так и в других местах. В этом случае повреждение выглядит очень однородным — все недопустимые значения кажутся построенными одинаково — так что вы можете быть в порядке. Однако, как правило, что-то, что помещает неправильные байты во внутреннее значение, может привести к неправильному, но все же допустимому значению. Оно может выглядеть примерно так, а может отличаться от исходного ожидаемого значения на несколько порядков, и на самом деле нет никакого способа сказать.