oracle - 尝试通过 PL/SQL 导出 Oracle 给出的日期为 0000-00-00

Question

我继承了一个 Oracle .dmp 文件，我试图将其转换为 CSV，以便将其加载到 MySQL 中。

描述了我使用的一般方法 here .不过，我在一行中遇到了问题。它包含一个日期 5544-09-14，如下所示:

alter session set nls_date_format = 'dd-MON-yyyy';
select OID, REF, TRADING_DATE From LOAN WHERE REF = 'XXXX';

OID REF                  TRADING_DATE
--- -------------------- ------------
1523 XXXX                 14-SEP-5544

这是来自遗留系统的垃圾数据，它没有验证输入日期。我想知道为什么 my PL/SQL function to export the data不过这个值让你窒息？

它导出 TRADING_DATE 值为“0000-00-00T00:00:00”的行，我不确定为什么？

SELECT dump(TRADING_DATE) FROM LOAN WHERE REF = 'XXXX';

DUMP(TRADING_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 44,156,9,14,1,1,1

和

SELECT to_char(trading_date, 'YYYYMMDDHH24MISS') FROM LOAN WHERE REF = 'XXXX';
TO_CHAR(TRADIN
--------------
00000000000000

Answer 1

存储在该列中的值不是有效日期。 dump 的第一个字节应该是世纪，根据 Oracle 支持说明 69028.1，它以“excess-100”表示法存储，这意味着它的值应该是 100 + 实际世纪；所以1900就是119，2000就是120，5500就是155。所以44就是-5600；您存储的日期似乎实际代表 5544-09-14 BC。由于 Oracle 仅支持年份介于 -4713 和 +9999 之间的日期，因此无法识别。

您可以很容易地重新创建它；最棘手的一点是首先将无效日期输入数据库:

create table t42(dt date);

Table created.

declare
    d date;
begin
    dbms_stats.convert_raw_value('2c9c090e010101', d);
    insert into t42 (dt) values (d);
end;
/

PL/SQL procedure successfully completed.

select dump(dt), dump(dt, 1016) from t42;

DUMP(DT)
--------------------------------------------------------------------------------
DUMP(DT,1016)
--------------------------------------------------------------------------------
Typ=12 Len=7: 45,56,9,14,1,1,1
Typ=12 Len=7: 2d,38,9,e,1,1,1

所以这有一行与您所做的相同的数据。使用 alter session 我可以看到有效日期:

alter session set nls_date_format = 'DD-Mon-YYYY';
select dt from t42;

DT
-----------
14-Sep-5544

alter session set nls_date_format = 'YYYYMMDDHH24MISS';
select dt from t42;

DT
--------------
55440914000000

但如果我使用明确的日期掩码，它只会变成零:

select to_char(dt, 'DD-Mon-YYYY'), to_char(dt, 'YYYYMMDDHH24MISS') from t42;

TO_CHAR(DT,'DD-MON-Y TO_CHAR(DT,'YY
-------------------- --------------
00-000-0000          00000000000000

如果我运行你的程序:

exec dump_table_to_csv('T42');

生成的 CSV 具有:

"DT"
"0000-00-00T00:00:00"

我认为不同之处在于那些试图显示日期的人坚持使用内部日期数据类型 12，而那些显示零的人使用外部数据类型 13，如注释 69028.1 中所述。

简而言之，您的程序没有做错任何事情，它尝试导出的日期在内部无效。除非你知道它应该是什么日期，从你的出发点来看这似乎不太可能，我认为除了猜测或忽略它之外你无能为力。除非，也许，你知道数据是如何插入的，并且可以弄清楚它是如何被破坏的。

我认为它更有可能来自 OCI 程序，而不是我在这里所做的；这个“原始”技巧最初来自 here .您可能还想查看注释 331831.1。和 this previous question有点相关。