VARRAY Methods FAQS

1. What is a VARRAY in Oracle?

A VARRAY (Variable-size Array) is a collection type in PL/SQL that stores a fixed maximum number of elements of the same type.

• Maintains order of elements
• Can be stored in database columns
• Elements are contiguous in memory

2. How do you declare a VARRAY?

DECLARE

   TYPE emp_varray_type IS VARRAY(5) OF VARCHAR2(50);

   emp_names emp_varray_type := emp_varray_type();

BEGIN

   NULL;

END;

• 5 is the maximum number of elements
• Must initialize using () before adding elements

3. How do you assign values?

BEGIN

   emp_names := emp_varray_type('Alice', 'Bob', 'Charlie');

END;

• You can assign all elements at once during initialization
• VARRAY cannot exceed maximum size

4. How do you access elements?

DBMS_OUTPUT.PUT_LINE(emp_names(1)); -- Alice

DBMS_OUTPUT.PUT_LINE(emp_names(2)); -- Bob

• Indexing is numeric and starts at 1
• Accessing beyond the declared size raises an exception

5. Common VARRAY methods

Method/Property	Description
EXTEND(n)	Increases the size by n (default 1)
TRIM(n)	Removes n elements from the end (default 1)
DELETE	Removes all elements (cannot delete specific index)
COUNT	Returns the number of elements currently in the VARRAY
FIRST	Returns the first index (always 1 if non-empty)
LAST	Returns the last index currently populated
PRIOR(n)	Returns the previous index before n
NEXT(n)	Returns the next index after n

6. Example using VARRAY methods

DECLARE

   TYPE emp_varray_type IS VARRAY(5) OF VARCHAR2(50);

   emp_names emp_varray_type := emp_varray_type('Alice', 'Bob', 'Charlie');

   i PLS_INTEGER;

BEGIN

   DBMS_OUTPUT.PUT_LINE('Total elements: ' || emp_names.COUNT);

 

   i := emp_names.FIRST;

   WHILE i IS NOT NULL LOOP

      DBMS_OUTPUT.PUT_LINE('Index ' || i || ': ' || emp_names(i));

      i := emp_names.NEXT(i);

   END LOOP;

 

   emp_names.TRIM; -- removes last element (Charlie)

   DBMS_OUTPUT.PUT_LINE('After TRIM, count: ' || emp_names.COUNT);

 

   emp_names.EXTEND(1);

   emp_names(emp_names.LAST) := 'David';

   DBMS_OUTPUT.PUT_LINE('After EXTEND and addition: ' || emp_names(emp_names.LAST));

END;

• Demonstrates EXTEND, TRIM, COUNT, FIRST, LAST, NEXT

7. Advantages of VARRAY

• Maintains element order
• Efficient storage for small fixed-size collections
• Can be stored in tables
• Ideal for multi-row data as a single column

8. Limitations

• Maximum size is fixed at declaration
• Cannot delete individual elements, only all elements
• Sparse indexing is not allowed
• Performance can degrade with very large VARRAYs

9. Best practices

• Use VARRAY for small, ordered collections
• Use EXTEND/TRIM for dynamic updates within maximum size
• Avoid overusing VARRAY for large datasets – prefer Nested Tables or Associative Arrays
• Always check COUNT before accessing indices