Using VARCHAR2 Associative Arrays

In Oracle9i Release 2, Oracle has finally "loosened up" on the way we can define and manipulate PL/SQL-specific collections, now called associative arrays. Specifically, we can "index by" strings in addition to integer values (i.e., row numbers). This gives us significant additional flexibility. Let's look at some examples and explore applications of this new feature.

Here is a block of code that demonstrates the basics:

/* File on web: assoc_array.sql */DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); country_population population_type; continent_population population_type; howmany NUMBER; limit VARCHAR2(64);BEGIN country_population('Greenland') := 100000; country_population('Iceland') := 750000; howmany := country_population('Greenland'); continent_population('Australia') := 30000000; continent_population('Antarctica') := 1000; -- Creates new entry continent_population('Antarctica') := 1001; -- Replaces previous value limit := continent_population.FIRST; DBMS_OUTPUT.PUT_LINE (limit); DBMS_OUTPUT.PUT_LINE (continent_population(limit)); limit := continent_population.LAST; DBMS_OUTPUT.PUT_LINE (limit); DBMS_OUTPUT.PUT_LINE (continent_population(limit));END;

Here is the output from the script:

Antarctica 1001 Australia 30000000

Recall that with this type of associative array, the values returned by calls to the FIRST, LAST, PRIOR, and NEXT methods are strings and not numbers. Also note that you cannot use %TYPE to declare the associative array type. You must use a literal, hardcoded declaration.

So why would you want to index by string instead of number? Suppose that you need to do some heavy processing of employee information in your program. You need to go back and forth over the set of selected employees, searching by the employee ID number, last name, and Social Security number (or appropriate national identification number for non-U.S. countries).

DECLARE TYPE name_t IS TABLE OF employee%ROWTYPE INDEX BY VARCHAR2(100); TYPE id_t IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER; by_name name_t; by_ssn name_t; by_id id_t; ceo_name employee.last_name%TYPE := 'ELLISON'; PROCEDURE load_arrays IS BEGIN FOR rec IN (SELECT * FROM employee) LOOP -- Load up all three arrays in single pass to database table. by_name (rec.last_name) := rec; by_ssn (rec.ssn) := rec; by_id (rec.employee_id) := rec; END LOOP; END;BEGIN load_arrays; -- Now I can retrieve information by name or ID: IF by_name (ceo_name).salary > by_id (7645).salary THEN make_adjustment (by_name); END IF;END;

As you can see in the preceding example, it doesn't take a whole lot of code to build multiple, highly efficient entry points into cached data transferred from a relational table. Still, to make it even easier for you to implement these techniques in your application, I have built a utility, which you will find in the genaa.sp file on the O'Reilly site, that will actually generate a package to implement caching for the specified relational table. It populates a collection based on the integer primary key and another collection for each unique index defined on the table (indexed by BINARY_INTEGER or VARCHAR2, depending on the type(s) of the column(s) in the index).

Finally, the file, summer_reading.pkg, offers another example of the use of VARCHAR2-indexed associative arrays to manipulate lists of information within a PL/SQL program.