VARRAYs (Variable-Size Arrays) in Oracle PL/SQL

What is a VARRAY?

A VARRAY (Variable-Size Array) is an Oracle collection type that stores a fixed maximum number of elements of the same data type. The actual number of elements can vary from 0 up to the specified maximum size.

A VARRAY is suitable when the maximum number of values is known in advance, such as storing a student’s top 5 marks or an employee’s limited phone numbers.

Features of VARRAY

• Stores elements of the same data type.
• Has a fixed maximum size specified at creation.
• Elements are stored in order (indexed starting from 1).
• Elements are stored contiguously (no gaps).
• Supports collection methods like COUNT, LIMIT, EXTEND, TRIM, FIRST, and LAST.

Syntax

1. Create a VARRAY Type

CREATE TYPE type_name AS VARRAY(max_size) OF datatype;
/

Example

CREATE TYPE marks_array AS VARRAY(5) OF NUMBER;
/

Example 1: VARRAY in PL/SQL

DECLARE
    TYPE marks_array IS VARRAY(5) OF NUMBER;

    marks marks_array := marks_array(80, 85, 90, 95, 88);

BEGIN
    FOR i IN 1 .. marks.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Mark = ' || marks(i));
    END LOOP;
END;
/

Output

Mark = 80
Mark = 85
Mark = 90
Mark = 95
Mark = 88

Example 2: Using EXTEND

DECLARE
    TYPE name_array IS VARRAY(3) OF VARCHAR2(30);

    names name_array := name_array();

BEGIN
    names.EXTEND;
    names(1) := 'Rahul';

    names.EXTEND;
    names(2) := 'Priya';

    names.EXTEND;
    names(3) := 'Amit';

    FOR i IN 1 .. names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
END;
/

Output

Rahul
Priya
Amit

Example 4: VARRAY as a Table Column

Step 1: Create VARRAY Type

CREATE TYPE phone_array AS VARRAY(3) OF VARCHAR2(15);
/

Step 2: Create Table

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID NUMBER,
    CUSTOMER_NAME VARCHAR2(50),
    PHONE_NUMBERS phone_array
);

Step 3: Insert Data

INSERT INTO CUSTOMER
VALUES
(
    101,
    'Rahul',
    phone_array('9876543210','9123456789')
);

Retrieve Data

SELECT *
FROM CUSTOMER;

Collection Methods

VARRAY vs Nested Table

Advantages

• Simple to use and maintain.
• Maintains the order of elements.
• Efficient for small collections with a known maximum size.
• Can be stored directly in database tables.
• Faster access for fixed-size collections.

Limitations

• Maximum size must be defined at creation time.
• Cannot exceed the specified limit.
• Individual elements cannot be deleted from the middle.
• Less suitable for large or frequently changing collections.

Applications

• Student marks (maximum 5 or 10 subjects)
• Employee phone numbers (limited count)
• Product sizes (S, M, L, XL)
• Weekly work schedule
• Top-ranked items