DELETE CURRENT OF in BULK COLLECT
481021Sep 1 2006 — edited Sep 5 2006Hi Guys I have a Reference Cursor
v_SQL := ' SELECT TABLEKEY FROM MYTABLE';
OPEN vCur_Inter FOR v_SQL;
LOOP
FETCH vCur_Inter BULK COLLECT INTO l_data LIMIT 200;
FOR i IN 1 .. l_data.COUNT
LOOP
nKey := l_data(i);
BEGIN
--PERFORM TASKS and accordingly DELETE the TABLEKEY row from MYTABLE
l_data.delete(i) ---THIS IS THE PROBLEM LINE
END;
END LOOP;
COMMIT;
EXIT WHEN vCur_Inter%NOTFOUND;
END LOOP;
CLOSE vCur_Inter;
l_data.delete(i) DOES NOT DELETE the data physically from the Oracle Table MYTABLE. In fact I am commiting after every BULK iteration or 200 rows.
How to do that? I thought of using "DELETE FROM MYTABLE WHERE CURRENT OF vCur_Inter" instead of l_data.delete(i), but that fails too, as we are deleting and looping in nested FORALL loop inside the CURSOR's own LOOP.