ORA-01801: Date Format is too Long for Internal Buffer

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.

Find out how I resolved this by reading the post on my Oracle Musings blog.