Varrays in PL/SQL: Syntax, Examples, and Use Cases for Oracle Collections

Varrays in PL/SQL (Variable-size Arrays) are bounded, one-dimensional collections that allow the storage of a fixed number of elements of the same data type. This guide explains how to define, use, and manipulate Varrays in PL/SQL through examples, covering declaration, object types, DML integration, and best practices.

What Are Varrays in PL/SQL?

Varrays (or VARYING ARRAYs) are a type of collection that store a fixed number of elements. They are ideal when the maximum size of the collection is known beforehand. In Oracle, Varrays are indexed starting from 1 and can be used in PL/SQL blocks, object types, and as column data types in tables.

Syntax for Declaring Varrays

Here's the basic syntax to define a Varray in PL/SQL:

TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];

type_name: Name of the Varray type.
size_limit: Maximum number of elements.
element_type: Oracle data type (e.g., INTEGER, VARCHAR2).
NOT NULL (optional): Disallows nulls in the array.

Initializing and Using Varrays

DECLARE
  TYPE integer_varray IS VARRAY(3) OF INTEGER;
  varray_integer INTEGER_VARRAY := integer_varray(NULL, NULL, NULL);
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Integer Varray [' || i || '] = ' || varray_integer(i));
  END LOOP;

  varray_integer(1) := 11;
  varray_integer(2) := 12;
  varray_integer(3) := 13;

  DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Updated Varray Values:');
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Integer Varray [' || i || '] = ' || varray_integer(i));
  END LOOP;
END;
/

Using EXTEND Method and Collection API

If you initialize a Varray with fewer elements than its defined size, you'll encounter an ORA-06533: Subscript beyond count error. To dynamically allocate rows, use the EXTEND method:

DECLARE
  TYPE integer_varray IS VARRAY(3) OF INTEGER;
  varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
  FOR i IN 1..3 LOOP
    varray_integer.EXTEND;
    varray_integer(i) := 10 + i;
  END LOOP;

  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Integer Varray [' || i || '] = ' || varray_integer(i));
  END LOOP;
END;
/

Defining Varrays as Object Types

To create reusable Varray types at the schema level:

CREATE OR REPLACE TYPE integer_varray AS VARRAY(3) OF INTEGER;
/

Use it in PL/SQL:

DECLARE
  varray_integer INTEGER_VARRAY := integer_varray(NULL, NULL, NULL);
BEGIN
  FOR i IN 1..3 LOOP
    varray_integer(i) := 10 + i;
  END LOOP;
END;
/

Handling Null Values in Varrays

By default, Varrays allow nulls. To disallow nulls:

CREATE OR REPLACE TYPE integer_varray AS VARRAY(100) OF INTEGER NOT NULL;
/

Then initialize and allocate rows:

DECLARE
  varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
  FOR i IN 1..varray_integer.LIMIT LOOP
    varray_integer.EXTEND;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Integer Varray Initialized [' || varray_integer.COUNT || ']');
END;
/

Varrays as Column Data Types in Tables

You can define Varrays as column types to store lists in relational tables.

CREATE OR REPLACE TYPE address_varray AS VARRAY(3) OF VARCHAR2(30 CHAR);
/

CREATE TABLE addresses (
  address_id       INTEGER NOT NULL,
  individual_id    INTEGER NOT NULL,
  street_address   ADDRESS_VARRAY NOT NULL,
  city             VARCHAR2(20 CHAR),
  state            VARCHAR2(20 CHAR),
  postal_code      VARCHAR2(20 CHAR),
  country_code     VARCHAR2(10 CHAR),
  CONSTRAINT address_pk PRIMARY KEY (address_id)
);

DML Operations with Varrays

Insert Full Varray

INSERT INTO addresses VALUES (
  11, 11,
  address_varray('Office of Senator McCain', '450 West Paseo Redondo', 'Suite 200'),
  'Tucson', 'AZ', '85701', 'USA'
);

Query Varray with Nested Table

CREATE OR REPLACE TYPE varray_nested_table IS TABLE OF VARCHAR2(30 CHAR);
/

SELECT column_value
FROM   THE (
  SELECT CAST(street_address AS varray_nested_table)
  FROM addresses
  WHERE address_id = 11
);

Update Varray

UPDATE addresses
SET street_address = address_varray('Office of Senator McCain', '2400 E. Arizona Biltmore Cir.', 'Suite 1150')
WHERE address_id = 11;

Update Specific Element Using PL/SQL

DECLARE
  TYPE address_type IS RECORD (
    address_id        INTEGER,
    individual_id     INTEGER,
    street_address    ADDRESS_VARRAY,
    city              VARCHAR2(20 CHAR),
    state             VARCHAR2(20 CHAR),
    postal_code       VARCHAR2(20 CHAR),
    country_code      VARCHAR2(10 CHAR)
  );
  address address_type;
  CURSOR get_address(id INTEGER) IS
    SELECT * FROM addresses WHERE address_id = id;
BEGIN
  OPEN get_address(11);
  FETCH get_address INTO address;
  CLOSE get_address;

  address.street_address(1) := 'Office of Senator John McCain';

  UPDATE addresses
  SET street_address = address.street_address
  WHERE address_id = 11;
END;
/

Best Practices for Using Varrays

Use Varrays when the number of elements is known and fixed.
Avoid Varrays for frequently updated structures — use Nested Tables instead.
Disallow nulls to avoid logic issues and data gaps.
Use EXTEND carefully to allocate memory for dynamic initialization.
Avoid querying Varray columns directly — use CAST with nested tables.
Prefer schema-level Varray types for reusability.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.