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

[Maija-Leena]

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

[Frank van Bortel]

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

[Maija-Leena]

They are Oracle date datatype.

Maija-Leena

"Frank van Bortel" <frank.va...@gmail.com> wrote in message
news:7bb63$4861f2f8$524b5c40$13...@cache2.tilbu1.nb.home.nl...

[joel garry]

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