Oracle Varrays.

Preamble

In Oracle PL/SQL Varray (an array with variable size) is an array whose number of elements can vary from zero (empty) to the declared maximum size.

To access a Varray element, use the variable_name(index) syntax:

• The lower boundary of the index is 1; the upper boundary is the current number of elements.
• The upper limit changes when the elements are added or removed, but it cannot exceed the maximum size.

When you store and extract a varray from the database, its indexes and the order of the elements remain stable.

Syntax to define and then declare a Varrays type variable in Oracle PL/SQL

TYPE type_varray IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
v_arr type_varray;

Parameters and arguments of the array

• type_varray – name of type Varray
• element_type – any PL/SQL data type, except for REF CURSOR
• size_limit is a positive integer literal representing the maximum number of elements in the array.
• v_arr – the name of a variable of the Varray type

Note:

• When defining a Varray type, you must specify its maximum size.

An example of how to use Varray in Oracle PL/SQL

DECLARE
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); -- Varray type

-- is a varray variable initialized by the constructor:

team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');

PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);

FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(i) || '.' || team(i));
END LOOP;

DBMS_OUTPUT.PUT_LINE('---');
END;

BEGIN
print_team('2001 Team:');

team(3) := 'Pierre'; -- Change the values of the two elements
team(4) := 'Yvonne';
print_team('2005 Team:');

-- Call the constructor to assign new values to the Varray variable:

team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
print_team('2009 Team:');
END;

As a result, we get:
2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
---
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
---
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
---

In this example we defined Foursome as a local Varray type, declared a team variable of this type (initialized by the constructor) and defined the print_team procedure which printed Varray. The example calls the procedure three times:

• after initializing the variable,
• after changing values of two elements separately,
• and after using the constructor to change the value of all elements.

Using Varray

Varray should be used when:

• You know the maximum number of elements.
• You access the elements in sequence.
• Since you must store or retrieve all elements simultaneously, Varray may not be practical for a large number of elements.

PL/SQL tutorial: VARRAYs in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.