Working with Collections in Oracle

Oracle PL/SQL provides the datatypes Table and VARRAY to allow you declare index-by tables, nested tables and variable-size arrays:

• Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
• Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

In this topic, you learn how those types let you reference and manipulate collections of data as whole objects. 

Associative Arrays (Index-By Tables)

Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique, either by using the primary key from a SQL table, or by concatenating strings together to form a unique value.

For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:

DECLARE
  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64)

BEGIN
  country_population('Greenland') := 100000;
  country_population('Iceland') := 750000;
  howmany := country_population('Greenland');

  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value
  which := continent_population.FIRST; -- Returns 'Antarctica'
-- as that comes first alphabetically.
  which := continent_population.LAST; -- Returns 'Australia'
  howmany := continent_population(continent_population.LAST);
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
END;
/

Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.

Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

Varrays

Items of type VARRAY are called varrays. A varray has a maximum size, which you must specify in its type definition. To reference an element in a varray, you use standard subscripting syntax. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, Grade(3) references the third element in varray Grades.

In the above example, the current upper bound for varray Grades is 7, but you can extend it to 8, 9, 10, and so on. Thus, a varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.