Associative arrays in Oracle are similar to conventional lists in other programming languages. An associative array is an unbounded array of cells and always defined in the declarative section of a PL/SQL program. While a cell is identified by an index of number or string type, it can hold a value of a scalar data type or user-defined composite type.
The syntax to declare an associative array in a PL/SQL block is as follows:
TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL INDEX BY [INDEX DATA TYPE]
In the syntax, the [INDEX DATA TYPE]
signifies the data type of an array subscript. It can be BINARY_INTEGER
, PLS_INTEGER
, POSITIVE
, NATURAL
, SIGNTYPE
, or VARCHAR2
. The data types that are not supported as index types are RAW
, NUMBER
, LONG-RAW
, ROWID
, and CHAR
.
The [ELEMENT DATA TYPE]
can be one of the following:
PL/SQL scalar data type:
NUMBER
(along with its subtypes),VARCHAR2
(and its subtypes),DATE
,BLOB
,CLOB
, orBOOLEAN
Inferred data: The data type inherited from...