Re: Unusual ORA-01438 Errors

MellyGirl <melly-girl_at_comcast.net> wrote:
> I know I can print out the "host variable" information, but is there a way
> to print out the values in the table row without doing a "select" to get it.
> This is what I am trying to do because they really do not want a separate
> "select" added. I do not believe that can be done without doing a select on
> the table to pull the values before the update.

My idea was simply that:
Your program fails, prints out the host variable values and the primary key from the table. Then you use SQL*Plus to get the original values stored in the database (hoping that nobody changed them in the meantime).

Maybe this approach is too simple...

> I agree that a row lock is not a bad thing. It is just that another member
> of my team thinks that the information pulled back for the update could have
> been corrupted. I would like to eliminate that idea, but I have to prove it
> first.

'Pulled back' is unclear to me.

If you have a statement like

UPDATE mytable SET col1 = col2 + 42 WHERE primkey = 4711

the retrieval of col2, adding of 42, and storage into col1 will take place within one statement and hence within one transaction. Every other statement that wants to modify the same row will have to wait.

The UPDATE statement will create an X lock on the selected row BEFORE it retrieves the value of col2.

The only way you can get problems of that sort is when you first select the value of col2 (let's assume it is 5) in one SELECT statement (without a FOR UPDATE clause), then take a coffee break, come back and UPDATE col1 to 47. In that case another session could have modified the value in the meantime.

Yours,
Laurenz Albe Received on Fri May 19 2006 - 08:38:08 CDT