SQL & PL/SQL

Hi,


I have to insert million rows to table to another table.

I have written oracle cursor for this requirement, when I am executed the procedure it’s interesting the million rows but sql puls or toad are not responding long time and come up with alert message like “Cursor must be opened”.

Please help me, why sql plus or toad are not responding long time and find the procedure below.

CREATE OR REPLACE PROCEDURE test_Proc AS

rcounttemp INTEGER := 1;
v_test table.test%TYPE;
v_test1 table.test1%TYPE;

Cursor test is Select test, test1 From table Where trunc(testDate) = trunc(sysdate)
AND testperiod = 'N';

BEGIN
OPEN test;
Loop
FETCH test INTO v_test, v_test1;

EXIT WHEN test%NOTFOUND;

Insert Into test2
(
test, test1
) Values (
v_test, v_test1
);

IF (MOD (rcounttemp, 10000) = 0) THEN
rcounttemp := 0;
COMMIT;
END IF;

rcounttemp := rcounttemp + 1;

END LOOP;

CLOSE test;

COMMIT;

END;
/