Associative arrays, formerly called INDEX BY tables, have been a feature of the PL/SQL language since version 2. They are sparse arrays, meaning that only the elements you load into them are stored. No space is reserved for in-between index values. One of their original limitations, however, was that subscripts (i.e., the "index" of INDEX BY) must be integers.
Oracle 9i Release 2 expanded the feature to allow text strings as subscripts. This makes associative arrays more like hash tables in other languages, such as Perl.
To indicate that a text string will be the index value, simply use INDEX BY VARCHAR2 (complete with maximum length) instead of the traditional INDEX BY BINARY_INTEGER. Here is an example:
TYPE counters_type IS TABLE OF BINARY_INTEGER
INDEX BY VARCHAR2(20);
To process individual elements of the associative array, use a VARCHAR2 variable or text literal as a subscript. The statement below updates one of the region_counts elements:
region_counts('Southwest') := region_counts('Southwest') + 1;
To loop through the elements of an associative array, use the collection methods .FIRST, .NEXT('key'), .PRIOR('key'), and .LAST as shown below (assume v_sub was previously declared as VARCHAR2):
v_grand_total := 0;
v_sub := region_counts.FIRST;
WHILE v_sub IS NOT NULL LOOP
v_grand_total := v_grand_total + region_counts(v_sub);
v_sub := region_counts.NEXT(v_sub);
This ability to use text keys directly can simplify your code. In previous versions, programs might use a two-column INDEX BY array: One column to store region names, and another to store total quantities. To update the counters, the code would first search the first column for the region code. Once the proper subscript was found, it would update the matching entry in the totals column.
Listing A shows the above associative array techniques. It declares an array type called counters_type, and then builds an instance of that type called region_counts. Data is added, updated, and processed sequentially all using text as subscripts.
Even though an associative array can use collection methods, it cannot be stored in the database as a column like a nested table can. Keep this in mind when deciding which collection type to use for temporary storage in your PL/SQL programs.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.