VARRAY (Variable-size array) in PL/SQL

In PL/SQL, a VARRAY (Variable-size Array) is a type of collection that allows you to store a fixed number of elements of a specific data type. VARRAYs are useful when you need to maintain an ordered collection of items, especially when the number of elements is known in advance and is relatively small. Here’s a detailed overview:

Definition - Syntax
TYPE V_Array_type IS VARRAY(n) OF datatype;

Key Characteristics of VARRAYs

Fixed Size
Ordered Collection
Homogeneous Elements
Dynamic Resizing
Performance

1. Fixed Size:

When you define a VARRAY, you specify its maximum size (the maximum number of elements it can hold). However, you can initialize it with fewer elements.

2. Ordered Collection:

The elements in a VARRAY are ordered, meaning that each element can be accessed using its index (starting from 1). The order is maintained as you add, modify, or delete elements.

3. Homogeneous Elements:

All elements in a VARRAY must be of the same data type. This could be any scalar type (like NUMBER, VARCHAR2, etc.) or even other collections or user-defined types.

4. Dynamic Resizing:

While you set a maximum size during definition, a VARRAY can hold fewer elements than its maximum capacity. You can also change the size by adding or removing elements, but you cannot exceed the defined maximum.

5. Performance:

VARRAYs are suitable for storing small amounts of data that are frequently accessed by index. They provide better performance than nested tables when you need a fixed-size collection.

Syntax for Declaring a VARRAY

The syntax for declaring a VARRAY is as follows:

TYPE array_type_name IS VARRAY(max_size) OF data_type;

array_type_name: The name of the VARRAY type you are creating.
max_size: The maximum number of elements the VARRAY can hold.
data_type: The data type of the elements stored in the VARRAY.

Definition and Syntax

1. Type Definition:

TYPE V_Array_type IS VARRAY(n) OF datatype;

n represents the maximum size of the array.
datatype defines the type of elements the array will hold.

2. Declaration and Initialization:

v_day V_Array_type := V_Array_type(NULL, NULL, NULL, ...);

Here, v_day is a variable of the V_Array_type, initialized with NULL values.

3. Assignment:

v_day(1) := 'MONDAY';
v_day(2) := 'TUESDAY';
...

4. Execution (Output):

dbms_output.put_line('v_day(1): ' || v_day(1));

dbms_output.put_line is used to print the values stored in the array.

Example Code

SET SERVEROUTPUT ON;

DECLARE
   TYPE V_Array_type IS VARRAY(7) OF VARCHAR2(30);
   v_day V_Array_type := V_Array_type(NULL, NULL, NULL, 
                           NULL, NULL, NULL, NULL);
BEGIN
   v_day(1) := 'MONDAY';
   v_day(2) := 'TUESDAY';
   v_day(3) := 'WEDNESDAY';
   v_day(4) := 'THURSDAY';
   v_day(5) := 'FRIDAY';
   v_day(6) := 'SATURDAY';
   v_day(7) := 'SUNDAY';

   dbms_output.put_line('v_day(1): ' || v_day(1));
   dbms_output.put_line('v_day(2): ' || v_day(2));
   dbms_output.put_line('v_day(3): ' || v_day(3));
   dbms_output.put_line('v_day(4): ' || v_day(4));
END;

Explanation:

The VARRAY is defined as V_Array_type with a size of 7 and type VARCHAR2(30).
v_day is declared as a VARRAY of this type and is initialized with NULL values.
Each element is then assigned a day of the week.
The dbms_output.put_line statements print out the values, demonstrating how to access elements from the array.

This example illustrates how to declare, initialize, and use a VARRAY in PL/SQL.

VARRAY using a FOR loop in PL/SQL

To loop through a VARRAY using a FOR loop in PL/SQL, you can use a simple FOR loop that iterates over the indices of the array. Below is an example demonstrating how to do this:

Example Code

Here’s how to define a VARRAY, assign values, and loop through it using a FOR loop:

SET SERVEROUTPUT ON;

DECLARE
   -- Define a VARRAY type with a maximum size of 7 for days of the week
   TYPE V_Array_type IS VARRAY(7) OF VARCHAR2(30);
   -- Declare a variable of the VARRAY type
   v_day V_Array_type := V_Array_type(NULL, NULL, NULL, 
                                NULL, NULL, NULL, NULL);
BEGIN
   -- Assign days of the week to the VARRAY
   v_day(1) := 'MONDAY';
   v_day(2) := 'TUESDAY';
   v_day(3) := 'WEDNESDAY';
   v_day(4) := 'THURSDAY';
   v_day(5) := 'FRIDAY';
   v_day(6) := 'SATURDAY';
   v_day(7) := 'SUNDAY';

   -- Loop through the VARRAY using a FOR loop
   FOR i IN 1 .. v_day.COUNT LOOP
       dbms_output.put_line('Day ' || i || ': ' || v_day(i));
   END LOOP;
