PL/SQL VARRAY

In Oracle PL/SQL, handling collections of data is a common requirement for many applications, whether it’s storing a list of values, managing configurations, or processing data in bulk. Among the various collection types available, VARRAY (variable-sized array) stands out as a practical option for cases where a fixed-size, ordered collection is needed. Unlike other collection types like nested tables and associative arrays, VARRAYs provide a structured way to manage data with a predetermined number of elements, making them suitable for scenarios where the data set's size is known beforehand.

In this article, we will cover everything you need to know about using VARRAYs in Oracle PL/SQL, including declaring and initializing them, accessing elements, and performing operations like adding or deleting elements.

What is a VARRAY?

A VARRAY (variable-sized array) is a one-dimensional collection that allows you to store an ordered set of elements of similar data types. It is best suited for cases where the number of elements is known and remains within a fixed range. VARRAYs are nonsparse, meaning they do not have gaps between elements, and the order of elements is always maintained. Some key characteristics of VARRAYs are:

• Fixed Size Limit: The maximum number of elements is defined when creating the VARRAY type.
• Ordered Collection: The order in which elements are added is preserved.
• Nonsparse Structure: No gaps are allowed between elements.

Different Operations on PL/SQL VARRAYS

Let us look at the different operations that can be done using the Varrays in Oracle PL/SQL.

1. Declare and Initialize VARRAY variables

To use a VARRAY in PL/SQL, you need to follow two steps: create a VARRAY type and then declare a variable of that type.

Step 1: Create a VARRAY Type

The syntax to create a VARRAY type is:

-- To Create a VARRAY type here
 TYPE SampleVARRAY IS VARRAY (3) of VARCHAR (10);

• SampleVARRAY: Name of the VARRAY type.
• 3: Maximum number of elements allowed in the VARRAY.
• VARCHAR2(10): Data type of the elements.

Step 2: Declare and Initialize a VARRAY Variable

After creating the VARRAY type, you can declare a variable of that type and initialize it:

-- To Declare a variable which is of VARRAY type 
DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');

2. Accessing VARRAY Elements

In VARRAYs, we can access elements easily like simple arrays. We can access single element that we want to access or also we can access all the elements by using loops. Syntax for accessing VARRAY elements is:

Accessing Single Element

To access a specific element, use the index number within parentheses.

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');

Begin
     -- Accessing elements by index
     dbms_output.PUT_LINE('This is First element: ' || myVarray(1));
End;

Output:

Accessing Multiple Elements

You can iterate through all the elements using a loop.

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');

Begin
     -- Accessing alll the elements
     FOR i IN 1.. myVarray.COUNT loop
          dbms_output.PUT_LINE('This is element ' || i || ':' || myVarray(i));
     END LOOP;
End;

Output:

3. Deleting Elements from VARRAY

To delete elements in VARRAYs we simply need to mention the element that we want to delete from VARRAY in DELETE(_). Syntax to delete elements from VARRAY is:

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');
Begin
     -- this will delete3rd element
     myVarray.DELETE(3); 
End;

Output:

4. Add More Elements to VARRAY

Consider we have created a VARRAY and now we want to add some more elements in it so this can be done by using the EXTEND keyword. Which will extend the VARRAY size by one. If we want to extend VARRAY size by more than one then we can simply mention that size in it as EXTEND(4). This will extend VARRAY by four.

The syntax to add an element is:

DECLARE 
     myVarray SampleVARRAY := SampleVARRAY('Ramesh', 'Kamlesh', 'Shyam');
Begin
     -- this will extend VARRAY by one
     myVarray.Extend;
     -- this will add 'Pankaj' at index 4
     myVarray(4) := 'Pankaj';
End;

Output:

Important Points About PL/SQL VARRAY

• The maximum number of elements for a VARRAY is defined at the time of creation and cannot be changed later. If you need to increase the size, you'll have to redefine the VARRAY type and update any dependent code.
• In Oracle databases, VARRAYs are stored as a single column in a table, which makes them ideal for preserving the order of elements.
• Unlike nested tables, VARRAYs cannot have gaps in their indexing. If you try to reference an index that doesn’t exist, you’ll encounter an exception.
• To remove elements, you must manually set them to NULL or create a new VARRAY without the unwanted elements.