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

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.

More Related Contents:

Leave a Comment Cancel reply