Re: PLSQL exception handling problem

On Sep 10, 3:07 pm, RogBa..._at_gmail.com wrote:
> Howdy folks I have been stuck on this problem most of the day.
> I have some PLSQL that is looping through a recordset and I am
> attempting to update a field.
> This could possibly fail because I have another session which could
> be updating the same field, but has not commited.
> I am trying to trap the error Resource Busy, which I do, but then
> procedure exits without processing more records. I know that somehow,
> I must move the error trapping inside a block within the loop, but I
> just cannot seem to get it right.
> Anyone got any hints for me.? Thanks
> (Oracle 10g)
>
> DECLARE
> v_ErrorCode NUMBER(15);
> v_ErrorText VARCHAR2(200);
> e_resource_busy exception;
> pragma exception_init(e_resource_busy,-54);
> CURSOR cursor1 IS
> select employeeid,dentalempcost from dental where employeeid in
> ('8641','372','4245') for update of dentalempcost NOWAIT;
> BEGIN
> For myrecord in cursor1 LOOP
> update lindac.dentalroger set DENTALEMPCOST=20 where current of
> cursor1;
> dbms_output.put_line('change made');
> END LOOP;
> commit;
> EXCEPTION
> WHEN e_resource_busy THEN dbms_output.put_line('busy');
> WHEN OTHERS THEN
> BEGIN
> v_ErrorCode:=SQLCODE;
> v_ErrorText:=SUBSTR(SQLERRM,1,200);
> dbms_output.put_line(v_ErrorCode);
> dbms_output.put_line(v_ErrorText);
> END;
> END;
There's more than a few problems with your block.

#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.

#3 Only bad things are going to happen by having the COMMIT in your block. Let the entity calling the block commit or not.

#4 Generally, the "other" code that is generating row level locks should be improved to not hang onto data those locks longer than it has to, and this block of code should do the waiting it needs to do the row level processing you want done in the first place rather than just skip work it is supposed to do.

I hope that helps. Received on Mon Sep 10 2007 - 22:47:59 CDT