Updating Rows in a Varray in Oracle 23ai

A varray column in Oracle 23ai can only be updated as a complete unit through the parent row UPDATE statement. Unlike nested tables, which support element-level UPDATE through the TABLE() operator covered in Lesson 5, a varray has no mechanism for targeting individual elements directly in SQL. To update varray data, either replace the entire collection using the varray type constructor in a standard UPDATE statement, or use a PL/SQL block to fetch the varray, modify it using collection methods, and write it back.

These two patterns serve different use cases and the distinction between them is important for writing correct varray DML in Oracle 23ai.

Pattern 1 — SQL Full Replacement Using the Type Constructor

The simplest varray update replaces all elements atomically using a single UPDATE statement. The SET clause assigns a new varray value using the type constructor, which must match the declared varray type name exactly:

UPDATE customer_obj_table
SET    phone_array = phone_array_type('1-800-234-5678', '1-888-987-6543')
WHERE  cust_id = 25;

This statement replaces the entire phone_array varray for customer ID 25 with two new phone numbers. The previous values are discarded and the new values from the constructor are stored atomically. Several constraints apply:

• The constructor name must match the declared varray type — phone_array_type() in this example
• The number of elements provided to the constructor cannot exceed the declared maximum size of the varray type
• All elements are replaced — there is no mechanism to retain existing elements while adding new ones through this pattern
• Setting the varray to NULL is also valid and removes all elements: SET phone_array = NULL

The SQL full replacement approach is appropriate when all new varray values are known at the time of the update and the complete set of values is being replaced rather than extended.

Varray UPDATE Syntax

The general syntax for updating a varray column through the parent row is:

UPDATE <table name>
SET    <varray column> = <varray constructor>
WHERE  <clause>;

Concrete Example — Replacing Varray Elements

The following example replaces the phone numbers for customer ID 26 with two new values:

UPDATE customer_obj_table
SET    phone_array = phone_array_type('1-800-333-5978', '1-888-654-6133')
WHERE  cust_id = 26;

The components of this statement map directly to the syntax table above. The phone_array_type constructor initializes the replacement varray with two elements. The WHERE clause ensures only the row for customer ID 26 is affected. All other rows retain their existing phone_array values.

Pattern 2 — PL/SQL Fetch-Extend-Update

When the goal is to add one or more elements to an existing varray without discarding the current values, the SQL full replacement approach requires knowing all current values in advance. For cases where only the new element is known, the PL/SQL fetch-extend-update pattern is the correct approach: fetch the current varray into a PL/SQL variable, extend it using the EXTEND collection method, assign the new element, then write the modified varray back to the table.

DECLARE
    v_phones phone_array_type;
BEGIN
    -- Step 1: Fetch the current varray into a PL/SQL variable
    SELECT phone_list
    INTO   v_phones
    FROM   customer_obj_table
    WHERE  cust_id = 26;

    -- Step 2: Extend the varray by one element
    v_phones.EXTEND;

    -- Step 3: Assign a value to the new last element
    v_phones(v_phones.LAST) := '1-800-336-6199';

    -- Step 4: Write the modified varray back to the table
    UPDATE customer_obj_table
    SET    phone_list = v_phones
    WHERE  cust_id = 26;

    COMMIT;
END;
/

Each step in the block serves a specific purpose:

• SELECT INTO — fetches the current varray from the table into the PL/SQL variable v_phones. The variable type must match the declared varray type.
• EXTEND — adds one NULL element at the end of the in-memory varray. The EXTEND call will raise an exception if the current element count already equals the declared maximum size of the type.
• Element assignment — populates the newly added last element using v_phones.LAST to reference its index. This avoids hardcoding the index position.
• UPDATE — writes the modified varray back to the table row for customer ID 26. The database row now contains the original elements plus the new one.
• COMMIT — persists the change to the database.

The EXTEND method can also accept arguments to add multiple elements at once. v_phones.EXTEND(3) adds three null elements, which can then be assigned individually by index. In all cases the total element count after extension must not exceed the varray's declared maximum.

Modifying an Existing Element by Index

The fetch-modify-update pattern also applies when replacing a specific element at a known index position without changing the total element count. In this case EXTEND is not needed — the fetched varray is modified directly by index assignment before being written back:

DECLARE
    v_phones phone_array_type;
BEGIN
    SELECT phone_list
    INTO   v_phones
    FROM   customer_obj_table
    WHERE  cust_id = 26;

    -- Replace the element at index 1 with a new value
    v_phones(1) := '1-800-999-0000';

    UPDATE customer_obj_table
    SET    phone_list = v_phones
    WHERE  cust_id = 26;

    COMMIT;
END;
/

This replaces only the first element of the varray for customer ID 26. The remaining elements are preserved because they are fetched as part of the complete varray and written back unchanged.

When to Use Each Pattern

Contrast with Nested Table UPDATE

The difference between varray and nested table update patterns reflects their storage architectures. A nested table is stored in a separate storage table and its individual rows are addressable through the TABLE() operator, enabling element-level UPDATE ... SET ... WHERE without fetching the entire collection. A varray is stored as a single unit within or adjacent to the parent row and has no separate addressable storage — the entire varray must be replaced as a unit at the SQL level, with PL/SQL required for element-level modification.

This architectural constraint is the reason varray schemas benefit from careful consideration of the update access pattern at design time. If the application frequently modifies individual elements rather than replacing the entire collection, a nested table is generally the more appropriate collection type choice.

Updating Rows in Varray - Exercise