Oracle Collections

Collections are powerful structures because they enable you to develop programs that manage large sets of data in memory. You can build collections of any SQL and PL/SQL datatype. Some collections onl work in PL/SQL where others work in both SQL and PL/SQL.

There are three types of collections that Oracle uses

VArray

They are densely populated arrays (no gaps in the index sequence) and behave like traditional programming arrays. They can be accessed by either SQL or PL/SQL, at creation they have a fixed size which cannot be changed.

Varrays use sequential integers for there subscripts (index), however the subscript starts from 1 not 0.

You would use a varray when the physical size of the collection is static and the collection may be used in tables. These are like the traditional arrays in other programming languages.

Nested Tables

They are initially defined as densely populated arrays but become sparsely populated when records are deleted. They can be accessed by either SQL or PL/SQL, they can also be dynamically extended.

Nested Tables use sequential integers for there subscripts (index), however the subscript starts from 1 not 0.

You would use nested tables when the physical size is unknown due to run-time variations and when the type may be used in tables. These are like the traditional lists or bags in other programming languages.

Associative Array

They are sparsely populated arrays which means the numbering does not have to be sequential, only unique. The subscript supports both unique integers or strings, they can grow dynamically but they can only be accessed via PL/SQL.

Associative arrays use sequential or non-sequential integers or strings for there subscript.

You would associative arrays when the physical size is unknow due to run-time variations and when the type will not be used in tables. These are like the traditional sets or maps in other programming languages.

Varrays

Varrays are single dimesional structures which can be used in table, record and object definitions, they can be accessed via both SQL and PL/SQL. Varrays have to be defined, declared and inialized, also remember that the subscript starts at 1 not 0.

VArray example

-- SQL Example
create or replace type integer_varray as varray(3) of integer;

DECLARE
   varray_integer integer_varray := integer_varray(NULL,NULL,NULL);
BEGIN
   for i in 1..3
   loop
      varray_integer(i) := 10 + i;
   end loop;

   dbms_output.put_line('Varray initialized as values');
   dbms_output.put_line('----------------------------');

   for i in 1..3
   loop
      dbms_output.put('Integer Varray [' || i || ']');
      dbms_output.put_line('[' || varray_integer(i) || ']');
   end loop;
END;
/

-- PL/SQL Example
DECLARE
  -- define a varray with 5 rows
  TYPE a_char_data IS VARRAY(5) of varchar2(10);
  v_char_data a_char_data := a_char_data();         -- calls the nested table constructor
BEGIN
  v_char_data.extend;                               -- allocates space in the varray
  v_char_data(1) := 'ABC';                          -- can have up to 10 characters as stated above
  v_char_data.extend;
  v_char_data(2) := 'DEF';

  for i in v_char_data.first .. v_char_data.last
  loop
     dbms_output.put_line(v_char_data(i));
  end loop;
end;

Complex example
(using database table)

create or replace type strings as varray(3) of varchar2(30 chars);

create table individuals (
   indivdual_id          number NOT NULL,
   name                  varchar2(60) NOT NULL,
   title                 varchar2(10),
   constraint indiv_pk   primary key(individual_id)
);

create table addresses (
   address_id                integer NOT NULL,
   individual_id             integer NOT NULL,
   street_address            strings NOT NULL,
   city                      varchar2(20) NOT NULL,
   constraint addr_pk        primary key(address_id),
   constraint addr_indiv_fk  foreign key(individual_id) references individuals (individual_id)
);

insert into individuals values (individuals.nextval, 'Paul Valle', 'Mr');
insert into addresses values (1, individuals.currval, strings ('100 Merchant Place', 'Middleton'), 'Milton Keynes');

create or replace type varray_nested_table is table of varchar2(30);

-- Selecting data (using a nested table)
select nested.column_value
from addresses a, table(cast(a.street_address as varray_nested_table)) nested
where address_id = 1;

-- Updating data
update addresses
set street_address = strings('101 Merchant Place', 'Middlton')
where address_id = 1;

