0

I have the following script

DECLARE

CURSOR cursor1 IS
    SELECT *
    FROM table1;

TYPE cursor_aat IS TABLE OF cursor1%ROWTYPE;        

l_cursor cursor_aat;

BEGIN        
    OPEN cursor1;

    LOOP

        FETCH cursor1
        BULK COLLECT INTO l_cursor LIMIT 200;

        FOR INDX IN 1 .. l_cursor.COUNT LOOP            
            INSERT INTO new_table
            (col1, col2)
            values
            (l_cursor(INDX).col1, l_cursor(INDX).col2);

        END LOOP;

        EXIT WHEN l_cursor.COUNT < 200;
    END LOOP;

END;

But it complains that it doesn't recognise l_cursor(INDX).col1. What am I doing wrong here?

Paul Michaels
  • 16,185
  • 43
  • 146
  • 269
  • 1
    This code seems to work for me assuming that `table1` has columns named `col1` and `col2`. What version of Oracle are you using? What is the exact error message that you are getting? – Justin Cave Sep 19 '12 at 15:48
  • 1
    seems like a perfect case to try FORALL instead of FOR – Kirill Leontev Sep 19 '12 at 15:50

1 Answers1

1

Why do you use that loop in the first place? It slows things down and creates unnecessary resource consumption on the server.

The whole PL/SQL script can be replaced with a single statement:

INSERT INTO new_table
(col1, col2)
SELECT col1, col2
FROM table1;

To copy only 200 rows from table1, use the following:

INSERT INTO new_table
(col1, col2)
SELECT col1, col2
FROM table1
WHERE rownum < 200;

Note that this does not guarantee which rows are copied as rows can be returned in any order by the SELECT statement. If you want 200 specific rows you need to apply an order by.

  • Sorry, I should have explained, the code above isn't complete - it's just a simplified version. The actual code inserts into more than one table. – Paul Michaels Sep 20 '12 at 08:17
  • @pm_2: it will still be more efficient to run one `insert ... select` for each target table than processing everything row-by-row. –  Sep 20 '12 at 08:32
  • More efficient but sadly not possible. There's some logic that needs to execute as well. – Paul Michaels Sep 20 '12 at 12:17