PLSQL exception handling problem

From: <RogBaker_at_gmail.com>
Date: Mon, 10 Sep 2007 20:07:16 -0000
Message-ID: <1189454836.744341.168480@w3g2000hsg.googlegroups.com>

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; Received on Mon Sep 10 2007 - 15:07:16 CDT