Initializing Associative Array in PL/SQL

plsql

Using PL/SQL, suppose we've declared a nested table type and its instance as follows:

type nested_tab is table of pls_integer;
tab nested_tab;.

We are then able to initialize our nested table as follows:

tab := nested_tab(1, 2, 3);.

Is there something similar we can do to initialize an associative array?

In other words, for the following associate array:

type associative_tab is table of varchar2(100) index by pls_integer;
a_tab associative_tab;

is there a shorter way than this:

a_tab(1) := 'hello';
a_tab(2) := 'world';
...
a_tab(100) := '100th string';

for intialization?

Best Answer

Updated answer for 18c and beyond

In 18c Oracle has introduced qualified expressions that can also be used to initialize an associative array. Example from the documentation:

DECLARE
  TYPE t_aa IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  v_aa1 t_aa := t_aa(1=>FALSE,
                     2=>TRUE,
                     3=>NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(1)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(2)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(3)));
END;

See also chapter Qualified Expressions for Associative Arrays from Easy Initializing for Records and Arrays by Steven Feuerstein

Original answer upto 12c

No - there is no a short-cut syntax to initialize an associative array. Unlike varrays and nested tables associative arrays do not have collection constructors.

Instead you should write your own initializer function. See an example from Declaring Associative Array Constants.

Related Question