Features of associative arrays

The associative array data type is used to represent associative arrays. It has many features which contribute to its utility.

The associative array data type supports the following associative array properties:
  • No predefined cardinality is specified for associative arrays. This enables you to continue adding elements to the array without concern for a maximum size which is useful if you do not know in advance how many elements will constitute a set.
  • The array index value can be a non integer data type. VARCHAR and INTEGER are supported index values for the associative array index.
  • Index values do not have to be contiguous. In contrast to a conventional array which is indexed by position, an associative array is an array that is indexed by values of another data type and there are not necessarily index elements for all possible index values between the lowest and highest. This is useful if for example you want to create a set that stores names and phone numbers. Pairs of data can be added to the set in any order and be sorted using which ever data item in the pair is defined as the index.
  • The elements in an associative array are sorted in ascending order of index values. The insertion order of elements does not matter.
  • Associative array data can be accessed and set using direct references or by using a set of available scalar functions.
  • Associative arrays are supported in SQL PL contexts.
  • Associative arrays can be used to manage and pass sets of values of the same kind in the form of a collection instead of having to:
    • Reduce the data to scalar values and use one-element-at-a-time processing which can cause network traffic problems.
    • Use cursors passed as parameters.
    • Reduce the data into scalar values and reconstitute them as a set using a VALUES clause.