END;

Explanation:

1. VARRAY Definition:

A VARRAY type named V_Array_type is defined to hold up to 7 days of the week.

2. Variable Declaration:

v_dayis declared as a variable of type V_Array_typeand initialized with NULL values.

3. Value Assignment:

Each day of the week is assigned to the respective index of the VARRAY.

4. FOR Loop:

The loop iterates from 1 to v_day.COUNT, which returns the number of elements in the VARRAY.

Inside the loop, dbms_output.put_line prints each day along with its index.

Output

When you execute this PL/SQL block, you will see the following output:

Day 1: MONDAY
Day 2: TUESDAY
Day 3: WEDNESDAY
Day 4: THURSDAY
Day 5: FRIDAY
Day 6: SATURDAY
Day 7: SUNDAY

This example shows how to use a FOR loop effectively to iterate through elements in a VARRAY.

Advantages of Using VARRAYs

Efficient Access: Elements can be accessed quickly using their index.
Easy Manipulation: Adding and modifying elements is straightforward.
Memory Management: The fixed size helps in managing memory efficiently for small collections.

Limitations

Fixed Size: The maximum size must be declared, which limits flexibility.
Not Ideal for Large Data: VARRAYs are not suitable for very large collections or when the number of elements can vary greatly.

Conclusion

VARRAYs in PL/SQL provide a useful way to manage collections of data with a fixed size and maintain their order. They are particularly useful in scenarios where you need to work with a known number of related items, making them an essential feature in PL/SQL programming.

VARRAY in PLSQL Functions | LIMIT | COUNT | EXTEND

Pranav Bakare ・ Oct 13

#sql #database #oracle #fullstack

Extending the VARRAY

When working with VARRAYs (variable-size arrays) in PL/SQL, several exceptions can occur. Here are three common types of exceptions you may encounter:

1. Reference to Uninitialized Collection

This exception occurs when you try to reference a VARRAY that has not been initialized. In PL/SQL, collections must be initialized before they can be used. If you attempt to access or manipulate an uninitialized collection, you will encounter a NULL_POINTER_EXCEPTION.

Example:

DECLARE
    TYPE V_Array_type IS VARRAY(7) OF VARCHAR2(30);
    v_days V_Array_type;  -- Declared but not initialized

BEGIN
    -- Attempt to access an uninitialized collection
    DBMS_OUTPUT.PUT_LINE(v_days(1));  -- This will raise an error
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Output:

Error: ORA-06530: Reference to uninitialized collection

2. Index Out of Bounds

This exception occurs when you try to access an index that is either less than 1 or greater than the number of elements in the VARRAY (i.e., greater than the value returned by COUNT). In PL/SQL, collection indexes start at 1, and attempting to access an invalid index results in a SUBSCRIPT_OUTSIDE_LIMIT exception.

Example:

DECLARE
    TYPE V_Array_type IS VARRAY(5) OF VARCHAR2(30);
    v_days V_Array_type := V_Array_type('Monday', 'Tuesday', 'Wednesday');

BEGIN
    -- Attempting to access an out-of-bounds index
    DBMS_OUTPUT.PUT_LINE(v_days(6));  -- This will raise an error
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Output:

Error: ORA-06532: Subscript beyond count

3. Collection Overflow

This exception occurs when you try to extend a VARRAY beyond its defined maximum size. Since VARRAYs have a fixed upper limit defined during their declaration, attempting to add more elements than allowed will result in a COLLECTION_OVERFLOW exception.

Example:

DECLARE
    TYPE V_Array_type IS VARRAY(3) OF VARCHAR2(30);
    v_days V_Array_type := V_Array_type('Monday', 'Tuesday', 'Wednesday');

BEGIN
    -- Attempt to extend beyond the maximum size
    v_days.EXTEND;  -- This will succeed but won't add a valid element yet
    v_days(4) := 'Thursday';  -- This will raise an error
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Output:

Error: ORA-06532: Subscript beyond count

Summary of Exceptions

Reference to Uninitialized Collection: Accessing a VARRAY before it has been initialized leads to a NULL_POINTER_EXCEPTION.
Index Out of Bounds: Accessing an invalid index will result in a SUBSCRIPT_OUTSIDE_LIMIT exception.
Collection Overflow: Attempting to exceed the defined maximum size of a VARRAY results in a COLLECTION_OVERFLOW exception.

Handling Exceptions

You can handle these exceptions using PL/SQL’s EXCEPTION block, allowing your program to respond appropriately to errors and maintain robust functionality. Always ensure collections are initialized, check index bounds, and be mindful of the defined size when working with VARRAYs.

Blog

VARRAY (Variable-size array) in PL/SQL

Pranav Bakare