Oracle 26ai Collections: When to Use VARRAYs and Nested Tables

Three PL/SQL collection kinds, two SQL collection kinds

When a VARRAY fits

Create a phone-list type and a table column

CREATE TYPE lesson5_phone_list_t AS
  VARRAY(10) OF VARCHAR2(25 CHAR);
/

CREATE TABLE lesson5_contacts (
  customer_id    NUMBER PRIMARY KEY,
  customer_name  VARCHAR2(80 CHAR),
  phone_list     lesson5_phone_list_t
);

INSERT INTO lesson5_contacts VALUES (
  101, 'Avery Chen',
  lesson5_phone_list_t('+1-202-555-0101', '+1-202-555-0102')
);
INSERT INTO lesson5_contacts VALUES (
  102, 'Morgan Diaz', lesson5_phone_list_t()
);
INSERT INTO lesson5_contacts VALUES (
  103, 'Taylor Reed', NULL
);
COMMIT;

The constructor creates a collection value. The first insert supplies two elements, the second supplies an initialized empty collection, and the third supplies a null collection. Telephone values use a character datatype because punctuation, country prefixes and leading zeros are meaningful. The element length allows formatted examples; it does not validate whether a number is dialable.

The slash submits the type definition in clients such as SQL*Plus when running a script. It is not SQL to send to a driver. Follow your client's statement-submission rules. Notice that phone_list is directly a relational table column: a VARRAY does not require an object wrapper.

CREATE TYPE lesson5_customer_t AS OBJECT (
  customer_id    NUMBER,
  customer_name  VARCHAR2(80 CHAR),
  phone_list     lesson5_phone_list_t
);
/

Query the complete collection or individual elements

Select the column when the application needs the complete list. As with the previous lesson's object values, the SQL datatype and the client's visual display are separate matters:

SELECT c.customer_id, c.phone_list
FROM lesson5_contacts c
ORDER BY c.customer_id;

Some clients show constructor-like text; others provide an expandable collection display. The result has two columns, and the second is a collection value. If the consumer requires one phone per result row, unnest the collection with TABLE(). VARRAYs support this SQL querying pattern; SQL access is not reserved for nested tables.

SELECT c.customer_id,
       p.COLUMN_VALUE AS phone_number
FROM lesson5_contacts c, TABLE(c.phone_list) p
WHERE c.customer_id = 101
ORDER BY p.COLUMN_VALUE;

Because the elements are scalar strings, COLUMN_VALUE names the element value. For a collection of objects, the unnested alias exposes the object's attributes instead. This query illustrates two rows for customer 101, sorted by phone text. It does not expose an automatic position column or promise the stored preference order.

Preserved order inside a VARRAY does not remove SQL's requirement for explicit result ordering. If a relational report must show preference position, model an explicit position attribute or column. Alternatively, traverse the VARRAY by subscript in PL/SQL. The query above is intentionally filtered to the populated collection; returning parents with no elements requires a separate outer-join design.

Initialize, extend and iterate safely in PL/SQL

Read the populated collection into a variable and use its current count to traverse the existing positions. Enable your client's DBMS_OUTPUT display to see the messages:

SET SERVEROUTPUT ON
DECLARE
  l_phones lesson5_phone_list_t;
BEGIN
  SELECT c.phone_list INTO l_phones
  FROM lesson5_contacts c
  WHERE c.customer_id = 101;

  DBMS_OUTPUT.PUT_LINE('Count: ' || l_phones.COUNT);
  DBMS_OUTPUT.PUT_LINE('Limit: ' || l_phones.LIMIT);

  IF l_phones.COUNT > 0 THEN
    FOR i IN 1 .. l_phones.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(i || ': ' || l_phones(i));
    END LOOP;
  END IF;
END;
/

The illustrative messages identify count 2, limit 10 and the two phone values in constructor order. The loop uses COUNT, not LIMIT, because unused capacity is not an existing element. This block assumes the known populated row. A general routine must also handle a missing customer and a null collection before calling methods such as COUNT.

An uninitialized VARRAY variable is atomically null. Calling EXTEND does not initialize it. First assign an empty constructor value, then add an element, and finally assign that element's value:

DECLARE
  l_phones lesson5_phone_list_t := lesson5_phone_list_t();
BEGIN
  IF l_phones.COUNT < l_phones.LIMIT THEN
    l_phones.EXTEND;
    l_phones(l_phones.LAST) := '+1-202-555-0103';
  END IF;
  DBMS_OUTPUT.PUT_LINE('Count: ' || l_phones.COUNT);
  l_phones.TRIM;
  DBMS_OUTPUT.PUT_LINE('After trim: ' || l_phones.COUNT);
END;
/

This independent block illustrates count 1 followed by count 0. EXTEND appends a slot; it does not supply a phone number. TRIM removes the last element. VARRAYs do not support deleting an individual position with DELETE(i). A null element still occupies a position and contributes to COUNT; dense does not mean every element is non-null.

Empty and null communicate different states if your application assigns them different meanings. Empty can mean that the list is known to contain no numbers; null can mean that no list value has been supplied. Define this contract rather than silently switching between them. For null collections, EXISTS is the collection method that does not raise COLLECTION_IS_NULL; other method calls need an initialized value.

Update a persisted VARRAY as a whole value

PL/SQL permits assignment to an existing element. Persisting that edit requires replacing the column's collection value. SQL does not support piecewise DML against an individual VARRAY element. This continuation changes the second phone in customer 101's list:

