Oracle Bulk Collection Script to Insert Data

Question

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?

Answer 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.