Re: ORA-01801: date format is too long for internal buffer (stored procedure called from Java using decode)

From: Frank van Bortel <>
Date: Wed, 25 Jun 2008 09:25:44 +0200
Message-ID: <7bb63$4861f2f8$524b5c40$>

Maija-Leena wrote:
> Hi,
> I'm calling a database procedure from Java and I sometimes get ORA-0180 from
> this update:
> UPDATE table1 SET date1=DECODE(date1,NULL,SYSDATE,date1), date2=SYSDATE
> WHERE ...
> What makes this difficult is that most of the time this works even with the
> very same row (it has a valid datevalue in date1).
> Is this due to Application Server's timezone/language changing or something
> like that ? I don't think I'll ever get error making the update from
> SqlPlus.
> I'll change this to NVL(date1,SYSDATE) as it should be so that might solve
> my problem, but I still would like to know why I get this error.
> Thanks in advance,
> Maija-Leena

What type is date1 (and date2, for that matter) If not Oracle date, *always* perform an explicit format conversion: to_date(date1,'dd-mon-yyyy hh24:mi:ss') or whatever format your dates are in


Frank van Bortel
Received on Wed Jun 25 2008 - 02:25:44 CDT

Original text of this message