Re: Unusual ORA-01438 Errors

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 19 May 2006 07:47:13 GMT
Message-ID: <1148024832.344092@proxy.dienste.wien.at>

MellyGirl <melly-girl_at_comcast.net> wrote:
> I have some Pro C code that has been in production since 1999. Lately, our
> processes seem to run fine, but will occasionally produce an ORA-01438 :
> value larger than specified precision allows for this column. When I check
> the data that is in the database, the values do not seem to warrant this
> error. Furthermore, the input values are not large at all, so it is pretty
> perplexing.

It would be good to know

a) the table definition
b) the values in the table row before the update
c) the values of the host variables in your update statement

for an instance where the update fails.

Additionally, it could prove helpful to know the value of the NLS_LENGTH_SEMANTICS initialization and session parameters, the database character set and the client character set.

You could have the Pro*C program print out enough relevant information (host variables, WHSE_ID and ITEM_ID) when it hits an error.

> This SQL runs pretty fast, but I do know that it is being hit over a
> million times and I am afraid that there is a conflict with two or more
> requests hitting the same WHSE_ID and ITEM_ID combination. If that is
> the case, I wonder why we are not experiencing a row lock.

You possible experience row locks all the time without even noticing. A row lock is nothing bad or dangerous, it only means that one session must wait for another one to finish its transaction before it can proceed. So if 'this SQL runs pretty fast', it shouldn't be a problem at all.

Yours,
Laurenz Albe Received on Fri May 19 2006 - 02:47:13 CDT