4 Replies Latest reply: Nov 4, 2011 10:50 AM by BluShadow RSS

    Loop 2 times on same cursor

    827574
      Hi,

      If you open a cursor and loop on it, and after the loop is finished, then loop on it again (without closing the cursor in between), will it loop again on the same data as was queried during the opening of the cursor? In other words, will the read-consistency of the active set created when opening the cursor be maintained when looping the second time?

      I need to check the data of table A, against table B (= first loop), and then empty table B and fill it with the data from table A (= second loop). But I need to be sure that the data from table A used to compare in the first loop, is exactly the data that is written to table B.

      Thanks,

      Roel
        • 1. Re: Loop 2 times on same cursor
          theoa
          If you open a cursor and loop on it, and after the loop is finished, then loop on it again (without closing the cursor in between), ...
          That is not possible, so the rest of the question is pretty meaningless.
          • 2. Re: Loop 2 times on same cursor
            bpat
            Use BULK Collect
            OPEN cur_test;
            FETCH cur_test BULK COLLECT INTO tbl_test;
            CLOSE cur_test;
            
            FOR i in 1..tbl_test.COUNT
            LOOP
            
            Do Something for first time
            
            END LOOP;
            
            FOR i in 1..tbl_test.COUNT
            LOOP
            
            Do Something for second time
            
            END LOOP;
            • 3. Re: Loop 2 times on same cursor
              Frank Kulash
              Hi, Roel,

              Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables.
              Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
              If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
              Always say which version of Oracle you're using.

              It's unclear why you need two cursors. You can probably do everything you want to in a single MERGE statement, especially if you're using Oracle 11, where MERGE can DELETE rows.
              • 4. Re: Loop 2 times on same cursor
                BluShadow
                Read the following article on Ref Cursors... the principle is the same...

                {thread:id=886365}