Re: PLSQL exception handling problem

On Sep 11, 8:00 am, RogBa..._at_gmail.com wrote:
> > #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.

I understand your position; I think we've all been there.

I would like to encourage you to pursue your line of thought where you set aside the work that is skipped into a separate queue of pending work.

Simply throwing a dbms_output() as the outcome of a WHEN OTHERS THEN is a critical flaw. When catching errors (especially WHEN OTHERS) you should be looking towards only two outcomes: #1: rethrow the error or another error or #2: definitively follow an alternate (even more bulletproof) processing path.

Whatever you planned to do manually when you receive this "skip" dbms_output feedback, you should automate into your procedure itself instead. Such would fulfill outcome variety #2 above. Received on Tue Sep 11 2007 - 10:41:03 CDT