Simplify PL/SQL INDEX BY tables using text subscripts

Associative arrays can simplify the lookup of temporary data in PL/SQL programs because text values can be used directly as subscripts to locate array elements. Learn how to apply associative array techniques in your development environment.

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:

region_counts  COUNTERS_TYPE;

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;
   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.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

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.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Editor's Picks

Free Newsletters, In your Inbox