Using Oracle VARRAYs in Tables

A VARRAY (variable-size array) is an Oracle collection type that stores an ordered set of elements with a maximum size declared in the type definition. When a VARRAY is used as a column in a table, it provides a compact way to embed a small, bounded list of repeating values directly in the parent row.

In older object-relational designs (Oracle 9i–11g era), this was sometimes described as "defining a table within a VARRAY." More precisely, you define a collection type (the VARRAY) whose elements may be scalars or objects, and then store that collection in a single column.

You do not literally create a relational table inside a column. Instead, you create:

1. An object type that represents one “row” of repeating data.
2. A VARRAY type that can hold up to N objects (bounded and ordered).
3. A table with a column of that VARRAY type.

This pattern is best when the repeating group is: small, bounded, and almost always retrieved together with the parent row.

The example below models an employee’s prior employers as an ordered list (job history). Each job history entry is one object, and the list is stored in a VARRAY column.

-- 1) Define the element type (one job history entry)
CREATE TYPE jobhist_ot AS OBJECT (
  employer_name  VARCHAR2(80),
  employer_phone VARCHAR2(20),
  sal            NUMBER(9,2)
);
/

-- 2) Define the collection type (bounded, ordered)
CREATE TYPE jobhist_va AS VARRAY(10) OF jobhist_ot;
/

-- 3) Use the VARRAY type as a column in a relational table
CREATE TABLE employee (
  empno       NUMBER       CONSTRAINT employee_pk PRIMARY KEY,
  dname       VARCHAR2(20) NOT NULL,
  location    VARCHAR2(20),
  job_history jobhist_va
);

Insert a VARRAY by using the collection constructor. Query it either as a whole column value or by expanding it into rows with TABLE().

-- Insert a row with a job history VARRAY
INSERT INTO employee (empno, dname, location, job_history)
VALUES (
  1001,
  'Finance',
  'Detroit',
  jobhist_va(
    jobhist_ot('ACME Corp',   '555-0100', 72000),
    jobhist_ot('Blue Labs',   '555-0110', 80000),
    jobhist_ot('Orchid LLC',  '555-0120', 88000)
  )
);

-- Expand the VARRAY into a row source (ordered, bounded list)
SELECT e.empno,
       e.dname,
       j.employer_name,
       j.employer_phone,
       j.sal
FROM   employee e,
       TABLE(e.job_history) j
WHERE  e.empno = 1001
ORDER  BY j.employer_name;

Advantages

• Bounded size: the maximum number of elements is enforced by the type.
• Ordered semantics: element order is preserved by design.
• Convenient parent-centric reads: when the repeating group is almost always needed with the parent row.

Constraints

• Hard maximum: you must pick a limit (for example, 10). If requirements change, the type may need to change.
• Search and update complexity: querying individual elements is more complex than querying a normalized child table.
• Modeling: for many OLTP designs, a child table with a foreign key remains simpler to index, audit, and evolve.

Oracle 23ai still supports VARRAYs and object types. If your requirement is truly “a bounded, ordered repeating group embedded in the parent row,” you can keep the VARRAY pattern exactly as shown.

That said, Oracle 23ai also supports modern approaches that frequently replace the old “table within a collection” pattern:

Option A: Keep VARRAY (best for small, bounded, ordered lists)

• Use when you want schema-level typing, strict ordering, and a guaranteed maximum size.
• Good fit for short lists such as up to 5 phone numbers, up to 10 prior employers, or up to 12 monthly values.

Option B: Use native JSON type (best for flexible nested arrays)

If the main goal is “store an array of objects inside a row” without predefining the inner schema, JSON is often the modern answer. JSON supports arrays-of-objects natively and is well aligned with application payloads and APIs.

CREATE TABLE employee_json (
  empno       NUMBER PRIMARY KEY,
  dname       VARCHAR2(20) NOT NULL,
  location    VARCHAR2(20),
  job_history JSON
);

Use JSON when the repeating group is not cleanly bounded, the schema evolves frequently, or the data is primarily exchanged as documents.

Option C: Use normalized tables + JSON Relational Duality Views (best of both worlds)

If you want relational integrity (keys, constraints, indexing) but want applications to see a single “document” with an array, duality views provide a JSON interface over normalized storage.

• Store the employee and job history in relational tables with foreign keys.
• Expose a single JSON document view where job history appears as an array.
• Allow DML through the JSON interface while Oracle maps changes back to base tables.

Design guidance: if you need strong relational consistency and reporting performance, this option typically scales better than embedding large arrays.

• VARRAY-in-a-table is still valid in Oracle 23ai for small, bounded, ordered repeating groups.
• JSON is often the modern alternative for nested arrays when flexibility matters more than rigid typing.
• Duality Views are the preferred pattern when you want relational storage with a document-style interface.

Before you move on, use the Exercise link below to validate the syntax and decision criteria.

Before you move on to the next lesson, click the Exercise link below to test your knowledge of VARRAYs.
Create varray - Exercise