An associative array is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).

The data type of index can be either a string type (VARCHAR) or INTEGER. Indexes are stored in sort order, not creation order.

For example, defines a type of associative array indexed by string, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values (in sort order, not creation order).

DECLARE
  -- Associative array indexed by integer:
  TYPE population IS TABLE OF varchar  -- Associative array type
    INDEX BY integer;                  -- indexed by string

  city_population  population;        -- Associative array variable
  i                integer;
BEGIN
  -- Add elements (key-value pairs) to associative array:
  city_population('2')  := 'Smallville';
  city_population('5') := 'Midland';
  city_population('3')  := 'Megalopolis';

  FOR i IN 1..6 LOOP
      IF city_population.EXISTS(i) THEN
          dbms_output.put_line ('city_population(' || i || ')' || ' exists');
      ELSE
          dbms_output.put_line ('city_population(' || i || ')' || ' does not exist');
      END IF;
  END LOOP;

END;
/

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.

When you retrieve a nested table value from the database into a PL/oraSQL nested table variable, PL/oraSQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.

Example: Nested Table of Local Type

DECLARE
TYPE Roster IS TABLE OF VARCHAR(15);  -- nested table type

-- nested table variable initialized with constructor: 
names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial Values:');
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('------------------');
    names2(5) := 'P Perez';  -- add one element
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;
END;
/

Example: Nested Table of Standalone Type

CREATE TYPE names2 IS TABLE OF VARCHAR(15);

DECLARE
  nt names2 ;
BEGIN
  nt(1) = 'D Caruso';
  nt(2) = 'J Hamil';
  nt(3) = 'R Singh';
  DBMS_OUTPUT.PUT_LINE(nt(2));
END;
/

A collection method is a PL/oraSQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain.

Table 42.1 shows collection methods.

The basic syntax of a collection method invocation is:

collection_name.method