Thursday, August 25, 2016

update and insert using for all bulk collect

DECLARE
    CURSOR rec_cur IS
     SELECT row_id, x_sfdc_account_id
             FROM s_org_ext
             where x_sfdc_account_id is not null
--             and row_id  in ('1+12N+1177',
--'1+12N+1182',
--'1+12N+1185')
;

    TYPE vc1_tab_t IS TABLE OF VARCHAR2(256);
    TYPE vc2_tab_t IS TABLE OF VARCHAR2(256);

    pk_tab vc1_tab_t;
--    fk_tab vc1_tab_t;
    fill_tab VC2_TAB_T;
BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO pk_tab,  fill_tab LIMIT 1000;
        EXIT WHEN pk_tab.COUNT() = 0;

        FORALL i IN pk_tab.FIRST .. pk_tab.LAST
           UPDATE md_parties
            SET    attribute_osr = fill_tab(i)
            WHERE  orig_system_reference = pk_tab(i)
            and source_system =5;
            commit;
    END LOOP;
    CLOSE rec_cur;
END;



DECLARE
    CURSOR rec_cur IS
       SELECT *
     FROM md_party_asset_link_Bkp;

    TYPE vc1_tab_t IS TABLE OF rec_cur%rowtype;

    pk_tab vc1_tab_t;
--    fk_tab vc1_tab_t;
BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO pk_tab LIMIT 100000;
        EXIT WHEN pk_tab.COUNT() = 0;

        FORALL i IN pk_tab.FIRST .. pk_tab.LAST
           INSERT INTO md_party_asset_link
           VALUES pk_tab (i);
            commit;
    END LOOP;
    CLOSE rec_cur;
END;


/* Formatted on 9/13/2016 3:18:21 PM (QP5 v5.185.11230.41888) */
DECLARE
   TYPE prod_tab IS TABLE OF md_party_asset_link_Bkp%ROWTYPE;

   products_tab   prod_tab := prod_tab ();
   start_time     NUMBER;
   end_time       NUMBER;
BEGIN
   -- Populate a collection - 100000 rows
   SELECT *
     BULK COLLECT INTO products_tab
     FROM md_party_asset_link_Bkp;

   Start_time := DBMS_UTILITY.get_time;

   FORALL i IN products_tab.FIRST .. products_tab.LAST
      INSERT INTO md_party_asset_link
           VALUES products_tab (i);

   end_time := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.PUT_LINE ('Bulk Insert: ' || TO_CHAR (end_time - start_time));
   COMMIT;
END;

No comments:

Post a Comment