Associative Arrays INDEX BY Overview - PolarDB for Oracle

An associative array maps unique keys to values. Keys can be integers (BINARY_INTEGER or PLS_INTEGER) or strings (VARCHAR2).

Key characteristics

Define the array type before declaring array variables. Data manipulation is done through array variables.
Declaring an array variable creates an empty associative array. Assign values to populate it.
Integer keys (BINARY_INTEGER or PLS_INTEGER) can be negative, positive, or zero.
String keys (VARCHAR2) can hold character values up to a specified length.
No predefined limit on the number of elements. The array grows dynamically as elements are added.
Associative arrays can be sparse — gaps may exist between assigned keys.
Referencing an element that has not been assigned raises an exception.

Define an array type

Use TYPE IS TABLE OF ... INDEX BY to define an associative array type:

TYPE assoctype IS TABLE OF { datatype | rectype | objtype }
  INDEX BY { BINARY_INTEGER | PLS_INTEGER | VARCHAR2(n) };

Parameter	Description
`assoctype`	Identifier for the array type
`datatype`	Scalar data type, such as `VARCHAR2` or `NUMBER`
`rectype`	Previously defined record type
`objtype`	Previously defined object type
`n`	Maximum length of a character key

Declare an array variable

array assoctype

Parameter	Description
`array`	Identifier assigned to the associative array
`assoctype`	Identifier for a previously defined array type

Reference an element

array(n)[.field]

Parameter	Description
`array`	Identifier for a previously declared array
`n`	Key value, matching the data type in the `INDEX BY` clause
`[.field]`	Required when the array is defined from a record or object type, to reference a specific field or attribute. Omit to reference the entire record.

Collection methods

Associative arrays support the following collection methods:

Method	Description
`COUNT`	Returns the number of elements in the array
`EXISTS`	Returns `TRUE` if the element at the specified key exists
`FIRST`	Returns the key of the first element
`LAST`	Returns the key of the last element
`NEXT`	Returns the key of the element after the specified key
`PRIOR`	Returns the key of the element before the specified key

Examples

Store and display employee names

The following example reads the first 10 employee names from the emp table into an array indexed by BINARY_INTEGER, then prints them:

DECLARE
    TYPE emp_arr_typ IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i) := r_emp.ename;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j));
    END LOOP;
END;

Output:

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

Use a record type in the array definition

Define a record type with multiple fields and use it as the element type:

DECLARE
    TYPE emp_rec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR2(10)
    );
    TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i).empno := r_emp.empno;
        emp_arr(i).ename := r_emp.ename;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || '     ' ||
            emp_arr(j).ename);
    END LOOP;
END;

Output:

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER

Use %ROWTYPE in the array definition

Replace the explicit record type with emp%ROWTYPE to derive the record structure directly from the table:

DECLARE
    TYPE emp_arr_typ IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i).empno := r_emp.empno;
        emp_arr(i).ename := r_emp.ename;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || '     ' ||
            emp_arr(j).ename);
    END LOOP;
END;

The output is the same as the previous example.

Assign records directly

Instead of assigning each field individually, assign the entire record in one statement:

DECLARE
    TYPE emp_rec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR2(10)
    );
    TYPE emp_arr_typ IS TABLE OF emp_rec_typ INDEX BY BINARY_INTEGER;
    emp_arr         emp_arr_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_arr(i) := r_emp;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_arr(j).empno || '     ' ||
            emp_arr(j).ename);
    END LOOP;
END;

Use string keys

Specify VARCHAR2 in the INDEX BY clause to use string keys:

DECLARE
    TYPE job_arr_typ IS TABLE OF NUMBER INDEX BY VARCHAR2(9);
    job_arr         job_arr_typ;
BEGIN
    job_arr('ANALYST')   := 100;
    job_arr('CLERK')     := 200;
    job_arr('MANAGER')   := 300;
    job_arr('SALESMAN')  := 400;
    job_arr('PRESIDENT') := 500;
    DBMS_OUTPUT.PUT_LINE('ANALYST  : ' || job_arr('ANALYST'));
    DBMS_OUTPUT.PUT_LINE('CLERK    : ' || job_arr('CLERK'));
    DBMS_OUTPUT.PUT_LINE('MANAGER  : ' || job_arr('MANAGER'));
    DBMS_OUTPUT.PUT_LINE('SALESMAN : ' || job_arr('SALESMAN'));
    DBMS_OUTPUT.PUT_LINE('PRESIDENT: ' || job_arr('PRESIDENT'));
END;

Output:

ANALYST  : 100
CLERK    : 200
MANAGER  : 300
SALESMAN : 400
PRESIDENT: 500

Two-dimensional associative arrays

A two-dimensional associative array is a nested associative array — each value in the outer array is itself an associative array. Use two keys to access elements in the inner array. All characteristics of one-dimensional associative arrays apply.

