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