-- Updating a portion of data
DECLARE
   type address_type is record (
      address_id       integer,
      individual_id    integer,
      street_address   strings,
      city             varchar2(20)
   );

   address address_type;

   cursor get_street_address (address_id_in integer) is
      select * from addresses where address_id = address_id_in;
BEGIN
   open get_street_address(1);
   fetch get_street_address into address;
   close get_street_address;

   address.street_address(1) := '102 Merchant Place';

   update addresses set street_address = address.street_address where address_id = 1;
END;
/

Nested Tables

Nested Tables are single dimesional structures which can be used in table, record and object definitions, they can be accessed via both SQL and PL/SQL. Nested Tables have to be defined, declared and inialized, also remember that the subscript starts at 1 not 0. The size of a Nested table is only constrained with the available memory in the SGA.

Nested table example

-- SQL Example
create or replace type card_table as table of varchar2(5);

DECLARE
   cards card_table := card_table(null,null,null);
BEGIN
   dbms_output.put_line('Nested table initialized as nulls');
   dbms_output.put_line('---------------------------------');

   for i in 1..3
   loop
      dbms_output.put_line('Cards Varray [' || i || ']');
      dbms_output.put_line('[' || cards(i) || ']');
   end loop;

   cards(1) := 'Ace';
   cards(2) := 'Two';
   cards(3) := 'Three';

   dbms_output.put(CHR(10));
   dbms_output.put_line('Nested table initialized');
   dbms_output.put_line('---------------------------------');

   for i in 1..3
   loop
       dbms_output.put_line('Cards [' || i || ']' || '[' || cards(i) || ']');
   end loop;
END;
/

-- PL/SQL Example
DECLARE
  TYPE a_char_data IS table of varchar2(10);
  v_char_data a_char_data := a_char_data();      -- calls the nested table constructor
BEGIN
  v_char_data.extend;
  v_char_data(1) := 'ABC';                       -- can have up to 10 characters as stated above
  v_char_data.extend;
  v_char_data(2) := 'DEF';

  for i in v_char_data.first .. v_char_data.last
  loop
     dbms_output.put_line(v_char_data(i));
  end loop;
end;

Note: extend is a method that adds a new empty element to a nested table, this must be called first.

Complex Nested table example see above in the varray section

Associative Arrays

Associated Arrays are single dimesional structures which can be used in programming structures. They can only be accessed in PL/SQL, they have a different approach to Varrays and Nested tables

You cannot store an associative array directly in the database, so you need logic to store and retrieve the values in this type of collection with the database, it also cannot be use in SQL.

associative array example

-- SQL Example
NOT POSSIBLE

-- PL/SQL Example
DECLARE
   -- Define variables to traverse a string indexed associative array.
   current VARCHAR2(9 CHAR);
   element INTEGER;

   -- Define required collection data types.
   TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);.
   TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);

   -- Declare a varray.
   month MONTHS_VARRAY :=
   months_varray('January','February','March','April','May','June'
      ,'July','August','September','October','November','December');

   -- Declare empty associative array.
   calendar CALENDAR_TABLE;
BEGIN
   -- Check if calendar has no elements.
   IF calendar.COUNT = 0
   THEN
      -- Print assignment output title.
      DBMS_OUTPUT.PUT_LINE('Assignment loop:');
      DBMS_OUTPUT.PUT_LINE('----------------');
      FOR i IN month.FIRST..month.LAST
      LOOP
         calendar(month(i)) := TO_CHAR(i);
         DBMS_OUTPUT.PUT_LINE('Index ['||month(i)||'] is ['||i||']');
      END LOOP;

      -- Print assigned output title.
      DBMS_OUTPUT.PUT(CHR(10));
      DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
      DBMS_OUTPUT.PUT_LINE('---------------------');
      FOR i IN 1..calendar.COUNT
      LOOP
         IF i = 1 THEN
            -- Assign the first character index to a variable.
            current := calendar.FIRST;
            -- Use the derived index to find the next index.
            element := calendar(current);
         ELSE
            -- Check if next index value exists.
            IF calendar.NEXT(current) IS NOT NULL
            THEN
               -- Assign the character index to a variable.
               current := calendar.NEXT(current);
               -- Use the derived index to find the next index.
               element := calendar(current);
            ELSE
               -- Exit loop since last index value is read.
               EXIT;
            END IF;
         END IF;

         -- Print an indexed element from the array.
         DBMS_OUTPUT.PUT_LINE('Index ['||current||'] is ['||element||']');
      END LOOP;
   END IF;
