Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Associative Arrays in Oracle

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

Back to the Top.