Using varrays v16

Suggest edits

A varray or variable-size array is a type of collection that associates a positive integer with a value. In many respects, it's similar to a nested table.

Varray overview

A varray has the following characteristics:

  • You must define a varray type with a maximum size limit. After you define the varray type, you can declare varray variables of that varray type. Data manipulation occurs using the varray variable, also known simply as a varray. The number of elements in the varray can't exceed the maximum size limit set in the varray type definition.
  • When you declare a varray variable, the varray at first is a null collection. You must initialize the null varray with a constructor. You can also initialize the varray by using an assignment statement where the right-hand side of the assignment is an initialized varray of the same type.
  • The key is a positive integer.
  • The constructor sets the number of elements in the varray, which must not exceed the maximum size limit. The EXTEND method can add elements to the varray up to the maximum size limit. For details, see Collection methods.
  • Unlike a nested table, a varray cannot be sparse. There are no gaps when assigning values to keys.
  • An attempt to reference a varray element beyond its initialized or extended size but within the maximum size limit results in a SUBSCRIPT_BEYOND_COUNT exception.
  • An attempt to reference a varray element beyond the maximum size limit or extend a varray beyond the maximum size limit results in a SUBSCRIPT_OUTSIDE_LIMIT exception.

Defining a varray type

The TYPE IS VARRAY statement is used to define a varray type in the declaration section of an SPL program:

TYPE <varraytype> IS { VARRAY | VARYING ARRAY }(<maxsize>)
  OF { <datatype> | <objtype> };

Where:

varraytype is an identifier assigned to the varray type.

datatype is a scalar data type such as VARCHAR2 or NUMBER.

maxsize is the maximum number of elements permitted in varrays of that type.

objtype is a previously defined object type.

You can use the CREATE TYPE command to define a varray type that's available to all SPL programs in the database. To make use of the varray, you must declare a variable of that varray type. The following is the syntax for declaring a varray variable:

<varray> <varraytype>

Where:

varray is an identifier assigned to the varray.

varraytype is the identifier of a previously defined varray type.

Initializing a varray

Initialize a varray using the varray type’s constructor:

<varraytype> ([ { <expr1> | NULL } [, { <expr2> | NULL } ]
  [, ...] ])

Where:

varraytype is the identifier of the varray type’s constructor, which has the same name as the varray type.

expr1, expr2, … are expressions that are type-compatible with the element type of the varray. If you specify NULL, the corresponding element is set to null. If the parameter list is empty, then an empty varray is returned, which means there are no elements in the varray. If the varray is defined from an object type, then exprn must return an object of that object type. The object can be the return value of a function or the return value of the object type’s constructor. The object can also be an element of another varray of the same varray type.

If you apply a collection method other than EXISTS to an uninitialized varray, a COLLECTION_IS_NULL exception is thrown. For details, see Collection methods.

The following is an example of a constructor for a varray:

DECLARE
    TYPE varray_typ IS VARRAY(2) OF CHAR(1);
    v_varray        varray_typ := varray_typ('A','B');

Referencing an element of the varray

Reference an element of the varray using this syntax:

<varray>(<n>)[<.element> ]

Where:

varray is the identifier of a previously declared varray.

n is a positive integer.

If the varray type of varray is defined from an object type, then [.element ] must reference an attribute in the object type from which the varray type is defined. Alternatively, you cam reference the entire object by omitting [.element ].

This example shows a varray where it is known that there are four elements:

DECLARE
    TYPE dname_varray_typ IS VARRAY(4) OF VARCHAR2(14);
    dname_varray    dname_varray_typ;
    CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dname_varray := dname_varray_typ(NULL, NULL, NULL, NULL);
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dname_varray(i) := r_dept.dname;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DNAME');
    DBMS_OUTPUT.PUT_LINE('----------');
    FOR j IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE(dname_varray(j));
    END LOOP;
END;

The following is the output from this example:

Output
DNAME
----------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
← Prev
Working with nested tables
↑ Up
Working with collection types
Next →
Working with collections