Associative Arrays

An associative array (formerly called PL/SQL table or index-by table) 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 (VARCHAR2, VARCHAR, STRING, or LONG) or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

Like a database table, an associative array:

Is empty (but not null) until you populate it
Can hold an unspecified number of elements, which you can access without knowing their positions

Unlike a database table, an associative array:

Does not need disk space or network operations
Cannot be manipulated with DML statements

Topics

See Also:

Table 6-1 for a summary of associative array characteristics
"assoc_array_type_def ::=" for the syntax of an associative array type definition

Example 6-1 Associative Array Indexed by String

This 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). (FIRST and NEXT are collection methods, described in "Collection Methods".)

Live SQL:

You can view and run this example on Oracle Live SQL at Associative Array Indexed by String

DECLARE
  -- Associative array indexed by string:
  
  TYPE population IS TABLE OF NUMBER  -- Associative array type
    INDEX BY VARCHAR2(64);            --  indexed by string
  
  city_population  population;        -- Associative array variable
  i  VARCHAR2(64);                    -- Scalar variable
  
BEGIN
  -- Add elements (key-value pairs) to associative array:
 
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
 
  -- Change value associated with key 'Smallville':
 
  city_population('Smallville') := 2001;
 
  -- Print associative array:
 
  i := city_population.FIRST;  -- Get first element of array
 
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('Population of ' || i || ' is ' || city_population(i));
    i := city_population.NEXT(i);  -- Get next element of array
  END LOOP;
END;
/

Result:

Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001

Example 6-2 Function Returns Associative Array Indexed by PLS_INTEGER

This example defines a type of associative array indexed by PLS_INTEGER and a function that returns an associative array of that type.

Live SQL:

You can view and run this example on Oracle Live SQL at Function Returns Associative Array Indexed by PLS_INTEGER

DECLARE
  TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  sn PLS_INTEGER := 10;  -- number of multiples to sum
  m  PLS_INTEGER := 3;   -- multiple

  FUNCTION get_sum_multiples (
    multiple IN PLS_INTEGER,
    num      IN PLS_INTEGER
  ) RETURN sum_multiples
  IS
    s sum_multiples;
  BEGIN
    FOR i IN 1..num LOOP
      s(i) := multiple * ((i * (i + 1)) / 2);  -- sum of multiples
    END LOOP;
    RETURN s;
  END get_sum_multiples;

BEGIN
  DBMS_OUTPUT.PUT_LINE (
    'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
    TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))
  );
END;
/

Result:

Sum of the first 5 multiples of 3 is 45

Declaring Associative Array Constants

When declaring an associative array constant, you can use qualified expressions to initialize the associative array with its initial values in a compact form.

For information about constructors, see "Collection Constructors".

Example 6-3 Declaring Associative Array Constant

You can use a qualified expression indexed association aggregate to initialize a constant associative array index expression and value expression.

DECLARE
  TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  v CONSTANT My_AA := My_AA(-10=>'-ten', 0=>'zero', 1=>'one', 2=>'two', 3 => 'three', 4 => 'four', 9 => 'nine');
BEGIN
  DECLARE
    Idx PLS_INTEGER := v.FIRST();
  BEGIN
    WHILE Idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7));
      Idx := v.NEXT(Idx);
    END LOOP;
  END;
END;
/

Prior to Oracle Database Release 18c, to achieve the same result, you had to create the function for the associative array constructor. You can observe by comparing both examples that qualified expressions improve program clarity and developer productivity by being more compact.

Live SQL:

You can view and run this example on Oracle Live SQL at Declaring Associative Array Constant

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER IS
  TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  FUNCTION Init_My_AA RETURN My_AA;
