Restrictions on associative array data types

It is important to note the restrictions on the array data type before you use it or when troubleshooting problems with their declaration or use.

The following restrictions apply to the array data type:
  • An associative array can only be declared, created, or referenced in SQL PL contexts. The following is a list of SQL PL contexts in which this data type can be used:
    • Parameter to an SQL function that is defined in a module.
    • Parameter to an SQL function that is not defined in a module, but that has a compound SQL (compiled) statement as function body not defined in a module.
    • Return type from an SQL functions that is defined in a module.
    • Return type from an SQL function that is not defined in a module, but that has a compound SQL (compiled) statement as function body.
    • Parameter to an SQL procedure.
    • Local variable declared in an SQL function that is defined in a module.
    • Local variable declared in an SQL function that is not defined in a module, but that has a compound SQL (compiled) statement as function body.
    • Local variable declared in an SQL procedure.
    • Local variable declared in a trigger with a compound SQL (compiled) statement as trigger body.
    • Expressions in SQL statements within compound compiled (SQL) statements.
    • Expressions in SQL statements in SQL PL contexts.
    • Global variable.
    Any use outside of one of the previously listed SQL PL contexts is not valid.
  • Associative arrays cannot be the type of a table column.
  • NULL is not permitted as an index value.
  • The maximum size of an associative array is limited by system resources.
  • Associative arrays can not be input to the TRIM_ARRAY function. Associative array values cannot be stored in table columns.
  • The MAX_CARDINALITY function is supported for use with associative arrays, but always returns null because associative arrays do not have a specified maximum size.
  • The use of arrays with row type elements in autonomous routines is not supported, if any of the row fields belonging to the element type are arrays or other row types.