About Associative Arrays

An associative array is an unbounded set of key-value pairs. Each key is unique, and serves as the subscript of the element that holds the corresponding value. Therefore, you can access elements without knowing their positions in the array, and without traversing the array.

The data type of the key can be either PLS_INTEGER or VARCHAR2 (length).

If the key type is PLS_INTEGER, the associative array is indexed by integer, and it is dense; that is, it has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).

If the key type is VARCHAR2 (length), the associative array is indexed by string (of length characters), and it is sparse; that is, it might have gaps between elements.

When traversing a dense associative array, you do not need to beware of gaps between elements; when traversing a sparse associative array, you do.

To assign a value to an associative array element, you can use an assignment operator:

array_name(key) := value

If key is not in the array, the assignment statement adds the key-value pair to the array. Otherwise, the statement changes the value of array_name(key) to value.

Associative arrays are useful for storing data temporarily. They do not use the disk space or network operations that tables require. However, because associative arrays are intended for temporary storage, you cannot manipulate them with DML statements or use SELECT INTO statements to assign their values to variables.

If you declare an associative array in a package, and assign values to the variable in the package body, then the associative array exists for the life of the database session. Otherwise, it exists for the life of the subprogram in which you declare it.