END;
/

Collection Set Operators

Oracle 11g delivers collections set operators, they act and function like SQL set operators in select statements. They only work with varrays and nested tables because they require numeric index values. You chave to migrate associate arrays into varrays or nested tables in order to use them.

cardinality counts the number of elements in a collection, the elements do not have to be unique
empty check whether a variable is null or not null
member of checks if the left operand is a member of the collection in the right operand
multiset except lets you find the elements remaining from the first set after removing any matching elements from the second set.
multiset intersect lets you find the intersection or matching values between two sets.
multiset union perform a union all operation on two collections.
set removes duplicates from a collection and thereby creates a set of unique values
submultiset compares the left operand against the right operand to determine if the left operand is a subset of the right operand.
Examples
Requires scripts for running below CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
   retval VARCHAR2(2000);
BEGIN
   IF set_in IS NULL
   THEN
      dbms_output.put_line('Result: <Null>');
   -- ELSIF set_in IS EMPTY                         -- could use this line instead
   ELSIF set_in.COUNT = 0
   THEN
      dbms_output.put_line('Result: <Empty>');
   ELSE
      FOR i IN set_in.FIRST..set_in.LAST
      LOOP
         IF i = set_in.FIRST
         THEN
            IF set_in.COUNT = 1
            THEN
               retval := '('||set_in(i)||')';
            ELSE
               retval := '('||set_in(i);
            END IF;
         ELSIF i <> set_in.LAST THEN
            retval := retval||', '||set_in(i);
         ELSE
            retval := retval||', '||set_in(i)||')';
         END IF;
      END LOOP;
   END IF;
   RETURN retval;
END format_list;
/

cardinality

DECLARE
   a list := list(1,2,3,3,4,4);
BEGIN
   dbms_output.put_line(cardinality(a));
END;
/

-- Returns in
6                                          -- total number of elements

empty if <list> IS NOT EMPTY                     -- check to see if the list is empty
then
........
end if;
member of DECLARE
   type list is table of varchar2(10);
   n varchar2(10) := 'One';
   a list := ('One', 'Two', 'Three');
BEGIN
   if n MEMBER OF a
   then
      dbms_output.put_line('n is a member');
   end if;
END;
/
multiset except DECLARE
   a list := list(1,2,3,4);
   b list := list(4,5,6,7);
BEGIN
   dbms_output.put_line(format_list(a MULTISET EXCEPT b ));
END;
/

-- Results in
(1,2,3)                                     -- only element 4 exists in both sets , thus removes 4 from                                             -- the first set
multiset intersect DECLARE
   a list := list(1,2,3,4);
   b list := list(4,5,6,7);
BEGIN
   dbms_output.put_line(format_list(a MULTISET INTERSECT b ));
END;
/

-- Results in
(4)                                         -- only element 4 exists in both sets
multiset union

DECLARE
   a list := list(1,2,3,4);
   b list := list(4,5,6,7);
BEGIN
   dbms_output.put_line(format_list(a MULTISET UNION b ));
   -- dbms_output.put_line(format_list(a MULTISET UNION DISTINCT b ));
   -- dbms_output.put_line(format_list(SET (a MULTISET UNION b ) ) );
END;
/

-- Results in
(1,2,3,4,4,5,6,7)                           -- returns all elements incuding duplicates

Note: you can use the distinct or set keywords to eliminate duplicates

set DECLARE
   a list := list(1,2,3,3,4,4,5,6,6,7);
