VARRAY in PLSQL Functions | LIMIT | COUNT | EXTEND

VARRAY in PLSQL Functions | LIMIT | COUNT | EXTEND

In PL/SQL, a VARRAY has a fixed maximum size defined at the time of its declaration. However, it does not directly support functions like LIMIT, COUNT, or EXTEND that are available in other collection types like nested tables or associative arrays. Instead, the following properties and methods can be used with VARRAY:

Key Properties and Methods for VARRAY

1. Count:

You can use the COUNT method to determine the number of elements currently stored in the VARRAY.

This method returns the actual number of elements, which can be less than or equal to the maximum size.

my_numbers.COUNT

2. Limit:

The LIMIT property can be accessed to get the maximum number of elements that can be stored in the VARRAY.

It returns the maximum size defined when the VARRAY was created.

my_numbers.LIMIT

3. Extending the Size:

Unlike other collection types, you cannot extend a VARRAY once it has been created.

To effectively increase the size, you would need to declare a new VARRAY with a larger limit and copy the elements from the old one to the new one, if necessary.

Example Usage

Here’s an example that demonstrates how to use COUNT and LIMIT with a VARRAY in PL/SQL:

DECLARE
    -- Declare a VARRAY type with a maximum size of 5
    TYPE number_varray IS VARRAY(5) OF NUMBER;

    -- Declare a variable of the VARRAY type
    my_numbers number_varray;

BEGIN
    -- Initialize the VARRAY with fewer elements than its limit
    my_numbers := number_varray(10, 20, 30);

    -- Display the current count of elements in the VARRAY
    DBMS_OUTPUT.PUT_LINE('Current count of elements: ' || my_numbers.COUNT);  -- Outputs: 3

    -- Display the limit of the VARRAY
    DBMS_OUTPUT.PUT_LINE('Maximum limit of the VARRAY: ' || my_numbers.LIMIT);  -- Outputs: 5

    -- Add a new element (up to the limit)
    my_numbers(4) := 40;  -- This works fine
    my_numbers(5) := 50;  -- This works fine

    -- Now the VARRAY is full, and the count should be 5
    DBMS_OUTPUT.PUT_LINE('Count after adding elements: ' || my_numbers.COUNT);  -- Outputs: 5

    -- Attempting to add one more element will raise an exception
    -- my_numbers(6) := 60; -- Uncommenting this will cause an exception

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Explanation of the Example

• 1. Initialization: The VARRAY is initialized with three elements (10, 20, 30).
• 2. COUNT: The COUNT method is used to check the number of elements currently in the VARRAY, which returns 3.
• 3. LIMIT: The LIMIT property returns the maximum size of the VARRAY, which is 5.
• 4. Adding Elements: Elements are added to the VARRAY until it reaches its limit.
• 5. Exception Handling: An attempt to add more elements than the defined limit will result in an exception, demonstrating the fixed size limitation of VARRAY.

Conclusion

While VARRAY in PL/SQL does not have functions like EXTEND or direct manipulation capabilities, it provides the COUNT and LIMIT properties that allow you to manage and understand the size of your collections. For more dynamic collections, consider using nested tables or associative arrays.