RogBaker_at_gmail.com wrote:
> 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.
SELECT FOR UPDATE
UPDATE
COMMIT
Everything else is superfluous.
--
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
Received on Tue Sep 11 2007 - 14:52:59 CDT
