10-1. Creating and Accessing a VARRAY
Problem
You have a small, static list of elements that you initialize once and that would benefit from using in a loop body.
Solution
Place the elements into a varray (or varying array). Once initialized, a varray may be referenced by its index. Begin by declaring a datatype of varray with a fixed number of elements, and then declare the datatype of the elements. Next, declare the variable that will hold the data using the newly defined type. For example, the following code creates a varying array to hold the abbreviations for the days of the week:
DECLARE
TYPE dow_type IS VARRAY(7) OF VARCHAR2(3);
dow dow_type := dow_type ('Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'),
BEGIN
FOR i IN 1..dow.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (dow(i));
END LOOP;
END;
Results
Sun
Mon
Tue
Wed
Thu
Fri
Sat
How It Works
The type statement dow_type defines a data structure to store seven instances of VARCHAR2(3). This is sufficient space to hold the abbreviations of the seven days of the week. The dow variable is defined as a VARRAY of the dow_type defined in the previous line. That definition invokes a built-in constructor method to initialize values for each of the elements in the VARRAY.
The FOR .. LOOP traverses the dow variable starting at the first element and ending with the last. The COUNT method returns the number of elements defined in a collection; in this recipe, there are seven elements in the VARRAY, so the LOOP increments from one to seven. The DBMS_OUTPUT.PUT_LINE statement displays its value.
A VARRAY is best used when you know the size the array and it will not likely change. The VARRAY construct also allows you to initialize its values in the declaration section.