Oracle Associative Arrays

In Oracle PL/SQL Associative Arrays, also known as index tables, which use arbitrary numbers and rows for index values.

Associative Arrays is a set of key-value pairs where each key is unique and used to find the corresponding value in an array.

Syntax to define and then declare a variable of type Associative Arrays in Oracle PL/SQL

TYPE type_assoc_arr IS TABLE OF element_type [NOT NULL].
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
var_type type_assoc_arr;

Parameters and arguments of index tables

• type_assoc_arr – name of Associative Arrays type.
• element_type – any PL/SQL data type, except for REF CURSOR.
• key_type index type, can be numerical: PLS_INTEGER or BINARY_INTEGER, it can also be VARCHAR2 or one of its subtypes VARCHAR, STRING or LONG.
• var_type is the name of a variable of Associative Arrays type.

Note:

• RAW, LONG RAW, ROWID, CHAR and CHARACTER types are not allowed as keys for Associative Arrays.
• Associative Arrays can store data using the primary key value as an index where the key values are not consecutive.
• When you reference an element of Associative Arrays that uses a key based on VARCHAR2, you can use other types such as DATE or TIMESTAMP if they can be converted to VARCHAR2 using the TO_CHAR function.
• Do not use TO_CHAR (SYSDATE) as a key.

Consider some examples to understand how to work with an associative array in Oracle PL/SQL.

Associative Arrays, indexed by VARCHAR2

DECLARE
-- An associative array indexed by a string:

TYPE population IS TABLE OF NUMBER -- Associative array type
INDEX BY VARCHAR2(64); -- indexed by line

city_population population; -- associative array variable
i VARCHAR2(64); -- Scalar variable

BEGIN
-- Adding elements (key-value pairs) to an associative array:
city_population('village') := 2000;
city_population("Rycenter") := 750000;
city_population('Megapolis') := 1000000;

-- Change the value associated to the "Village" key:
city_population('Village') := 2001;

-- Print an associative array:

i := city_population.FIRST; -- We get the first element of the array

WHILE i IS NOT NULL LOOP
DBMS_Output.PUT_LINE
('Population ' || i || ' is equal to ' || city_population(i) ||' inhabitants);
i := city_population.NEXT(i); -- We get the following array element
END LOOP;
END;

As a result we will get:
The population of the Village is equal to 2001 inhabitants.
The population of Megapolis is equal to 1000000 inhabitants.
The population of RaiCenter is equal to 750000 inhabitants.

In this example, we defined a type of associative array indexed by a string, declared a variable of this type city_population, filled the variable with three elements, changed the value of one element and typed the values (in sorting order rather than in creation order). (FIRST and NEXT – collection methods).

Associative Arrays, indexed by an integer

In this example, the type of associative array indexed by PLS_INTEGER is defined and the function returns the associative array.

DECLARE
TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
n PLS_INTEGER := 5; -- number of multiples to sum for display
sn PLS_INTEGER := 10; -- number of multiples to sum
m PLS_INTEGER := 3; -- multiple

FUNCTION get_sum_multiples (
multiple IN PLS_INTEGER,
num IN PLS_INTEGER
) RETURN sum_multiples
IS
s sum_multiples;
BEGIN
FOR i IN 1..num LOOP
s(i) := multiple * ((i * (i + 1)) / 2); -- sum of multiples
END LOOP;
RETURN s;
END get_sum_multiples;

BEGIN
DBMS_OUTPUT.PUT_LINE (
' Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))
);
END;

As a result, we get the sum of the first five numbers divisible by 3:
Sum of the first 5 multiples of 3 is 45

Associative Arrays is suitable for:

• A relatively small search table that can be built in memory every time you call a subroutine or initialize a package that announces it.
• Transferring collections to and from the database server.

Note:

• You cannot declare the type of Arrays at schema level. Therefore, to pass the Associative Arrays variable as a parameter to a separate stored sub-program, you must declare the type of this variable in the package specification.
• This makes the type available to the called sub-program (which declares a formal parameter of this type) and the calling sub-program or anonymous block (which declares and passes a variable of this type).
  For example.

Transfer of an associative array to an autonomous subprogram

--create an associative array type aa_type in the package aa_pkg
CREATE OR REPLACE PACKAGE aa_pkg IS
TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15);
END;
--create a procedure with the parameter aa_pkg.aa_type
CREATE OR REPLACE PROCEDURE print_aa (
aa aa_pkg.aa_type
) IS
i VARCHAR2(15);
BEGIN
i := aa.FIRST;

WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE (aa(i) || ' ' || i);
i := aa.NEXT(i);
END LOOP;
END LOOP;
-- do
DECLARE
aa_var aa_pkg.aa_type;
BEGIN
aa_var('zero') := 0;
aa_var('one') := 1;
aa_var('two') := 2;
print_aa(aa_var);
END;
Result:
1 one
2 two
0 zero

Result

The most effective way to transfer collections to the database server and back is to use Associative Arrays with FORALL or BULK COLLECT operator.

Associative Arrays is designed for temporary storage of data. To make Associative Arrays permanent for the database session life, declare it in the package specification and fill it in the package body.

PL/SQL tutorial: Collection Associative Array in Oracle Database