Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

Affiliations

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 = ;

’04-AUG-17′

However, selecting to_char did yield something odd:

select to_char(date_field,'YYYYMMDD') from table_name t where t.id = ;

’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 = ;

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.