11g: Bulk Insert Example

I happen to work recently on a scenario where I had to inset close to 46 Million records . I had to perform a BULK COLLECT (Insert) script so that I dont get ORA-01555: Snapshot too old error.

DECLARE
cursor c1 is
SELECT *
FROM DS.TIME_TEMP_TBLE;

TYPE TYPE_TB1 IS TABLE OF DS.TIME_TARGET_TBLE%ROWTYPE;
TARG_TB1 TYPE_TB1;

l_limit number := 1000000;
BEGIN

OPEN c1;
LOOP
                Fetch c1 BULK COLLECT INTO TARG_TB1 LIMIT l_limit;

                FORALL i IN 1..TARG_TB1.COUNT
                insert into DS.TIME_TARGET_TBLE
                values TARG_TB1(i);
 COMMIT;
        EXIT WHEN c1%ROWCOUNT < l_limit;
END LOOP;
CLOSE c1;
END;
/
COMMIT;
EXIT;

Other different options are to use FOR instead of  FORALL, or use

FORALL i IN TARG_TB1.first..TARG_TB1.last 

From my testing, I felt that using COUNT was faster.