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

From: joel garry <joel-garry_at_home.com>
Date: Wed, 25 Jun 2008 11:27:50 -0700 (PDT)
Message-ID: <95458998-3355-41d7-b2a1-a9cd453cf429@w34g2000prm.googlegroups.com>


On Jun 24, 11:40 pm, "Maija-Leena" <kangasm..._at_netti.fi> 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.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20080625/news_1n25cyber.html
Received on Wed Jun 25 2008 - 13:27:50 CDT

Original text of this message