Working with arrays in PL/SQL is not very complicated, this is a sequence of cells we can access by their index.

Sometimes, all we need is to get a value from an index we have, and this index is not a sequence number, but a number that has meaning or even text. To solve this problem we can use a regular table or temporary table, but these have some overhead and can be affected by other users or operations.

A simple example for this scenario can be coloring the world map in 4 colors (red, green, blue and yellow). In this example each country has a color and we need to know the color for a given country and go over the countries to check if they have a color.

The most effective way to do that is to use associative arrays with varchar2(30) as the index (for the country names) and varchar2(6) and the value (for the colors).

The following code shows how to work with associative arrays, add new cells, check the array length and go over the entire array.

<pre>declare
   type map_array is table of varchar2(4) index by varchar2(30);
   map map_array;
   idx varchar2(30);
begin
   -- add countries to the array
   map('France'):='blue';
   map('Italy'):='red';

   -- print the number of countries in the arrary
   dbms_output.put_line('There are '||map.count||' countries');

   -- go over the entire array
   idx:=map.first;
   while (idx is not null)
   loop
      dbms_output.put_line('Country '||idx||' is '||map(idx));
      idx:=map.next(idx);
   end loop;

   -- check if a country exists
   idx:='England';
   if (map.exists(idx))
   then
      dbms_output.put_line(idx||' has the color '||map(idx));
   else
      dbms_output.put_line(idx||' does not have a color');
   end if;
end;