Associative arrays

Associative arrays are analogous to conventional arrays or lists which can be defined within a PL/SQL program only. Neither the array structure nor the data can be stored in the database. It can hold the elements of a similar type in a key-value structure without any upper bound to the array. Each cell of the array is distinguished by its subscript, index, or cell number. The index can be a number or a string.

Associative arrays were first introduced in Oracle 7 release as PL/SQL tables to signify its usage within the scope of a PL/SQL block. Oracle 8 release identified the PL/SQL table as Index by table due to its structure as an index-value pair. Oracle 10g release recognized the behavior of index by tables as arrays so as to rename it as associative arrays due to association of an index with an array.

The following diagram explains the physical lookup structure of an associative array:

Associative arrays follow the following syntax for declaration in a PL/SQL declare block:

TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL INDEX BY [INDEX DATA TYPE]

In the preceding syntax, the index type signifies the data type of the array subscript. RAW, NUMBER,LONG-RAW, ROWID, and CHAR are the unsupported index data types. The suited index types areBINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL, SIGNTYPE, or VARCHAR2.

The element’s 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, or BOOLEAN
• Inferred data: The data type inherited from a table column, cursor expression or predefined package variable
• User-defined type: A user defined object type or collection type

For illustration, the following are the valid conditions of the associative array in a PL/SQL block:

[sourcecode language=”sql”]
/*Array of CLOB data*/
TYPE clob_t IS TABLE OF CLOB
INDEX BY PLS_INTEGER;
/*Array of employee ids indexed by the employee names*/
TYPE empno_t IS TABLE OF employees.empno%TYPE NOT NULL
INDEX BY employees.ename%type;
[/sourcecode]

/*Array of CLOB data*/ TYPE clob_t IS TABLE OF CLOB INDEX BY PLS_INTEGER; /*Array of employee ids indexed by the employee names*/ TYPE empno_t IS TABLE OF employees.empno%TYPE NOT NULL INDEX BY employees.ename%type;

The following PL/SQL program declares an associative array type in a PL/ SQL block. Note that the subscript of the array is of a string type and it stores the number of days in a quarter. This code demonstrates the declaration of an array and assignment of the element in each cell and printing them. Note that the program uses the FIRST and NEXT collection methods to display the array elements. The collection methods would be covered in detail in the PL/SQL collection methods section:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a collection type associative array and its variable*/
TYPE string_asc_arr_t IS TABLE OF NUMBER
INDEX BY VARCHAR2(10);
l_str string_asc_arr_t;
l_idx VARCHAR2(50);
BEGIN
/*Assign the total count of days in each quarter against each cell*/
l_str (‘JAN-MAR’) := 90;
l_str (‘APR-JUN’) := 91;
l_str (‘JUL-SEP’) := 92;
l_str (‘OCT-DEC’) := 93;
l_idx := l_str.FIRST;
WHILE (l_idx IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE(‘Value at index ‘||l_idx||’ is ‘||l_str(l_
idx));
l_idx := l_str.NEXT(l_idx);
END LOOP;
END;
/

Value at index APR-JUN is 91
Value at index JAN-MAR is 90
Value at index JUL-SEP is 92
Value at index OCT-DEC is 93

PL/SQL procedure successfully completed.
[/sourcecode]

In the preceding block, note the string indexed array. A string indexed array considerably improves the performance by using indexed organization of array values. In the last block, we noticed the explicit assignment of data.

In the following program, we will try to populate the array automatically in the program. The following PL/SQL block declares an associative array to hold the ASCII values of number 1 to 100:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL Block*/
DECLARE
/*Declare an array of string indexed by numeric subscripts*/
TYPE ASCII_VALUE_T IS TABLE OF VARCHAR2(12)
INDEX BY PLS_INTEGER;
L_GET_ASCII ASCII_VALUE_T;
BEGIN
/*Insert the values through a FOR loop*/
FOR I IN 1..100
LOOP
L_GET_ASCII(I) := ASCII(I);
END LOOP;
/*Display the values randomly*/
DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(5));
DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(15));
DBMS_OUTPUT.PUT_LINE(L_GET_ASCII(75));
END;
/

53
49
55

PL/SQL procedure successfully completed.
[/sourcecode]

The salient features of associative arrays are as follows:

• An associative array can exist as a sparse or empty collection
• Being a non-persistent collection, it cannot participate in DML transactions
• It can be passed as arguments to other local subprograms within the same block
• Sorting of an associative array depends on the NLS_SORT parameter
• An associative array declared in package specification behaves as a session-persistent array