Oracle has provided us with collections since Oracle 7. It is one of the most renamed features in the Oracle database. In Oracle 7 they were called ‘PL/SQL Tables’. In Oracle 8 they were renamed to ‘Index By Tables’ and since Oracle 9 they are called ‘Associative Arrays’. If you index by an integer value then you can read the values by using an integer index variable. If the array is dense, that means all the indexes, from the first to the last, have a value associated to them you can even use a numeric for loop to get to the data. But what if you index by a string value, which is a possibility since Oracle 9i.

The solution is actually rather simple. Using a simple loop. First of all create an index variable of the same type you use to index the collection. It would be kind of silly to declare the collection using a varchar2(10) and then declare an index variable as a varchar2(5). If an index value would be ‘Patrick’ then this wouldn’t fit in the index variable resulting in a error.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Then assign the index variable the value of the first index used. Now start the loop and do the stuff you want with the collection. At the end of the loop assign the next index value to the variable. If you try to read past the end of the collection, then the value of the index will be NULL, which is a great marker to exit the loop.

If you put the exit clause as the first statement in the loop, then there is no reason to check for any items present in the collection. If you want to check how many items are present, then use <collection>.count.

I have created a small script to demonstrate how it can be done:

declare
  subtype index_t is varchar2(10);
  type  birthdates_tt is table of date index by index_t;
  l_birthdates birthdates_tt;
  l_indx index_t;
begin
  -- Fill the table with the birthdates
  l_birthdates('Patrick') := to_date('29-12-1972','DD-MM-YYYY');
  l_birthdates('Dana') := to_date('06-03-1976','DD-MM-YYYY');
  l_birthdates('Quinty') := to_date('18-12-1998','DD-MM-YYYY');
  l_birthdates('Kayleigh') := to_date('19-11-2000','DD-MM-YYYY');
  l_birthdates('Mitchell') := to_date('23-06-2003','DD-MM-YYYY');

  -- make index variable the same as the first from the list
  l_indx := l_birthdates.first;
  -- start a simple loop
  loop
    -- exit the loop when we passed the end
    exit when l_indx is null;
    -- write the value of the index and the value at index in the AA to screen
    dbms_output.put_line(l_indx ||' => '||l_birthdates(l_indx));
    -- select the next index value from the list
    l_indx := l_birthdates.next(l_indx);
  end loop;
end;
/

I hope this information helps in leveraging the power of collections. That is no need to know the index values upfront, because you can traverse the collection at runtime where you don’t know the values of the indexes used. Some of this behavior and more about collections will be part of my presentation about Optimizing SQL with Collections at OPP2009.