ORA 1801 - Dateformat too long for internal buffer

[Nicolas Bronke]

On one database table I got this strange error (Oracle 9.2.0.4 Linux)

ORA-01801: Datumsformat zu lang f³r internen Puffer

I found out that in a date column and in some rows the datevalue is
"corrupted"
If I make an update on this specific row and column everything works okay.

But I am wondering how such incorrect value could be entered in this table.
As I know, this table is updated by a delphi/BDE program and a JAVA program.
But I cannot imagine, that from there the problem is coming.

Thank you for any hints

Regards
Nicolas

[Mark D Powell]

Nicolas, from past experience I know that OCI programs can insert
invalid values in date columns. It could well be that the underlying
code in Delphi/BDE or perhaps even the jdbc drivers you are using use
OCI.

You might consider adding a trigger to the table that validates the
date information as a debugging tool to learn where the invalid
information is coming from. That is you can add it, capture an error,
and then drop the trigger to allow normal processing while you do more
research with additional information in hand.

HTH -- Mark D Powell --

[Nicolas Bronke]

Post by Mark D Powell
Nicolas, from past experience I know that OCI programs can insert
invalid values in date columns. It could well be that the underlying
code in Delphi/BDE or perhaps even the jdbc drivers you are using use
OCI.
You might consider adding a trigger to the table that validates the
date information as a debugging tool to learn where the invalid
information is coming from. That is you can add it, capture an error,
and then drop the trigger to allow normal processing while you do more
research with additional information in hand.

Thanks. The bad thing is, I still searching to reproduce this behaviour. For
the Prod-Database I a made a "workaround" and created a trigger on this
table and for the "problem"-column, which removes the time, what I do not
need in this case. But this cannot be really the be solution, because this
could happen again on other date columns or tables.
I have to seach further.
Regards
Nicolas