Good Forever

Oracle 10.2.0.1 (I know, version out-of-date) on Windows 2003:

The other day one of my stored procedures error’ed out with:

ORA-01801: date format is too long for internal buffer

somewhere near a line in which I was doing the following assignment:

d := to_char(c.date_field,'YYYYMMDD');

Where d is of type varchar2, and c is a row from a cursor for loop, and date_field is a date column in the cursor

selecting the date field from the database didn’t look odd:

select date_field from table_name t where t.id = <id of failing row>;

’04-AUG-17′

However, selecting to_char did yield something odd:

select to_char(date_field,'YYYYMMDD') from table_name t where t.id = <id of failing row>;

’00000000′

Odd, no 04, no AUG and no 17.

At this point I could tell something was up with the data in the field, so I resorted to dump’ing it’s contents:

select dump(date_field) from table_name t where t.id = <id of failing row>;

Typ=12 Len=7: 220,117,8,4,1,1,1

Uh-oh — the first byte is supposed to indicate the century (subtract 100 from the value to get the century), and legitimate centuries stop at 99. This value indicates a century value of 120. For an “actual” date of:

August 4th, 12017

I guess what was weird to me was that the PL/SQL to_char reported an error, while the SQL to_char didn’t (instead it returned an odd ’0000000′ result).

Going forward, it was relatively easy to find rows like this in the table:

select id, dump(date_field) from table_name t where date_field > to_date('99991231','YYYYMMDD');

We actually left the bad data alone and popped a view on top of the table using LEAST / GREATEST to cap the value at 9999-12-31, which minimized the code impact to our PL/SQL routines.

Still and all, I can’t wait to hop into my time machine with Jules Verne and see what happens on that date.

One Response to “Good Forever”

  1. ORA-01801: Date Format is too Long for Internal Buffer – All Things Oracle Says:

    [...] out how I resolved this by reading the post on my Oracle Musings blog. All Things [...]

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder