appending to cursor in oracle

Posted by Omnipresent on Stack Overflow See other posts from Stack Overflow or by Omnipresent
Published on 2010-03-23T18:43:55Z Indexed on 2010/03/23 19:03 UTC
Read the original article Hit count: 377

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about cursor