DECLARE
  l_phones lesson5_phone_list_t;
BEGIN
  SELECT c.phone_list INTO l_phones
  FROM lesson5_contacts c
  WHERE c.customer_id = 101
  FOR UPDATE;

  IF l_phones.EXISTS(2) THEN
    l_phones(2) := '+1-202-555-0199';
    UPDATE lesson5_contacts c
    SET phone_list = l_phones
    WHERE c.customer_id = 101;
  END IF;
END;
/

The block changes the collection variable and writes that complete value back. It does not commit. In a practice session, choose COMMIT to retain the change or ROLLBACK to discard it and release the row lock. In an application, transaction ownership belongs to the calling workflow. Avoid leaving the transaction open while waiting for unrelated user input.

FOR UPDATE locks the selected row while the application reads and replaces the list, addressing the risk of another session changing it between those operations. Production code still needs an appropriate error-handling and concurrency policy. A simple direct UPDATE with a new constructor is another whole-value replacement when retaining existing elements is unnecessary.

When a nested table fits better

A nested table is useful when membership varies widely and queries or modifications focus on selected elements. Persisted nested tables use separate storage tables. Their retrieval order and PL/SQL indexes should not be treated as permanent element identity. Store a business key or explicit position when an element needs one.

This small, independent example introduces the syntax. The next lesson examines nested tables in more detail:

CREATE TYPE lesson5_tag_list_t AS TABLE OF VARCHAR2(30 CHAR);
/

CREATE TABLE lesson5_projects (
  project_id  NUMBER PRIMARY KEY,
  tags        lesson5_tag_list_t
) NESTED TABLE tags STORE AS lesson5_project_tags_nt;

INSERT INTO lesson5_projects VALUES (
  201, lesson5_tag_list_t('training', 'oracle')
);
COMMIT;

SELECT p.project_id, t.COLUMN_VALUE AS tag
FROM lesson5_projects p, TABLE(p.tags) t
ORDER BY p.project_id, t.COLUMN_VALUE;

The query illustrates oracle and training in alphabetical order. The declared storage table supports the nested collection; it is not a reason to bypass the parent collection interface casually. Nested tables allow supported SQL element-level operations, unlike VARRAY piecewise updates. Design constraints and indexes deliberately, and measure the queries your application actually runs.

A PL/SQL nested table can become sparse after deleting an element. Then COUNT reports the number of existing elements, not necessarily the largest index. A loop over 1 .. COUNT can encounter a missing element or skip a later one. Use FIRST and NEXT to visit existing indexes:

DECLARE
  TYPE number_list_t IS TABLE OF NUMBER;
  l_values number_list_t := number_list_t(10, 20, 30);
  l_index PLS_INTEGER;
BEGIN
  l_values.DELETE(2);
  l_index := l_values.FIRST;
  WHILE l_index IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(l_index || ': ' || l_values(l_index));
    l_index := l_values.NEXT(l_index);
  END LOOP;
END;
/

This illustrates indexes 1 and 3, containing 10 and 30, even though the count is two. Traversing existing indexes also handles an initialized empty collection because FIRST returns null. Null collections remain a separate initialization case.

When an associative array fits

Choose an associative array for temporary keyed values in PL/SQL. Assign values by key without first extending the collection. String keys can represent labels; integer keys can represent sparse identifiers. An associative array starts empty, rather than atomically null.

DECLARE
  TYPE phone_lookup_t IS TABLE OF VARCHAR2(25)
    INDEX BY VARCHAR2(20);
  l_phone phone_lookup_t;
BEGIN
  l_phone('preferred') := '+1-202-555-0101';
  l_phone('backup') := '+1-202-555-0102';
  IF l_phone.EXISTS('preferred') THEN
    DBMS_OUTPUT.PUT_LINE(l_phone('preferred'));
  END IF;
END;
/

This block illustrates a lookup of the preferred number. Traversal follows key sort order, not insertion order; string-key ordering is affected by relevant NLS settings. If sequence is a requirement, do not assume that the order of assignments supplies it.

Associative arrays cannot serve as persisted table-column types. That does not mean they have no database interoperability: PL/SQL APIs, bulk operations and supported client bindings can use them under specific type and interface rules. Check the interface you are implementing instead of assuming every collection kind is interchangeable.

Make the choice from the workload

Ask whether the values form an owned list or independently managed entities. A ten-entry preference list normally loaded together points toward a VARRAY. Frequently changing membership with SQL element operations points toward a nested table. Temporary key-value work points toward an associative array. Separate child rows often fit phone verification records, transaction items or relationships needing independent keys and constraints.

Storage is also a workload question. VARRAY storage can involve inline data or LOB storage depending on its definition, size and storage settings; nested tables use separate storage. Neither representation provides a universal efficiency advantage. Benchmark realistic counts, element widths, reads and updates before treating a small demonstration as performance evidence.

Define shared SQL collection types at schema level when table columns or SQL object attributes need them. Local and package PL/SQL types serve different scopes, and identically shaped declarations are not necessarily assignment-compatible. Although each collection has one index dimension, composite elements can model multilevel collections. Keep this introductory model simple unless the requirements actually need nesting.

For the next lesson, remember three distinctions: a VARRAY's limit is its capacity, a nested table's index is not permanent persisted position, and an associative array's key is a lookup key. Choose the datatype first, then specify initialization, iteration, persistence and transaction behavior.

Oracle 26ai references: collection kinds, VARRAYs, nested tables, associative arrays, collection methods and collection design.