Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...