Converting Merge clause with Bulk collect/FORALL in pl/sql

Question

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.

Can we convert merge clause with bulk collect where the logic remains same ? i dint find any useful resources.

I have attached my merge procedure .

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;
/

If anyone can help me to guide the syntax on how to achieve the above procedure using bulk collect will be helpful.

Answer 1

Merge is always better than forall for atomic updates.

A simplistic use case is https://ograycoding.wordpress.com/2012/10/13/oracle-merge-v-bulk-collect-and-forall/

Answer 2

I know its a bit late, but use the following for future if you haven't solved it yet

   drop table projects;

create table projects (
       proj_id integer not null primary key,
       proj_title varchar2(20)
    );

 insert into projects (proj_id, proj_title) values (1, 'Project One');

 insert into projects (proj_id, proj_title) values (2, 'Project Two');

commit;

 select *
    from projects;



 declare
       type varray_t is varray(2) of projects%rowtype;
       arr varray_t;
    begin
     with test_data as (select 2 as proj_id, 'New Project Two' as proj_title from dual
                          union all select 3 as proj_id, 'New Project Three' as proj_title from dual)
       select proj_id, proj_title
     bulk collect into arr
     from test_data;

    forall i in arr.first .. arr.last
       merge into projects
       using (select arr(i).proj_id as proj_id,
                     arr(i).proj_title as proj_title
                from dual) mrg
          on (projects.proj_id = mrg.proj_id)
        when matched then update set projects.proj_title = mrg.proj_title
        when not matched then insert (proj_id, proj_title) values (mrg.proj_id, mrg.proj_title);

    dbms_output.put_line(sql%rowcount || ' rows merged');

    commit;
 end;

    

Answer 3

I hope this will give you kind of idea. Avoid the copy and paste and check the syntax.

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;
                
                FORALL rec in data.first .. data.last
                
                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(rec).bndng_typ
                        AND   d.bndng_val = data(rec).bndng_val
                        AND   d.financial_institution_id = data(rec).financial_institution_id
                        AND   d.prdct_id = data(rec).prdct_id
                        AND   d.prdct_sub_id = data(rec).prdct_sub_id
                        AND   d.instrmnt_id = data(rec).instrmnt_id
                    )
                    inp ON (
                    updtbl.POS_NUM = data(rec).POS_NUM
                    AND updtbl.POS_TYPE = data(rec).POS_TYPE
                    AND updtbl.PRICE_COMPONENT_ID = data(rec).PRICE_COMPONENT_ID
                    AND updtbl.RPRTD_TILL_DT = data(rec).RPRTD_TILL_DT
                    )
                    WHEN NOT MATCHED THEN 
                    INSERT VALUES (
                        data(rec)                       
                    )
                     WHEN  MATCHED THEN 
                     update SET SEQ_NUM=1,
                     NET_INTRST_AMT=round(data(rec).curr_loan_bal*inp.price_component_value*v_noofDays/36000,2),
                     DM_BTID=200
                     WHERE SEQ_NUM=2;
              END LOOP;
            CLOSE dtls;
        END;
    END;
END TEST1;