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 =
However, selecting to_char did yield something odd:
select to_char(date_field,'YYYYMMDD') from table_name t where t.id =
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 =
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.