INSERT Commands for a Varray in Oracle 23ai

A varray is an ordered, bounded collection — the maximum number of elements is fixed at type creation and cannot be exceeded. This bounded structure determines how varray insertion works and why it differs from nested table insertion covered in Lesson 3.

The most important architectural constraint to understand before writing varray INSERT statements is this: it is not possible to insert individual elements into a varray after the parent row has been created using the TABLE() operator. Unlike nested tables, which support element-level INSERT INTO TABLE() statements, a varray is always inserted and replaced as a complete unit through the parent row. Elements can only be added at the time the parent row is created, or replaced in their entirety via an UPDATE statement — covered in Lesson 6.

Oracle provides two approaches for inserting a varray during parent row creation:

1. A single INSERT statement with a populated varray constructor that initializes all elements immediately
2. A single INSERT statement with an empty varray constructor that defers element population to a later UPDATE

The Varray Constructor

Like nested tables, varrays use a type constructor for initialization. The constructor name matches the varray type name exactly. For a varray type named PHONE_ARRAY, the constructor is PHONE_ARRAY().

• PHONE_ARRAY('value1', 'value2') — initializes the varray with the specified elements. The number of elements provided cannot exceed the declared maximum size of the varray type.
• PHONE_ARRAY() — initializes a non-NULL empty varray with no elements. The varray exists and can be replaced via UPDATE later, but currently holds no values.

The distinction between an empty constructor PHONE_ARRAY() and a NULL varray column matters operationally. A NULL varray cannot be referenced or updated using collection syntax. An empty varray initialized with PHONE_ARRAY() is a valid, non-NULL collection that can be replaced with a populated varray via UPDATE.

Method 1 — Single INSERT with Populated Varray Constructor

The following example inserts a customer record into CUSTOMER_OBJ_TABLE with two phone numbers populated in the PHONE_ARRAY varray column at insert time. The schema uses nested object type constructors — CUSTOMER_TYPE for the outer object, ADDRESS_TYPE for the address attribute, and PHONE_ARRAY for the varray column:

INSERT INTO customer_obj_table VALUES (
    CUSTOMER_TYPE(
        1,
        'Amy',
        'Black',
        ADDRESS_TYPE('100 West Main', NULL, 'Omaha', 'NE', '98765'),
        PHONE_ARRAY('1-808-876-6543', '1-808-209-0987'),
        DATE '2025-12-22',
        'JANET'
    )
);

Each component of this statement serves a specific purpose:

• CUSTOMER_TYPE() — the outer object type constructor that initializes the entire customer object row. All column values are passed as arguments to this constructor in the order they are defined in the type.
• 1, 'Amy', 'Black' — the customer ID, first name, and last name scalar attributes
• ADDRESS_TYPE() — a nested object type constructor for the address attribute. The second argument is NULL for address line 2, which has no value for this record.
• PHONE_ARRAY('1-808-876-6543', '1-808-209-0987') — the varray constructor populated with two phone number strings. The varray type must have been defined with a maximum size of at least 2 to accept this insert.
• DATE '2025-12-22' — the record date using ANSI date literal syntax
• 'JANET' — the last updated by value

This method is appropriate when all varray elements are known at the time the parent row is created and can be expressed as constructor literal values.

Method 2 — Single INSERT with Empty Varray Constructor

When varray data is not available at the time the parent row is created, insert the parent row with an empty varray constructor and populate the elements later via UPDATE:

INSERT INTO customer_obj_table VALUES (
    CUSTOMER_TYPE(
        2,
        'Amy',
        'Black',
        ADDRESS_TYPE('100 West Main', NULL, 'Omaha', 'NE', '98765'),
        PHONE_ARRAY(),
        DATE '2025-12-22',
        'JANET'
    )
);

The PHONE_ARRAY() empty constructor initializes the varray column as a non-NULL empty collection. The parent row is valid and committed, and the varray column can be replaced with a populated varray in a subsequent UPDATE statement when the phone numbers become available.

Do not use NULL in place of PHONE_ARRAY() when the intent is to populate the varray later. A NULL varray column cannot be updated using the collection replacement syntax — it must be treated as a nullable column and assigned a complete constructor value from scratch. The empty constructor approach is cleaner and more explicit about intent.

When to Use Each Method

In both cases the varray is inserted as a complete unit within the parent row. There is no mechanism in Oracle 23ai for adding a single element to an existing varray using an INSERT statement — this is the fundamental difference between varrays and nested tables for insertion operations.

Oracle Collection Types — Indexing and Population Density

Understanding how Oracle's three collection types differ in their indexing model clarifies why varrays and nested tables behave differently from associative arrays in DML contexts.

Varrays and nested tables are both SQL datatypes — they can be stored as column types in database tables and manipulated through SQL DML. Both use 1-based sequential integer indexing, meaning element positions are numbered starting at 1 and no gaps are permitted in the index sequence. This sequential, no-gap structure is described as a densely populated collection. The difference between the two is that a varray has a fixed declared maximum element count, while a nested table is unbounded.

Associative arrays are a PL/SQL-only datatype and cannot be stored as database column types. They are defined and used exclusively within PL/SQL blocks, anonymous blocks, and package specifications. Unlike varrays and nested tables, associative arrays support both integer and string indexes, and integer indexes do not need to be sequential — gaps are permitted. This non-sequential structure is described as sparsely populated. Associative arrays are dynamically sized with no fixed upper bound.

All three collection types have access to Oracle's collection API methods such as COUNT, FIRST, LAST, PRIOR, NEXT, and EXISTS. Varrays and nested tables additionally support EXTEND and TRIM. Associative arrays support DELETE with sparse index removal, which varrays and nested tables do not support in the same way due to their dense indexing requirement.

Inserting Rows into Varray - Exercise