ORACLE PL/SQL Associative Arrays

An example of an Associative Array in Oracle 11g.
Associative arrays give you the ability to create in memory tables of a given datatype and iterate over them. This example shows the declaration of a table of character data which is populated from a select statement on an Oracle table.



-- Assign values to an empty associative array
procedure myproc
is
  TYPE tt_vals IS TABLE OF VARCHAR2(80);

    l_vals  tt_vals;
   l_count  NUMBER := 1;
begin
  l_vals := tt_vals(10);  -- l_vals.COUNT is 1 here and l_vals.EXISTS(1) is TRUE
  for mychar in (
    select CHAR_COL from mytab;
 )
LOOP
  l_vals.extend;
  l_vals(l_count) := mychar;
  l_count := l_count+1;
END LOOP;


----

l_vals(1) gives the value of the first element in the array, l_vals(2) the second....
l_vals.exists(N) evaluates to TRUE if the Nth value exists, FALSE otherwise
l_vals.count gives count of elements in the array

Other Collection Methods for the Associative Array are -
LIMIT,
FIRST and LAST,
PRIOR and NEXT for looping,
TRIM,
DELETE.

See Oracle PL/SQL Language Reference (pdf)

Also - http://notastrophe.blogspot.com/2013/03/oracle-plsql-associative-arrays-using.html