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 above 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.