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

From: joel garry <>
Date: Wed, 25 Jun 2008 11:27:50 -0700 (PDT)
Message-ID: <>

On Jun 24, 11:40 pm, "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

May not have anything to do with your issue, but I found bug 5345437 fascinating - a cursor is reused, but doesn't know about changed NLS settings when shared pool under load - or recently flushed. Oracle db version dependent, some things not fixed until 11.1. Also interesting in that it is an example of an admitted bug without consistent replicability.


-- is bogus.
Received on Wed Jun 25 2008 - 13:27:50 CDT

Original text of this message