Re: PLSQL exception handling problem

On Sep 11, 11:40 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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;
>
> 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.

1. You correct DA. Sometimes I try to simply things for posting. the table is actually LINDAC.DENTALROGER. I just tried to replace it with DENTAL in the post to make it easier on the eyes. Sorry for missing one.
2. I was trying to determine each one that would fail, identify that record, but update the ones that I could.