BEGIN
   dbms_output.put_line(format_list(SET(A));
END;
/

-- Results in
(1,2,3,4,5,6,7)                           -- returns all elements excuding duplicates
submultiset

DECLARE
   a list := list(1,2,3,4);
   b list := list(1,2,3,4,5);
   c list := list(1,2,3,3,4,4);
BEGIN
   if a SUBMULTISET c
   then
      dbms_output.put_line('[a] is a subset of [c]');
   end if;

   if NOT a SUBMULTISET c
   then
      dbms_output.put_line('[b] is not a subset of [c]');
   end if;
END;
/

-- Results in
[a] is a subset of [c]
[b] is not a subset of [c]

Collection Set API

There are a number of operations that you can perform on a collection

EXISTS

check to see if an element in a collection exists

collection.exists(index)
COUNT

returns the number of elements

collection.count
LIMIT returns the maximum number of entries in a VARRAY collection.limit
FIRST returns the first element collection.first
LAST returns the last element collection.last
NEXT returns the next element in the collection collection.next
PRIOR returns previous element in a collection collection.prior
EXTEND extends the number of elements in a collection collection.extend         -- extend one element
collection.extend(n)      -- extend x elements
collection.extend(n, I)  -- extend n elements,                                      copy l elements
TRIM removes elements from the end of a collection collection.trim
collection.trim(n)
DELETE removes elements from a collection

collection.delete           -- delete one element
collection.delete(n)       -- delete x elements
collection.delete(m,n)    -- delete from m to n

The table below summaries the differences between the above collections

  Associate Array Nested Table VARRAY
index type Number or String Number Number
fixed size declared No No Yes
spare index allowed Yes Yes No
store in database No Yes Yes
access individual elements in stored collection n/a Yes No

Bulk Collect operations

Executing SQL statements in programs causes a context switch between the PL/SQL and SQL engines, too many context switches and performance degrades, to reduce context switches we use bulk collect which lets us transfers rows between SQL and PL/SQL as collections. Normally developers usually tend to use the cursor for loops method for processing data, they declare a cursor, open it, fetch from it row by row in a loop and process the row they fetched.

There are two bulk collect attributes that you can use

%BULK_EXCEPTION(i) lets you see whether or not a row encountered an error during a bulk insert, update or delete.
%BULK_ROWCOUNT(i) lets you see whether or not an element is altered by a bulk insert, update or delete.

Here are the examples

Implicit bulk collection select column1, column2 collect bulk into collection1, collection 2from table_name [where statements];
Explicit bulk collection fetch cursor_name [parameter1, etc] bulk collect into collection1, collection2 [limit rows];
Bulk collect example

DECLARE
  cursor test_cursor select object_name from t_all_objects;
  type cl_type is table of test_cursor%rowtype;
  recl cl_type ;
BEGIN
  open test_cursor;
  fetch test_cursor bulk collect into recl;
END;

Bulk collect example
(limit rows)
DECLARE
  cursor test_cursor select object_name from t_all_objects;
  type cl_type is table of test_cursor%rowtype;
  recl cl_type ;
BEGIN
  open test_cursor;
  loop
    fetch test_cursor bulk collect into recl limit 2000;
    for i in 1 .. recl.count
    loop
       null;
    end loop;
    exit when test_cursor%notfound;
  end loop;
END;

Forall statement

The forall statement basically does the same as the bulk collect statement, the construct packages up multiple write statements and sends them off to the oracle database in a single message thus reducing overall performance of the whole operation.

indices of clause allows bulk operation on a sparse collection (where elements have been deleted) by removing the reference to specific elements.

values of clause allows values of one collection to be used as index pointers to another collection.

forall example

forall <index> IN lower_bound .. upper_bound
  sql_statement;

forall example
(exception capature)
forall <index> IN lower_bound .. upper_bound save exceptions
  sql_statement;
forall example
(indices of)
forall <index> INDICES OF lower_bound .. upper_bound
  sql_statement;
forall example
(values of)
forall <index> VALUES OF lower_bound .. upper_bound
  sql_statement;