Assign values using two-dimensional subscripts

Without two-dimensional support, you need an intermediate table to populate a nested array:

DECLARE
    type TB1 is table of varchar(10) index by varchar(10);
    type TB2 is table of TB1 index by varchar(10);
    v_table1 TB1;
    v_table2 TB2;
BEGIN
    v_table1('a') := 1;
    v_table1('b') := 2;
    v_table1('c') := 3;
    v_table2('a') := v_table1;
END;

With two-dimensional support, use two-dimensional subscripts directly — no intermediate table needed:

DECLARE
    type TB1 is table of varchar(10) index by varchar(10);
    type TB2 is table of TB1 index by varchar(10);
    v_table2 TB2;
BEGIN
    v_table2('a')('a') := 1;
    v_table2('a')('b') := 2;
    v_table2('a')('c') := 3;
END;

Iterate over a two-dimensional associative array

Use FIRST and NEXT to traverse both dimensions:

-- Examples of dim-2 associative array
DECLARE
    type type_row is table of varchar(10) index by varchar(10);
    type type_table is table of type_row index by varchar(10);
    v_table type_table;
    i varchar2(64);
    i_2 varchar2(64);
BEGIN
    v_table('a')('b') := 10;
    v_table('a')('c') := 11;
    v_table('z')('b') := 12;
    v_table('z')('c') := 13;
    i := v_table.FIRST;
    WHILE i IS NOT NULL LOOP
        i_2 := v_table(i).FIRST;
        WHILE i_2 IS NOT NULL LOOP
            dbms_output.put_line(i || ' ' || i_2 || '-' || TO_CHAR(v_table(i)(i_2)));
            i_2 := v_table(i).NEXT(i_2);
        END LOOP;
        i := v_table.NEXT(i);
    END LOOP;
END;

Output:

a b-10
a c-11
z b-12
z c-13

Collection methods for two-dimensional associative arrays

Two-dimensional associative arrays support COUNT, FIRST, LAST, NEXT, PRIOR, and EXISTS.

Call syntax:

Method type	Syntax	Example
No parameters (e.g., `COUNT`)	`array_dim2(n)`	`v_table3('a').COUNT`
One parameter (e.g., `NEXT`)	`array_dim2(n).op(n)`	`v_table3('a').NEXT(v_table3('a').FIRST)`

Where array_dim2 is the identifier for a previously declared two-dimensional associative array, n is a key value matching the INDEX BY data type, and op is the collection method name.

The following example demonstrates all supported collection methods:

DECLARE
    type TB1 is table of varchar(10) index by varchar(10);
    type TB2 is table of TB1 index by varchar(10);
    type TB3 is table of TB2 index by varchar(10);
    v_table TB2;
    v_table3 TB3;
BEGIN
    v_table('a')('b') := 10;
    v_table('b')('c') := 11;
    v_table('c')('b') := 12;
    v_table('d')('c') := 13;
    v_table3('a') := v_table;

    dbms_output.put_line(v_table3('a').COUNT);
    dbms_output.put_line(v_table3('a').FIRST);
    dbms_output.put_line(v_table3('a').LAST);
    dbms_output.put_line(v_table3('a').NEXT(v_table3('a').FIRST));
    dbms_output.put_line(v_table3('a').prior(v_table3('a').LAST));
    dbms_output.put_line(v_table3('a').exists(v_table3('a').FIRST));
END;

Output:

4
a
d
b
c
t

Limitations

Two-dimensional associative arrays have the following limitations:

Record types are not supported. An array whose element type is a record type cannot use two-dimensional subscripts. Attempting the following raises an error:

DECLARE
    TYPE emp_typ IS RECORD (
        ename       varchar(10),
        first         varchar(10)
    );
    type TB is table of emp_typ index by varchar(10);
    type TB2 is table of TB index by varchar(10);
    mytable     TB2;
    myrecord    emp_typ;
BEGIN
    mytable('a')('b') := myrecord;
END;

Error:

ERROR:  At present, only associative array without package and IS TABLE OF type is not record type support multidimensional subscripts search and assignment
CONTEXT:  compilation of SPL function "inline_code_block" near line 11

Nested tables and varrays cannot contain a two-dimensional associative array. Attempting the following raises an error:

DECLARE
    type TB1 is table of number index by number;
    type TB2 is table of TB1 index by number;
    type TB3 is table of TB2;
    v_table2 TB2;
    v_table3 TB3;
BEGIN
    v_table2(1)(1) := 1;
    v_table2(1)(2) := 2;
    v_table2(2)(3) := 3;
    v_table2(2)(4) := 4;

    v_table3 := TB3(v_table2);
END;

Error:

ERROR:  Nested table and Varray can't be assgined with a multidimensional associative array

Two-dimensional subscripts apply only to associative arrays, not to nested tables or varrays.