title: Converting Merge clause with Bulk collect/FORALL in pl/sql
body: <p>I wrote a procedure where the data gets updated/inserted simultaneously to the destination table from source table. The procedure is working fine for less no of records, but when i try to execute more records its taking more time to perform the operation.</p>
  <p>Can we convert merge clause with bulk collect where the logic remains same ? i dint find any useful resources.</p>
  <p>I have attached my merge procedure .</p>
  <pre><code>create or replace PROCEDURE TEST1 (
      p_array_size IN NUMBER
  ) IS
  
      CURSOR dtls IS SELECT DISTINCT
          account_num
          
                          FROM
          table1
                          WHERE
          rprtd_till_dt = (
              SELECT
                  dt - 1
              FROM
                  dates
              WHERE
                  id = 'odc'
          );
         
  
      TYPE data_tbl IS TABLE OF dtls%rowtype;
      data data_tbl;
      
      BEGIN 
          DECLARE
          v_noofDays NUMBER:=0;
          currentDt DATE;
          BEGIN 
              SELECT dt INTO  currentDt FROM dates WHERE id = 'odc';        
          
          BEGIN
              OPEN dtls;
              LOOP
                  FETCH dtls BULK COLLECT INTO data LIMIT p_array_size;
                  EXIT WHEN data.COUNT = 0;
          
                  FOR i IN 1..data.COUNT         
                  LOOP
                      IF(TRUNC(data(i).creation_dt,'MM') = TRUNC(currentDt,'MM')) THEN
                          v_noofDays := currentDt - 1 - data(i).creation_dt;
                      ELSE
                          v_noofDays := currentDt  - TRUNC(currentDt,'MM');
                      END IF;
                      MERGE INTO table1 updtbl USING ( SELECT
                          d.*
                                                                        FROM
                          table2 d,
                          (
                              SELECT
                                  b.prdct_id,
                                  
                              FROM
                                  table3 a,
                                  table2 b
                              WHERE
                                  a.ir_id = b.ir_id
                                  AND   a.price_component_id = b.price_component_id
                                  AND   a.financial_institution_id = b.financial_institution_id
                              GROUP BY
                                  b.prdct_id,
                                  
                          ) e
                          WHERE
                          d.prdct_id = e.prdct_id
                          AND   d.bndng_typ = data(i).bndng_typ
                          AND   d.bndng_val = data(i).bndng_val
                          AND   d.financial_institution_id = data(i).financial_institution_id
                          AND   d.prdct_id = data(i).prdct_id
                          AND   d.prdct_sub_id = data(i).prdct_sub_id
                          AND   d.instrmnt_id = data(i).instrmnt_id
                      )
                      inp ON (
                      updtbl.POS_NUM = data(i).POS_NUM
                      AND updtbl.POS_TYPE = data(i).POS_TYPE
                      AND updtbl.PRICE_COMPONENT_ID = inp.PRICE_COMPONENT_ID
                      AND updtbl.RPRTD_TILL_DT = data(i).RPRTD_TILL_DT
                      )
          
                      WHEN NOT MATCHED THEN 
                      INSERT VALUES (
                          data(i).loan_account_num,
                          
                          inp.ir_id,
                          inp.price_component_id,
                         
                      )
                       WHEN  MATCHED THEN 
                       update SET SEQ_NUM=1,
                       NET_INTRST_AMT=round(data(i).curr_loan_bal*inp.price_component_value*v_noofDays/36000,2),
                      DM_BTID=200
                      WHERE SEQ_NUM=2;
                      COMMIT; 
                  END LOOP;
              END LOOP;
              CLOSE dtls;
          END;
      END;
  END TEST1;
  /
  </code></pre>
  <p>If anyone can help me to guide the syntax on how to achieve the above procedure using bulk collect will be helpful.</p>