howto traverse an associative array

From: bonminh lam <hansmayer1962_at_hotmail.com>
Date: 19 Dec 2003 06:56:14 -0800
Message-ID: <3c6b1bcf.0312190656.64bb9831@posting.google.com>

Hello,

since Oracle 9i, it is possible to use associativ array (or index by table) as in the following example taken from Oracle's PL/SQL User's Guide:

SQL> DECLARE
  2 TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

  3         country_population population_type;
  4         continent_population population_type;
  5         howmany NUMBER;
  6         which VARCHAR2(64);
  7  BEGIN
  8         country_population('Greenland') := 100000;
  9         country_population('Iceland') := 750000;
 10         howmany := country_population('Greenland');
 11  
 12         continent_population('Australia') := 30000000;
 13         continent_population('Antarctica') := 1000; -- Creates new
entry
 14         continent_population('Antarctica') := 1001; -- Replaces
previous value
 15         which := continent_population.FIRST; -- Returns

'Antarctica'
 16 dbms_output.put_line('which :'||which);

 17       -- as that comes first alphabetically.
 18         which := continent_population.LAST; -- Returns 'Australia'
 19   dbms_output.put_line('which :'||which);
 20         howmany :=
continent_population(continent_population.LAST);
 21       -- Returns the value corresponding to the last key, in this
 22       -- case the population of Australia.

 23 END;
 24 /
which :Antarctica
which :Australia

PL/SQL procedure successfully completed.

My question is how to traverse or loop through for example the table variable country_population. Since the first und last predicate returns a varchar2 value, the syntax like this "for i in foo.first .. foo.last loop" does not work.

I guess associative array is not designed to be used this way, but it would still be a nice-to-have thing.

Anyone knows a solution?

Bon-Minh Lam Received on Fri Dec 19 2003 - 08:56:14 CST