尝试通过PL/SQL导出Oracle给出了注册日期 [英] Trying to export a Oracle via PL/SQL gives a date of 0000-00-00

问题描述

我继承了一个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具有:

"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计划；此原始"技巧最初来自上一个问题有点相关.

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.

这篇关于尝试通过PL/SQL导出Oracle给出了注册日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！