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

Answer 3

The SQL engine parse and executes the SQL Statements but in some cases ,returns data to the PL/SQL engine.

During execution a PL/SQL statement, every SQL statement cause a context switch between the two engine. When the PL/SQL engine find the SQL statement, it stop and pass the control to SQL engine. The SQL engine execute the statement and returns back to the data in to PL/SQL engine. This transfer of control is call Context switch. Generally switching is very fast between PL/SQL engine but the context switch performed large no of time hurt performance . SQL engine retrieves all the rows and load them into the collection and switch back to PL/SQL engine. Using bulk collect multiple row can be fetched with single context switch.

Example : 1

DECLARE

Type stcode_Tab IS TABLE OF demo_bulk_collect.storycode%TYPE;
Type category_Tab IS TABLE OF demo_bulk_collect.category%TYPE;
s_code stcode_Tab;
cat_tab category_Tab;
Start_Time NUMBER;
End_Time NUMBER;

CURSOR c1 IS 
select storycode,category from DEMO_BULK_COLLECT;
BEGIN
   Start_Time:= DBMS_UTILITY.GET_TIME;
   FOR rec in c1
   LOOP
     NULL;
     --insert into bulk_collect_a values(rec.storycode,rec.category);
   END LOOP;
    End_Time:= DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('Time for Standard Fetch  :-' ||(End_Time-Start_Time) ||'  Sec');

    Start_Time:= DBMS_UTILITY.GET_TIME;    
    Open c1;
        FETCH c1 BULK COLLECT INTO s_code,cat_tab;
    Close c1;
 FOR x in s_code.FIRST..s_code.LAST
 LOOP
 null;        
 END LOOP;
End_Time:= DBMS_UTILITY.GET_TIME; 
DBMS_OUTPUT.PUT_LINE('Using Bulk collect fetch time :-' ||(End_Time-Start_Time) ||'  Sec');
END;

Answer 4

CREATE OR REPLACE PROCEDURE APPS.XXPPL_xxhil_wrmtd_bulk 
AS

   CURSOR cur_postship_line
   IS
     SELECT ab.process, ab.machine, ab.batch_no, ab.sales_ord_no, ab.spec_no,
          ab.fg_item_desc brand_job_name, ab.OPERATOR, ab.rundate, ab.shift,
          ab.in_qty1 input_kg, ab.out_qty1 output_kg, ab.by_qty1 waste_kg,
       --   null,
          xxppl_reports_pkg.cf_waste_per (ab.org_id,ab.process,ab.out_qty1,ab.by_qty1,ab.batch_no,ab.shift,ab.rundate) waste_percentage,
          ab.reason_desc reasons, ab.cause_desc cause,
          to_char(to_date(ab.rundate),'MON')month,
          to_char(to_date(ab.rundate),'yyyy')year,
          xxppl_org_name (ab.org_id) plant
   FROM   (SELECT a.org_id,
                  (SELECT so_line_no
                   FROM   xx_ppl_logbook_h x
                   WHERE  x.batch_no = a.batch_no
                   AND    x.org_id = a.org_id) so_line_no,
                 (SELECT spec_no
                  FROM   xx_ppl_logbook_h x
                  WHERE  x.batch_no = a.batch_no
                  AND    x.org_id = a.org_id
                  and    x.SALES_ORD_NO=a.sales_ord_no
                  and    x.OPRN_ID =a.OPRN_ID
                  and    x.RESOURCES = a.RESOURCES) SPEC_NO,       
                  (SELECT OPERATOR
                   FROM   xx_ppl_logbook_f y, xx_ppl_logbook_h z
                   WHERE  y.org_id = a.org_id
                   AND    y.batch_no = a.batch_no
                   AND    y.rundate = a.rundate
                   AND    a.process = y.process
                   AND    a.resources = y.resources
                   AND    a.oprn_id = y.oprn_id
                   AND    z.org_id = y.org_id
                   AND    z.batch_no = y.batch_no
                   AND    z.batch_id = z.batch_id
                   AND    z.rundate = y.rundate
                   AND    z.process = y.process) OPERATOR,
                  a.batch_no, a.oprn_id, a.rundate, a.fg_item_desc,
                  a.resources, a.machine, a.sales_ord_no, a.process,
                  a.process_desc, a.tech_desc, a.sub_invt, a.by_qty1,
                  a.by_qty2, a.um1, a.um2, a.shift,
                  DECODE (shift, 'I', 1, 'II', 2, 'III', 3) shift_rank,
                  a.reason_desc, a.in_qty1, a.out_qty1, a.cause_desc
           FROM   xxppl_byproduct_waste_v a
           WHERE  1 = 1
--AND a.org_id = (CASE WHEN :p_orgid IS NULL THEN a.org_id ELSE :p_orgid END)
                  AND a.rundate BETWEEN to_date('01-'||to_char(add_months(TRUNC(TO_DATE(sysdate,'DD-MON-RRRR')) +1, -2),'MON-RRRR'),'DD-MON-RRRR')  AND SYSDATE
---AND a.process = (CASE WHEN :p_process IS NULL THEN a.process ELSE :p_process END)
          ) ab;
          
          
   TYPE postship_list IS TABLE OF xxhil_wrmtd_tab%ROWTYPE;

   postship_trns                 postship_list;
   v_err_count                   NUMBER;
BEGIN

delete from xxhil_wrmtd_tab;


      OPEN cur_postship_line;

      FETCH cur_postship_line
      BULK COLLECT INTO postship_trns;

      FORALL i IN postship_trns.FIRST .. postship_trns.LAST SAVE EXCEPTIONS
         INSERT INTO xxhil_wrmtd_tab
         VALUES      postship_trns (i);

      CLOSE cur_postship_line;

      COMMIT;
END;
/




---- INSERING DATA INTO TABLE WITH THE HELP OF CUROSR