PLSQL collections come in once you've been using PLSQL for a while and realize that there is a need for more complex or sophisticated data types such as the array, table or set types that exist in other languages. Oracle PLSQL has 3 types of collections:

associative arrays (also known as index-by tables)
VARRAY (a varying size array)
nested tables

Let's explore each of these in detail.

PLSQL Associative Arrays

Associative arrays are the equivalent of hash tables or unordered tables in other languages. They are suitable for smallish look-up tables and for passing collections of data backwards and forwards to the database.

They are implemented as pairs - the index value and the data value - and it is important to note that the index value can only be of type STRING or INTEGER.

Other properties of associative arrays type of PLSQL collections are that they are effectively unbounded (the maximum number of elements is the maximum value of a variable of type PLS_INTEGER) and they can be densely populated or sparsely populated (i.e there can be gaps in a sequence).

Let's look at an example.

First we declare the type, then we declare a variable of that type.

DECLARE

TYPE varchar_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
--have to declare a type. These type declarations define our plsql collections

first_names TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
-- the above declaration is not allowed

first_names varchar_tab; -- a plsql collection variable

BEGIN

first_names(-5) := 'Winston';
first_names(-10) := 'John';
first_names(200) := 'Brian';

DBMS_OUTPUT.PUT_LINE(first_names(-5));

END;

In the above example, the index is an integer, and we're storing strings but we could switch this around and index by string and store integers.

DECLARE
TYPE int_tab IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);

test_scores int_tab;

BEGIN

  test_scores('Winston') := 60;
  test_scores('John') := 90;
  test_scores('Brian') := 95;

DBMS_OUTPUT.PUT_LINE(test_scores('Brian'));

END;

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

Thus far we've used constants as the index but what if we don't know what the value of the index is for any elements in our PLSQL collections? What do we do then?

Simple we just use the methods FIRST, LAST and NEXT and PRIOR on our PLSQL collections. As their names imply, these functions return the value of the first element in the collection, the last element in the collection, the next element in the collection, and the previous element, respectively.

This means we can re-write our previous example as follows:

DECLARE
TYPE int_tab IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);

test_scores int_tab;

ix VARCHAR2;

BEGIN

  test_scores('Winston') := 60;
  test_scores('John') := 90;
  test_scores('Brian') := 95;

   DBMS_OUTPUT.PUT_LINE('Scores');
ix := test_scores.first;

WHILE ix <= test_scores.last LOOP
DBMS_OUTPUT.PUT_LINE(ix||':'||test_scores(ix));
   ix := test_scores.NEXT(ix);
END LOOP;

END;

This will produce the results (in this case) in alphabetical order:
  Brian:95
  John:90
Winston:60

A few other points to note:

with associative array PLSQL collections if we attempt to read from an uninitialized element of the array then an "ORA-01403: no data found" exception is automatically raised.
for an uninitialized associative array, FIRST, LAST, PRIOR and NEXT will all return NULL
we can also delete elements from an associative array or the whole array using the DELETE method

DECLARE
TYPE int_tab IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);

first_test_scores int_tab;
second_test_scores int_tab;
third_test_scores int_tab;

ix VARCHAR2;

BEGIN

  first_test_scores('Winston') := 60;
  first_test_scores('John') := 90;
  first_test_scores('Brian') := 95;
  first_test_scores('Brett') := 54;
  first_test_scores.DELETE('Brett');

second_test_scores('Winston') := 16;
  second_test_scores('John') := 28;
  second_test_scores('Brian') := 45;
  second_test_scores('Brett') := 68;
  second_test_scores('Bill') := 61;
  second_test_scores.DELETE('Brett','John');
-- delete scores from Brett through to John
--(i.e. Brett's, Brian's and John's scores)

third_test_scores('Winston') := 71;
third_test_scores('John') := 58;
  third_test_scores.DELETE; -- deletes ALL elements

   DBMS_OUTPUT.PUT_LINE('FIRST TEST SCORES');
ix := first_test_scores.first;

WHILE ix <= first_test_scores.last LOOP
DBMS_OUTPUT.PUT_LINE(ix||':'||first_test_scores(ix));
   ix := first_test_scores.NEXT(ix);
END LOOP;

   DBMS_OUTPUT.PUT_LINE('SECOND TEST SCORES');
ix := second_test_scores.first;

WHILE ix <= second_test_scores.last LOOP
DBMS_OUTPUT.PUT_LINE(ix||':'||second_test_scores(ix));
   ix := second_test_scores.NEXT(ix);
END LOOP;

   DBMS_OUTPUT.PUT_LINE('THIRD TEST SCORES');
ix := third_test_scores.first;

WHILE ix <= third_test_scores.last LOOP
DBMS_OUTPUT.PUT_LINE(ix||':'||third_test_scores(ix));
   ix := third_test_scores.NEXT(ix);
END LOOP;

END;

This will produce the following results :
FIRST TEST SCORES
  Brian:95
  John:90
Winston:60
SECOND TEST SCORES
  Bill:61
  Winston:60
THIRD TEST SCORES

If you want to learn more about PLSQL or other aspects of Oracle see our partners for instructor-led PL/SQL training in the UK and PL/SQL training in NZ .

PLSQL Collections Explained (Part 1 - associative arrays)

PLSQL Associative Arrays

Oracle Tips & Tricks to SKYROCKET Your Career!