PL/SQL: Collection Types

All programming languages need arrays. Oracle PL/SQL provides three types. The following post aims to serve as a quick reference and provide some minor examples for each type.

VARRAY's

VARRAY's are the closest to the Pascal or C motion of an array. They are always defined to be of fixed length and start at index 1. VARRAY's can be used on both SQL or PL/SQL and whenever they are stored into the databse, they preserve the order of their elements.

Declaring a VARRAY can be performed as follows

    declare
      TYPE week_type IS VARRAY(7) of VARCHAR2(30);
      week_day week_type := week_type();
  

Nested Tables

Nested tables are much like VARRAY's in the sense that they are single dimensional arrays that can be used in both SQL and PL/SQL, but they do not posses the maximum length limitation. Unlike VARRAY's, nested tables are multisets which means that there is no inherent order to their elements

Both VARRAY's and nested tables must be initialized with the default collection-type constructor, meaning that all declarations of the form my_collection CollectionType must end like := collectionType().

Both VARRAY's and nested tables must be extended to accumulate space for their data. No matter wheither you define the maximum size of an array or not, array slots start with a null value. So you cannot just start assigning values to slots unless you create the appropriate space first. See the examples later on.

Nested tables like their "Associative Array" cousins that we shall discuss shortly are what Steven Feuersteinin refers to as sparse data structures. This means that elements can be removed from nested tables leaving an empty slot in the middle of the array. For a demonstration of the concept, Sse the example on nested tables later on.

Declaring a nested table can be performed as follows

    DECLARE
      TYPE string30_table IS TABLE OF Varchar2(30);
      week_day week_type := tring30_table();
  

Associative Arrays

Associative Arrays are single dimensional arrays with a unique index only available in PL/SQL code. Their index can be of any type may that be Integer or Varchar2. Associative Arrays do not need to be initialized using any default constructor. You just declare them and user them.

Declaring an associative array can be performed as follows

    DECLARE
      TYPE string30_array IS TABLE OF VARCHAR2(30)
            INDEX BY BINARY_INTEGER;
      week_day string30_array;
  

Steven Feuersteinin his excelant Oracle PL/SQL Programming 4th Edition book presents the following example regarding usage of associative arrays.

DECLARE
  TYPE list_of_names_t IS TABLE OF VARCHAR2(50)
           INDEX BY PLS_INTEGER;
  happy_family list_of_names_t;
  l_row PLS_INTEGER;
BEGIN
  happy_family(202020202) := 'Eli';
  happy_family(-15070) := 'Steven';
  happy_family(-90900) := 'Chris';
  happy_family(88) := 'Neva';

  l_row := happy_family.FIRST;
  WHILE l_row IS NOT NULL
  LOOP
    DBMS_OUTPUT.put_line( happy_family(l_row));
    l_row := happy_family.NEXT(l_row);  
  END LOOP;
END;

When dealing with VARRAY's and nested tables then the type definition may be stored at schema level. Again a small example this time from Oracle PL/SQL for Dummies . Notice the different naming styles between the two.

DECLARE 
  TYPE month_nt IS TABLE OF VARCHAR2(50);
  v_month_nt month_nt := month_nt();
  i NUMBER;
BEGIN
  v_month_nt.extend(3);
  v_month_nt(1) := 'January';
  v_month_nt(2) := 'February';
  v_month_nt(3) := 'March';

  v_month_nt.DELETE(2);
  DBMS_OUTPUT.PUT_LINE('Count ' || v_month_nt.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last ' || v_month_nt.LAST);

  i := v_month_nt.FIRST;
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_month_nt(i));
    i := v_month_nt.NEXT(i);

    IF i IS NULL THEN
      EXIT;
    END IF;

  END LOOP;
END;

The output of the previous script is ...

Count 2
Last 3
January
March

... meaning that we have an array with three slots and a gap between them. I wanted to take this example a little bit further so I changed the definition of the array type to VARRAY., making the program look like this. (Notice the standard FOR loop used for traversing a VARRAY.

DECLARE 
  TYPE month_va IS VARRAY(20) OF VARCHAR2(50);
  v_month_va month_va := month_va();
  i PLS_INTEGER;
BEGIN
  v_month_va.extend(3);
  v_month_va(1) := 'January';
  v_month_va(2) := 'February';
  v_month_va(3) := 'March';

  DBMS_OUTPUT.PUT_LINE('Count ' || v_month_va.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last ' || v_month_va.LAST);

  FOR i IN v_month_va.FIRST .. v_month_va.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_month_va(i));
  END LOOP;
END;