Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Saturday, January 24, 2015

Oracle PL/SQL Bulk Collect

--> PL/SQL Normal Insert and Bulk Insert
CREATE TABLE XXAA_INSERT_TEST (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE XXAA_INSERT_TEST ADD (CONSTRAINT XXAA_INSERT_TEST_pk PRIMARY KEY (id));

ALTER TABLE XXAA_INSERT_TEST ADD (CONSTRAINT XXAA_INSERT_TEST_uk UNIQUE (code));

SET SERVEROUTPUT ON
DECLARE
  TYPE t_XXAA_INSERT_TEST_tab IS TABLE OF XXAA_INSERT_TEST%ROWTYPE;

  l_tab    t_XXAA_INSERT_TEST_tab := t_XXAA_INSERT_TEST_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size
  LOOP
    l_tab.extend;
    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE XXAA_INSERT_TEST';

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO XXAA_INSERT_TEST (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || (DBMS_UTILITY.get_time - l_start));
  EXECUTE IMMEDIATE 'TRUNCATE TABLE XXAA_INSERT_TEST';

  -- Time bulk inserts. 
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO XXAA_INSERT_TEST VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || (DBMS_UTILITY.get_time - l_start));

  COMMIT;

END;

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect