8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Associative Arrays
The index-by tables available in previous releases of Oracle have been renamed to Associative Arrays in Oracle9i Release 2. In addition to the rename Oracle have added the ability to index-by string values making them significantly more flexible. To show this lets assume we need to hold an array of country names and ISO codes. Prior to Oracle9i Release 2 if we wanted to lookup the country names based on their ISO codes we would do the following.
SET SERVEROUTPUT ON DECLARE TYPE country_type IS RECORD ( iso_code VARCHAR2(5), name VARCHAR2(50) ); TYPE country_tab IS TABLE OF country_type INDEX BY BINARY_INTEGER; t_country country_tab; BEGIN -- Populate lookup t_country(1).iso_code := 'UK'; t_country(1).name := 'United Kingdom'; t_country(2).iso_code := 'US'; t_country(2).name := 'United States of America'; t_country(3).iso_code := 'FR'; t_country(3).name := 'France'; t_country(4).iso_code := 'DE'; t_country(4).name := 'Germany'; -- Find country name for ISO code "DE" << lookup >> FOR i IN 1 .. 4 LOOP IF t_country(i).iso_code = 'DE' THEN DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country(i).name); EXIT lookup; END IF; END LOOP; END; /
In Oracle 9i Release 2 we are able to index-by a string value.
SET SERVEROUTPUT ON DECLARE TYPE country_tab IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(5); t_country country_tab; BEGIN -- Populate lookup t_country('UK') := 'United Kingdom'; t_country('US') := 'United States of America'; t_country('FR') := 'France'; t_country('DE') := 'Germany'; -- Find country name for ISO code "DE" DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country('DE')); END; /
The index values for associative arrays must be unique. If a new value is assigned using an existing key the existing value of that key is updated. They are particularly useful as quick lookups for reference data where the index value is the primary key of the original table. Associative arrays cannot be stored in tables like nested tables or varrays. Session level persistance can be achieved by defining the types and variables in a package.
For more information see:
Hope this helps. Regards Tim...