SQL & PL/SQL

Hi There

Having a bit of an issue with my stored procedure. I was told to speed up the time it takes to run this procedure, in the end we will be processing millions of rows once this is complete.

So after a bit of reading i decided to use BULK COLLECT INTO and FORALL

I am able to get the BULK COLLECT to compile and run (not doing anything in the loop)

Once I put the FORALL INSERT into the mix I can't compile. I get the ORA-03001 error.

Database version is 10g.

Any ideas on what I am doing wrong here?

Here is my code, seems pretty simple

create or replace procedure proc_test_forall AS

CURSOR C_LIST IS
SELECT R.ACCTNBR
FROM table1 R;

TYPE rtxn_array IS TABLE OF C_LIST%ROWTYPE;

CUR_ARRAY RTXN_ARRAY;

BEGIN

OPEN C_LIST;
LOOP
FETCH C_LIST BULK COLLECT INTO CUR_ARRAY LIMIT 100;
EXIT WHEN C_LIST%NOTFOUND;

FORALL i IN 1 .. CUR_ARRAY.COUNT
INSERT INTO table2

VALUES
CUR_ARRAY(i);

END LOOP;
CLOSE C_LIST;

END;