appending to cursor in oracle
- by Omnipresent
I asked a question yesterday which got answers but didnt answer the main point. I wanted to reduce amount of time it took to do a MINUS operation.
Now, I'm thinking about doing MINUS operation in blocks of 5000, appending each iterations results to the cursor and finally returning the cursor.
I have following:
V_CNT NUMBER :=0;
V_INTERVAL NUMBER := 5000;
begin
select count(1) into v_cnt from TABLE_1
while (v_cnt > 0)
loop
open cv_1 for
SELECT A.HEAD,A.EFFECTIVE_DATE,
FROM TABLE_1 A
WHERE A.TYPE_OF_ACTION='6' AND A.EFFECTIVE_DATE >= ADD_MONTHS(SYSDATE,-15)
AND A.ROWNUM <= V_INTERVAL
MINUS
SELECT B.head,B.EFFECTIVE_DATE,
FROM TABLE_2 B
AND B.ROWNUM <= V_INTERVAL
V_CNT := V_CNT - V_INTERVAL;
END LOOP;
end;
However, as you see...in each iteration the cursor is overwritten. How can I change the code so that in each iteration it appends to cv_1 cursor rather than overwriting?
