I am trying to figure out how to delete a record using the FOR .. IN .. LOOP
FOR REC IN (SELECT * FROM WORKLIST) LOOP
GET_DATA(REC);
--DELETE REC FROM WORKLIST
END LOOP;
The GET_DATA procedure explodes every row to multiple ones so the current one becomes obsolete. I am trying to delete just the current record. Not sure if this is possible using the REC object in this case.
Sadly, WORKLIST does not have any keys.
Use current of on cursor :
DECLARE
CURSOR c1 IS
SELECT * from worklist FOR UPDATE <any column name>;
BEGIN
open c1;
loop
fetch c1 into crow;
exit when c1%notfound;
DELETE FROM worklist WHERE CURRENT OF c1;
end loop;
COMMIT;
close c1;
END;
For Update is generally discourged. This can cause deadlocks. Read more here asktom.oracle.com/pls/asktom/…
FOR UPDATE caused deadlocks. I was referring correct. Please refer the whole articledeadlock detected, which causes the second attempted transaction to fail. That's what we want to happen. Without FOR UPDATE both sessions would fail.FOR UPDATE can lead to deadlocks. Where did you find me worng here. And thats why he referred an example with Autonomus transaction.If WORKLIST has a primary key, just use it:
DELETE FROM WORKLIST WHERE <PK> = REC.<PK>
If no primary key I would try:
DECLARE
vTmp schema.worklist%rowtype;
BEGIN
FOR rec IN (SELECT rowid, w.* FROM WORKLIST w) LOOP
vTmp := null;
vTmp.col1 := rec.col1;
vTmp.col2 := rec.col2;
-- And so on ...
GET_DATE(vTmp);
DELETE FROM WORKLIST WHERE rowid = rec.rowid;
END LOOP;
END;
