How to do a bulk collect into an associative array : Table of Type « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Table of Type

How to do a bulk collect into an associative array

   
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> CREATE TABLE myTable
  2  (id                NUMBER              NOT NULL
  3  ,CONSTRAINT id_pk  PRIMARY KEY (id));

Table created.

SQL>
SQL> 
SQL> DECLARE
  2    
  3    TYPE number_table IS TABLE OF myTable.id%TYPE INDEX BY BINARY_INTEGER;
  4
  5    
  6    number_list NUMBER_TABLE;
  7
  8  BEGIN
  9
 10    
 11    FOR i IN 1..10000 LOOP
 12      
 13      number_list(i) := i;
 14
 15    END LOOP;
 16
 17    
 18    FORALL i IN 1..number_list.COUNT
 19      INSERT INTO myTable VALUES (number_list(i));
 20
 21    COMMIT;
 22
 23  END;
 24  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myTable;

Table dropped.

SQL>








Related examples in the same category

1.Create a function to convert string type variable to date type variable
2.Fetch a bulk into a table structure
3.Define a nested table type for each column
4.Extend once, outside the loop for better performance
5.A package to manage a list of employees
6.Fill table of custom type and use it in for loop to insert
7.Table of custome type indexed by BINARY_INTEGER
8.Reference type attribute through index
9.Use for loop to fill a table collection
10.Select bulk collect into
11.Use for loop to insert value to table collection and then use table collection in another insert statement
12.The EXISTS Table Attribute
13.FIRST & LAST Table Attributes
14.NEXT & PRIOR Table Attributes
15.Uses the COUNT method to display the number of rows contained in a table collection
16.How to do a bulk collect into a nested table.
17.Table collection indexed by column type