Re: PLSQL exception handling problem

RogBaker_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;
Several things don't make sense to me about your block not pointed out by cleveridea.

1. Your cursor is on one table, "DENTAL", and your FOR UPDATE OF refers to a column in that table. Then you update "LINDAC.DENTALROGER." How is this supposed to work?
2. What purpose is served by a cursor and a loop. A simple UPDATE statement should suffice unless this isn't your real code. -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org