VARRAY FAQS

1. What is a VARRAY in Oracle?

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

• Elements are stored in order and dense (no gaps).
• Size limit is defined at declaration.
• Can store scalars, records, or objects.
• Useful for small, fixed-size collections.

2. How do you declare a VARRAY?

PL/SQL declaration:

DECLARE

   TYPE num_varray_type IS VARRAY(5) OF NUMBER;

   nums num_varray_type;

BEGIN

   NULL;

END;

SQL declaration for table column:

CREATE TYPE num_varray_type AS VARRAY(5) OF NUMBER;

/

 

CREATE TABLE dept_table (

   dept_id NUMBER,

   salaries num_varray_type

);

3. How do you assign values to a VARRAY?

DECLARE

   TYPE num_varray_type IS VARRAY(5) OF NUMBER;

   nums num_varray_type := num_varray_type(10, 20, 30);

BEGIN

   DBMS_OUTPUT.PUT_LINE(nums(1)); -- 10

END;

• Elements are 1-based indexed.
• Number of elements cannot exceed the maximum size defined in the type.

4. How do you access or update elements?

DECLARE

   TYPE num_varray_type IS VARRAY(5) OF NUMBER;

   nums num_varray_type := num_varray_type(10, 20, 30);

BEGIN

   nums(2) := 50; -- Update 2nd element

   DBMS_OUTPUT.PUT_LINE(nums(2)); -- 50

END;

• No gaps are allowed; indices always contiguous.

5. How do you loop through a VARRAY?

DECLARE

   TYPE num_varray_type IS VARRAY(5) OF NUMBER;

   nums num_varray_type := num_varray_type(10, 20, 30);

   i PLS_INTEGER;

BEGIN

   FOR i IN 1 .. nums.COUNT LOOP

      DBMS_OUTPUT.PUT_LINE(nums(i));

   END LOOP;

END;

• COUNT returns the number of elements.

6. Can VARRAYs be used in SQL queries?

Yes, using TABLE() operator:

DECLARE

   TYPE num_varray_type IS VARRAY(5) OF NUMBER;

   nums num_varray_type := num_varray_type(10, 20, 30);

BEGIN

   FOR rec IN (SELECT * FROM TABLE(nums)) LOOP

      DBMS_OUTPUT.PUT_LINE(rec.COLUMN_VALUE);

   END LOOP;

END;

• COLUMN_VALUE is the default column name in SQL context.

7. Common methods/properties

Method/Property	Description
COUNT	Number of elements currently in the VARRAY
EXTEND	Not supported (VARRAY size is fixed)
TRIM	Remove last element(s)
FIRST / LAST	Returns first/last index
NEXT / PRIOR	Returns next/prior index

⚠ Unlike nested tables, VARRAYs cannot EXTEND beyond their declared maximum size.

8. Advantages of VARRAYs

• Ordered and dense storage
• Fixed size ensures predictable memory usage
• Can be stored in database columns
• Ideal for small, fixed-size collections

9. Limitations

• Maximum size is fixed at type creation
• Cannot sparsely delete elements (indices remain contiguous)
• Less flexible than nested tables for growing collections
• Not ideal for very large collections

10. Common mistakes

• Trying to add more elements than maximum size
• Assuming elements can be deleted to create gaps
• Confusing VARRAYs with nested tables (nested tables can extend dynamically)

11. Best practices

• Use for small, fixed-size collections
• Prefer nested tables for large or dynamically growing collections
• Always check COUNT before accessing elements
• Use TABLE() operator for SQL querying