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

2021-12-06 00:00:00 oracle plsql

我继承了一个 Oracle .dmp 文件，我正在尝试将其导入 CSV，以便将其加载到 MySQL 中.

I have inherited an Oracle .dmp file which I'm trying to get into CSV so that I can load it into MySQL.

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

The general approach I'm using is described here. I'm having a problem with one row though. It contains a date of 5544-09-14 like so:

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

这是来自未验证输入日期的旧系统的垃圾数据.我想知道为什么 我的 PL/SQL 导出数据函数会卡住不过这个值?

This is garbage data from the legacy system which didn't validate the input dates. I'm wondering why my PL/SQL function to export the data chokes on this value though?

它导出了 TRADING_DATE 值为0000-00-00T00:00:00"的那一行，我不知道为什么?

It exports that row with a TRADING_DATE value of '0000-00-00T00:00:00' and I'm not sure why?

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

推荐答案

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

The value stored in that column is not a valid date. The first byte of the dump should be the century, which according to Oracle support note 69028.1 is stored in 'excess-100' notation, which means it should have a value of 100 + the actual century; so 1900 would be 119, 2000 would be 120, and 5500 would be 155. So 44 would represent -5600; the date you have stored appears to actually represent 5544-09-14 BC. As Oracle only supports dates with years between -4713 and +9999, this isn't recognised.

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

You can recreate this fairly easily; the trickiest bit is getting the invalid date into the database in the first place:

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 我可以看到看起来像一个有效日期:

So this has a single row with the same data you do. Using alter session I can see what looks like a valid date:

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

但如果我使用显式日期掩码，它只会得到零:

But if I use an explicit date mask it just gets zeros:

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 具有:

The resultant CSV has:

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

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

I think the difference is that those that attempt to show the date are sticking with internal date data type 12, while those that show zeros are using external data type 13, as mentioned in note 69028.1.

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

So in short, your procedure isn't doing anything wrong, the date it's trying to export is invalid internally. Unless you know what date it was supposed to be, which seems unlikely given your starting point, I don't think there's much you can do about it other than guess or ignore it. Unless, perhaps, you know how the data was inserted and can work out how it got corrupted.

我认为它更有可能来自 OCI 程序，而不是我在这里所做的；这个原始"技巧最初来自 此处.您可能还想查看注释 331831.1.和前面的问题有些相关.

I think it's more likely to be from an OCI program than what I did here; this 'raw' trick was originally from here. You might also want to look at note 331831.1. And this previous question is somewhat related.

相关文章