VARRAY for scalar data types

This task is for using Oracle style VARRAYs inside stored routines.

A VARRAY is single-dimensional collections of elements with the same data type.
A VARRAY always has a fixed number of elements and never has gaps between the elements (not sparse).

Declare a VARRAY type

TYPE array_type_name IS VARRAY(max_elements) OF element_type [NOT NULL];

• array_type_name is the type of the VARRAY.
• max_elements is the maximum number of elements allowed in the VARRAY.
• NOT NULL specifies that the element of the VARRAY of that type cannot have NULL elements. Note that a VARRAY variable can be null, or uninitialized.
• element_type is the type of elements of the VARRAY type’s variable.

Example:

DECLARE

    TYPE array_type_name IS VARRAY(10) OF INT;

BEGIN

    NULL;

END;

/

Declare a global VARRAY type

To create a VARRAY type which is accessible globally in the database, not just in PL/SQL code, one uses the following syntax:

CREATE [OR REPLACE ] TYPE type_name AS | IS

  VARRAY(max_elements) OF element_type [NOT NULL];

Global VARRAY types is out of scope of this task and will be implemented separately.

Declare and initialize VARRAY variables

Once a VARRAY type is created, a VARRAY instance of that type can be declared. The syntax is:

varray_name type_name [:= type_name(...)];

• The varray_name is the name of the VARRAY.
• The type_name is the VARRAY type.
• The type_name(...) is the constructor of the VARRAY type, which accepts a comma-separated list of elements as arguments. It has the same name as the VARRAY type.

Example:

DECLARE

    TYPE array_type_name IS VARRAY(10) OF INT;

     array_var array_type_name;

BEGIN

    NULL;

END;

/

Before using a VARRAY variable, it must be initialized. Otherwise, an error is raised:

DECLARE

    TYPE array_type_name IS VARRAY(10) OF INT;

     array_var array_type_name;

BEGIN

    IF array_var(1) = 1 THEN

      NULL;

    END IF;

END;

/

ORA-06531: Reference to uninitialized collection ORA-06512: at line 5

The following syntax initialized a VARRAY variable to an empty collection (zero elements):

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var1 array_type_name := array_type_name(); -- declare initializing

  array_var2 array_type_name;

BEGIN

   array_var2 := array_type_name(); -- assignment initializing

END;

/

The following syntax specifies elements while initializing a VARRAY:

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var1 array_type_name := array_type_name(1,2,3); -- declare initializing

  array_var2 array_type_name;

BEGIN

   array_var2 := array_type_name(1,2,3); -- assignment initializing

END;

/

A VARRAY can be assigneed to another VARRAY (all members are copied):

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var1 array_type_name := array_type_name(1,2,3); -- declare initializing

  array_var2 array_type_name;

BEGIN

   array_var2 := array_var1;

END;

/

Accessing VARRAY elements

To access an array element use the following syntax:

varray_name(n);

where n is the index of the element position between 1 and the maximum number of elements defined in the VARRAY type.

Example:

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var array_type_name := array_type_name(1,2,3);

BEGIN

  array_var(1):= array_var(1)*10;

  DBMS_OUTPUT.PUT_LINE(array_var(1));

END;

/

10

If the index is not in the range (1, initialized element count), the SUBSCRIPT_BEYOND_COUNT error is raised.

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var array_type_name := array_type_name(1,2,3);

BEGIN

  DBMS_OUTPUT.PUT_LINE(array_var(4));

END;

/

ORA-06533: Subscript beyond count ORA-06512: at line 5

If the index is greater than the number of elements in the VARRAY type declaration, the SUBSCRIPT_BEYOND_COUNT error is also raised, but with a different error text:

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var array_type_name := array_type_name(1,2,3);

BEGIN

  DBMS_OUTPUT.PUT_LINE(array_var(11));

END;

/

ORA-06532: Subscript outside of limit ORA-06512: at line 5

Adding elements

The method EXTEND adds elements to the end of a VARRAY.

• EXTEND appends one NULL element to the VARRAY
• EXTEND(n) appends n NULL elements to the VARRAY
• EXTEND(n,i) appends n copies of the i-th element to the collection.

Example:

DECLARE

  TYPE array_type_name IS VARRAY(10) OF INT;

  array_var array_type_name := array_type_name(10);

BEGIN

  array_var.EXTEND;

  array_var(2):= 20;

  array_var.EXTEND(2);

  array_var(3):= 30;

  array_var(4):= 40;

  array_var.EXTEND(2,1);

  FOR i IN 1..6

  LOOP

    DBMS_OUTPUT.PUT_LINE(array_var(i));

  END LOOP;

END;

/

10

20

30

40

10

10

Deleting elements

To delete all elements of a VARRAY, you use the DELETE method:

array_var.DELETE;

To remove one element from the end of a VARRAY, use the TRIM() method:

array_var.TRIM()

To remove n elements from the end of a VARRAY, use the TRIM method:

array_var.TRIM(n)

TODO: Describe other methods:

• EXISTS
• FIRST
• LAST
• COUNT
• LIMIT