How do I use bulk collect and insert in Pl/SQl

Question

I want to fetch around 6 millions rows from one table and insert them all into another table. How do I do it using BULK COLLECT and FORALL ?

Answer 1

declare
  -- define array type of the new table
  TYPE new_table_array_type IS TABLE OF NEW_TABLE%ROWTYPE INDEX BY BINARY_INTEGER;

  -- define array object of new table
  new_table_array_object new_table_array_type;

  -- fetch size on  bulk operation, scale the value to tweak
  -- performance optimization over IO and memory usage
  fetch_size NUMBER := 5000;

  -- define select statment of old table
  -- select desiered columns of OLD_TABLE to be filled in NEW_TABLE
  CURSOR old_table_cursor IS
    select * from OLD_TABLE; 

BEGIN

  OPEN old_table_cursor;
  loop
    -- bulk fetch(read) operation
    FETCH old_table_cursor BULK COLLECT
      INTO new_table_array_object LIMIT fetch_size;
    EXIT WHEN old_table_cursor%NOTFOUND;

    -- do your business logic here (if any)
    -- FOR i IN 1 .. new_table_array_object.COUNT  LOOP
    --   new_table_array_object(i).some_column := 'HELLO PLSQL';    
    -- END LOOP;    

    -- bulk Insert operation
    FORALL i IN INDICES OF new_table_array_object SAVE EXCEPTIONS
      INSERT INTO NEW_TABLE VALUES new_table_array_object(i);
    COMMIT;

  END LOOP;
  CLOSE old_table_cursor;
End;

Hope this helps.

Answer 2

oracle

Below is an example From

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE PartNum IS TABLE OF parent.part_num%TYPE
INDEX BY BINARY_INTEGER;
pnum_t PartNum;

TYPE PartName IS TABLE OF parent.part_name%TYPE
INDEX BY BINARY_INTEGER;
pnam_t PartName;

BEGIN
  SELECT part_num, part_name
  BULK COLLECT INTO pnum_t, pnam_t
  FROM parent;

  FOR i IN pnum_t.FIRST .. pnum_t.LAST
  LOOP
    pnum_t(i) := pnum_t(i) * 10;
  END LOOP;

  FORALL i IN pnum_t.FIRST .. pnum_t.LAST
  INSERT INTO child
  (part_num, part_name)
  VALUES
  (pnum_t(i), pnam_t(i));
  COMMIT;
END