plsql - Trying to export a Oracle via PL/SQL gives a date of 0000-00-00

Question

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

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

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?

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

and

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

Answer 1

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

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

And if I run your procedure:

exec dump_table_to_csv('T42');

The resultant CSV has:

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

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.

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.