CREATE TABLE parts
(
   partnum    NUMBER,
   partname   VARCHAR2 (15)
)

CREATE OR REPLACE PACKAGE tmr
IS
   PROCEDURE start_timer;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY tmr
IS
   l_start   INTEGER;

   PROCEDURE start_timer
   IS
   BEGIN
      l_start := DBMS_UTILITY.get_cpu_time;
   END start_timer;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            CASE
               WHEN message_in IS NULL THEN 'Completed in:'
               ELSE '"' || message_in || '" completed in: '
            END
         || (DBMS_UTILITY.get_cpu_time - l_start)
         || ' cs');

      /* Reset timer */
      start_timer;
   END show_elapsed_time;
END;

DECLARE 
   PROCEDURE compare_inserting (num IN INTEGER) 
   IS 
      TYPE numtab IS TABLE OF parts.partnum%TYPE 
         INDEX BY PLS_INTEGER; 
 
      TYPE nametab IS TABLE OF parts.partname%TYPE 
         INDEX BY PLS_INTEGER; 
 
      pnums       numtab; 
      pnames      nametab; 
   BEGIN 
      FOR indx IN 1 .. num 
      LOOP 
         pnums (indx) := indx; 
         pnames (indx) := 'Part ' || TO_CHAR (indx); 
      END LOOP; 
 
      tmr.start_timer; 
 
      FORALL indx IN 1 .. num 
         INSERT INTO parts 
              VALUES (pnums (indx), pnames (indx)); 
 
      tmr.show_elapsed_time ('Inserted ' || num || ' rows'); 
 
      ROLLBACK; 
   END; 
BEGIN 
   DBMS_OUTPUT.put_line ('Associative Arrays'); 
   compare_inserting (100000); 
END;

DECLARE 
   PROCEDURE compare_inserting (num IN INTEGER) 
   IS 
      TYPE numtab IS TABLE OF parts.partnum%TYPE; 
 
      TYPE nametab IS TABLE OF parts.partname%TYPE; 
 
      pnums       numtab := numtab(); 
      pnames      nametab := nametab(); 
   BEGIN 
      pnums.extend (num); 
      pnames.extend (num); 
       
      FOR indx IN 1 .. num 
      LOOP 
         pnums (indx) := indx; 
         pnames (indx) := 'Part ' || TO_CHAR (indx); 
      END LOOP; 
 
      tmr.start_timer; 
 
      FORALL indx IN 1 .. num 
         INSERT INTO parts 
              VALUES (pnums (indx), pnames (indx)); 
 
      tmr.show_elapsed_time ('Inserted ' || num || ' rows'); 
 
      ROLLBACK; 
   END; 
BEGIN 
   DBMS_OUTPUT.put_line ('Nested Table'); 
   compare_inserting (100000); 
END;

DECLARE 
   PROCEDURE compare_inserting (num IN INTEGER) 
   IS 
      TYPE numtab IS VARRAY (100000) OF parts.partnum%TYPE; 
 
      TYPE nametab IS VARRAY (100000) OF parts.partname%TYPE; 
 
      pnums       numtab := numtab(); 
      pnames      nametab := nametab(); 
   BEGIN 
      pnums.extend (num); 
      pnames.extend (num); 
       
      FOR indx IN 1 .. num 
      LOOP 
         pnums (indx) := indx; 
         pnames (indx) := 'Part ' || TO_CHAR (indx); 
      END LOOP; 
 
      tmr.start_timer; 
 
      FORALL indx IN 1 .. num 
         INSERT INTO parts 
              VALUES (pnums (indx), pnames (indx)); 
 
      tmr.show_elapsed_time ('Inserted ' || num || ' rows'); 
 
      ROLLBACK; 
   END; 
BEGIN 
   DBMS_OUTPUT.put_line ('Varrays'); 
   compare_inserting (100000); 
END;