I can never remember how to initialise PL/SQL associative arrays and thought I’d write a short post about it. This is primarily based on an article on Oracle’s Ask Tom site, plus a little extra detail from Steven Feuerstein. Associative arrays were previously known as index-by tables, by the way.
Associative arrays before 18c
Prior to Oracle 18c, you had to initialise an associative array in a slightly cumbersome way, like so:
DECLARE TYPE array_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; v_the_array array_t; v_index PLS_INTEGER; BEGIN v_the_array(1) := 'one'; v_the_array(2) := 'two'; v_the_array(3) := 'three'; v_the_array(9) := 'nine'; v_index := v_the_array.first; WHILE ( v_index IS NOT NULL ) LOOP dbms_output.put_line('v_the_array(' || v_index || '): ' || v_the_array(v_index)); v_index := v_the_array.next(v_index); END LOOP; END; /
There are slight variations to the theme as explained in example 5-3 of the Oracle Database PL/SQL language reference you might find interesting. You end up having to repeat yourself a lot, as you can see immediately below the BEGIN keyword.
Oracle 18c simplifies this task
With 18c the syntax reminds me a little of Perl, as in you define the type first; in the next step you add a variable to your anonymous code block, which you initialise in the same step:
DECLARE TYPE array_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; -- this is new for 18c, see Steven Feuerstein's article here: -- https://blogs.oracle.com/oraclemagazine/easy-initializing-for-records-and-arrays v_the_array array_t := array_t( 1 => 'one', 2 => 'two', 3 => 'three', -- note gap here ;) 9 => 'nine'); v_index PLS_INTEGER; BEGIN v_index := v_the_array.first; WHILE ( v_index IS NOT NULL ) LOOP dbms_output.put_line('v_the_array(' || v_index || '): ' || v_the_array(v_index)); v_index := v_the_array.next(v_index); END LOOP; END; /
This way you can define the array in a much nicer looking way and with less code. I also find it more readable.
You are of course not limited to using PLS_INTEGER for indexing the array, you can index differently:
DECLARE TYPE capitals_t IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100); v_capitals capitals_t := capitals_t( 'France' => 'Paris', 'Belgium' => 'Brussels', 'Austria' => 'Vienna'); v_index VARCHAR2(100); BEGIN v_index := v_capitals.first; WHILE ( v_index IS NOT NULL ) LOOP dbms_output.put_line('The capital of ' || v_index || ' is ' || v_capitals(v_index)); v_index := v_capitals.next(v_index); END LOOP; END; /
Once I wrapped my head around this it all made sense, and I think I’ll use this syntax from now on.