Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: PLSQL exception handling problem

Re: PLSQL exception handling problem

From: <>
Date: Tue, 11 Sep 2007 06:00:03 -0700
Message-ID: <>

> #1 Since your cursor is FOR UPDATE NOWAIT, it's an all or nothing deal
> on the row level processing. If it cannot grab all the rows specified
> in the query, it simply throws the exception and that's that. If you
> want to process all the rows that have no row level lock (from another
> session), eliminate the FOR UPDATE NOWAIT and put the NOWAIT into your
> row level processing UPDATE statement.
> #2 Wrap you new and improved UPDATE statement in its own
> begin..exception...end where you catch your exception, instead of the
> outermost block as you do now.

Thanks for your response, Cleveridea. The "Other Code" should definitely be improved. Unfortunately, it is an application that was developed rather hastily by someone else that allows users to make changes to their information. Management does not want to shut it down for some maintenance that we need to do, let alone bring it down to improve upon it. The maintenace that we have to do is modify certain fields in people's data. My idea was use the FOR UPDATE NOWAIT when I do a loop through each record in the table, that way if someone had their own record locked while they were making a change, it would generate the wait error for me, then I could insert that user's id into a table of "users that could not be modified" then the loop would continue and try to modify the next record. Otherwise, without the NOWAIT, our update would wait for everyone's change to be committed, and I suppose that would mean when no one is in the system which might be an hour or a week. Received on Tue Sep 11 2007 - 08:00:03 CDT

Original text of this message