Re: PLSQL exception handling problem

RogBaker@xxxxxxxxx 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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: ADO Performance
    ... sections (this will vary a little depending on cursor location): ... Find out how long it takes to run the query and get the recordset ... place some logic on the server side to reduce the execution time. ... loop with oRS.MoveNext command. ...
    (microsoft.public.vb.general.discussion)
  • Re: PLSQL exception handling problem
    ... I have some PLSQL that is looping through a recordset and I am ... This could possibly fail because I have another session which could ... CURSOR cursor1 IS ... for update of dentalempcost NOWAIT; ...
    (comp.databases.oracle.server)
  • Re: PLSQL exception handling problem
    ... I have some PLSQL that is looping through a recordset and I am ... This could possibly fail because I have another session which could ... CURSOR cursor1 IS ... for update of dentalempcost NOWAIT; ...
    (comp.databases.oracle.server)
  • Re: using adOpenDynamic
    ... Are you sure a default forward-only cursor won't do the job for ... Set objRS = CreateObject ... Loop ... response.write "Recordset was empty" ...
    (microsoft.public.inetserver.asp.db)
  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Engpass ist vor allem das LAN und der Server selbst. ... ob man mit einem SQL-Server oder der Jet-Engine ... Cursor und statischen Recordsets, egal welches Datenbanksystem ... dass eine Bewegung im Recordset eben auch ...
    (microsoft.public.de.vb.datenbank)