END My_Types;
/
CREATE OR REPLACE PACKAGE BODY My_Types IS
  FUNCTION Init_My_AA RETURN My_AA IS
    Ret My_AA;
  BEGIN
    Ret(-10) := '-ten';
    Ret(0) := 'zero';
    Ret(1) := 'one';
    Ret(2) := 'two';
    Ret(3) := 'three';
    Ret(4) := 'four';
    Ret(9) := 'nine';
    RETURN Ret;
  END Init_My_AA;
END My_Types;
/
DECLARE
  v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();
BEGIN
  DECLARE
    Idx PLS_INTEGER := v.FIRST();
  BEGIN
    WHILE Idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7));
      Idx := v.NEXT(Idx);
    END LOOP;
  END;
END;
/

Result:

-10   -ten
0   zero
1    one
2    two
3  three
4   four
9   nine

NLS Parameter Values Affect Associative Arrays Indexed by String

National Language Support (NLS) parameters such as NLS_SORT, NLS_COMP, and NLS_DATE_FORMAT affect associative arrays indexed by string.

Topics

See Also:

Oracle Database Globalization Support Guide for information about linguistic sort parameters

Changing NLS Parameter Values After Populating Associative Arrays

The initialization parameters NLS_SORT and NLS_COMP determine the storage order of string indexes of an associative array.

If you change the value of either parameter after populating an associative array indexed by string, then the collection methods FIRST, LAST, NEXT, and PRIOR might return unexpected values or raise exceptions. If you must change these parameter values during your session, restore their original values before operating on associative arrays indexed by string.

See Also:

Collection Methods for more information about FIRST, LAST, NEXT, and PRIOR

Indexes of Data Types Other Than VARCHAR2

In the declaration of an associative array indexed by string, the string type must be VARCHAR2 or one of its subtypes.

However, you can populate the associative array with indexes of any data type that the TO_CHAR function can convert to VARCHAR2.

If your indexes have data types other than VARCHAR2 and its subtypes, ensure that these indexes remain consistent and unique if the values of initialization parameters change. For example:

Do not use TO_CHAR(SYSDATE) as an index.

If the value of NLS_DATE_FORMAT changes, then the value of (TO_CHAR(SYSDATE)) might also change.
Do not use different NVARCHAR2 indexes that might be converted to the same VARCHAR2 value.
Do not use CHAR or VARCHAR2 indexes that differ only in case, accented characters, or punctuation characters.

If the value of NLS_SORT ends in _CI (case-insensitive comparisons) or _AI (accent- and case-insensitive comparisons), then indexes that differ only in case, accented characters, or punctuation characters might be converted to the same value.

See Also:

Oracle Database SQL Language Reference for more information about TO_CHAR

Passing Associative Arrays to Remote Databases

If you pass an associative array as a parameter to a remote database, and the local and the remote databases have different NLS_SORT or NLS_COMP values, then:

The collection method FIRST, LAST, NEXT or PRIOR (described in "Collection Methods") might return unexpected values or raise exceptions.
Indexes that are unique on the local database might not be unique on the remote database, raising the predefined exception VALUE_ERROR.

Appropriate Uses for Associative Arrays

An associative array is appropriate for:

A relatively small lookup table, which can be constructed in memory each time you invoke the subprogram or initialize the package that declares it
Passing collections to and from the database server

Declare formal subprogram parameters of associative array types. With Oracle Call Interface (OCI) or an Oracle precompiler, bind the host arrays to the corresponding actual parameters. PL/SQL automatically converts between host arrays and associative arrays indexed by PLS_INTEGER.

Note:

You cannot bind an associative array indexed by VARCHAR.

Note:

You cannot declare an associative array type at schema level. Therefore, to pass an associative array variable as a parameter to a standalone subprogram, you must declare the type of that variable in a package specification. Doing so makes the type available to both the invoked subprogram (which declares a formal parameter of that type) and the invoking subprogram or anonymous block (which declares and passes the variable of that type). See Example 11-2.

Tip:

The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause. For details, see "FORALL Statement" and "BULK COLLECT Clause".

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare it in a package specification and populate it in the package body.