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

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Wed, 25 Jun 2008 09:25:44 +0200
Message-ID: <7bb63$4861f2f8$524b5c40$13949@cache2.tilbu1.nb.home.nl>


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

-- 

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

Original text of this message