Query Modify Nested Tables and Varrays Conclusion

Module Summary

This module covered the DML operations available for Oracle's two storable collection types — nested tables and varrays — across eight lessons. Each lesson built on the previous, establishing the foundational concepts first and then demonstrating the INSERT, UPDATE, and DELETE syntax for each collection type. The following summaries describe what each lesson contributed to that progression.

Lesson Summaries

Lesson 1 — Modifying Nested Tables and Varrays in Oracle 23ai introduced the module's scope and the Oracle object-relational model that underlies collection DML. The lesson identified Oracle PL/SQL's three collection types — nested tables, varrays, and associative arrays — and established the critical distinction that only nested tables and varrays can be stored as columns in database tables. Associative arrays exist exclusively within PL/SQL programs. The lesson explained the CREATE TYPE ... AS OBJECT statement, showed how object types are composed by referencing one another (address_t inside person_t inside picture_t), and described the dependency order that must be followed when creating interdependent types. The AUTHID clause was introduced, distinguishing AUTHID CURRENT_USER (invoker rights, recommended for multi-schema deployments) from AUTHID DEFINER (definer rights, the default when no clause is specified). The lesson also explained how type bodies support method overloading to provide polymorphism within Oracle's object-relational model.

Lesson 2 — Concepts for Modifying Nested Tables and Varrays in Oracle 23ai established the operational concepts required before writing collection DML. The single most important constraint introduced in this lesson is that varrays do not support partial updates — the entire varray must be replaced with a new set of values in a single UPDATE statement. Individual element assignment requires a PL/SQL block, not a standalone SQL statement. The lesson described how Oracle stores nested table elements in a separate storage table linked to the parent row through a hidden 16-byte NESTED_TABLE_ID column, and how varrays are stored inline with the parent row as raw data when the declared size is 4 KB or less, or as BLOB data when the declared size exceeds 4 KB. Collection type selection guidance was presented in a comparison table covering element count bounds, access patterns, and indexing requirements. The lesson also catalogued Oracle's built-in collection methods — EXTEND, TRIM, DELETE, FIRST, LAST, PRIOR, NEXT, COUNT, EXISTS, and LIMIT — and clarified which methods apply to nested tables, varrays, and associative arrays respectively. The LIMIT method is varray-only and returns NULL for nested tables.

Lesson 3 — Inserting Rows into a Nested Table in Oracle 23ai demonstrated the two approaches for adding data to a nested table column. Both approaches rely on the type constructor — a function Oracle automatically creates for every user-defined collection type, named identically to the collection type itself. Method 1 uses a single INSERT statement that populates both the parent row and the nested table simultaneously, passing the constructor with element arguments directly in the VALUES clause. This method is appropriate when all nested table data is known at insert time. Method 2 separates parent row creation from nested table population: the parent row is inserted first with an empty constructor (DETAIL_TABLE()) to initialize the nested table as a non-NULL empty collection, and elements are added incrementally in subsequent INSERT INTO TABLE() statements. The distinction between an empty constructor and a NULL nested table was emphasized — a NULL nested table cannot be targeted by the TABLE() operator in subsequent DML. The lesson also showed how to query USER_TAB_COLUMNS and USER_TYPES to verify the collection type structure before writing INSERT statements, and how to confirm the NESTED_TABLE_ID after insertion to validate that elements are correctly linked to their parent row.

Lesson 4 — INSERT Commands for a Varray in Oracle 23ai demonstrated the two approaches for inserting varray data, and reinforced the most important architectural constraint distinguishing varrays from nested tables for insert operations: it is not possible to insert individual elements into an existing varray using the TABLE() operator. A varray is always inserted and replaced as a complete unit through the parent row. Method 1 populates the varray at insert time using the type constructor with element arguments inside the parent row INSERT statement. Method 2 uses an empty constructor (PHONE_ARRAY()) to defer element population to a later UPDATE. The lesson clarified that a NULL varray and an empty-constructor varray behave differently in subsequent operations — an empty constructor produces a valid non-NULL collection that can be replaced via UPDATE, while a NULL varray cannot be updated using collection syntax. The lesson also presented a comparison table of Oracle's three collection types by storage capability, index type, population density, and size bound, clarifying why associative arrays — which support sparse integer and string indexing and are PL/SQL-only — differ fundamentally from the densely indexed, SQL-storable nested tables and varrays.

Lesson 5 — Updating Rows in a Nested Table in Oracle 23ai demonstrated element-level UPDATE for nested tables using the TABLE() operator and introduced the concept of flattening. Flattening is the technique by which Oracle exposes the rows of a nested table storage table as a queryable and modifiable set through the TABLE() operator. Without flattening, there is no mechanism in SQL to address individual nested table rows directly. The lesson showed the general UPDATE TABLE(SELECT ...) SET ... WHERE ... syntax and explained each component: the TABLE() operator that exposes the nested table, the subquery that identifies the parent row, the SET clause that specifies which nested table column to update, and the outer WHERE clause that filters which nested rows are affected. Omitting the outer WHERE clause updates all nested rows for the identified parent. The lesson demonstrated updating multiple columns in a single SET clause and showed the FORALL bulk update pattern — using BULK COLLECT to gather parent row identifiers and FORALL to apply a single nested table UPDATE across all collected rows in one SQL engine round trip, reducing PL/SQL-to-SQL context switches for large result sets.

Lesson 6 — Updating Rows in a Varray in Oracle 23ai demonstrated the two update patterns for varray columns and explained why they differ from nested table updates. Pattern 1 is SQL full replacement: a single UPDATE statement assigns a new varray value using the type constructor in the SET clause, replacing all elements atomically. This is appropriate when all new values are known and the complete collection is being replaced. Pattern 2 is the PL/SQL fetch-extend-update: the current varray is fetched into a PL/SQL variable using SELECT INTO, extended by one or more elements using the EXTEND collection method, populated at the new index positions, and written back to the table via UPDATE. This pattern is required when existing elements must be preserved and only one or more new elements are being added. The lesson also demonstrated replacing a specific element at a known index position using direct index assignment on the fetched variable, without calling EXTEND. The architectural reason for the varray update model was reiterated: because a varray is stored as a single unit within or adjacent to the parent row, there is no separately addressable storage for individual elements, unlike a nested table whose rows exist in a separate storage table accessible through the TABLE() operator.

Lesson 7 — DELETE Commands for Nested Tables and Varrays in Oracle 23ai established the deletion semantics for both collection types and the asymmetry between them. Nested tables support element-level DELETE through the TABLE() operator, using the same flattening technique as the UPDATE statement from Lesson 5. The outer WHERE clause on the DELETE FROM TABLE() statement filters which nested rows are removed; omitting it removes all rows for the identified parent. The lesson demonstrated targeted deletion by a single attribute value, deletion by status across multiple rows, and the FORALL bulk deletion pattern for applying the same nested table DELETE across many parent rows efficiently. Varrays do not support element-level DELETE — Oracle 23ai does not change this constraint. Three approaches are available for varrays: setting the entire varray to NULL via UPDATE, setting a specific element to NULL by index in a PL/SQL block, or reconstructing the varray without the unwanted element in a PL/SQL block and writing the reconstructed collection back. The lesson also introduced Oracle 23ai JSON Relational Duality Views as an architectural alternative for use cases where document-oriented access patterns or REST exposure through Oracle REST Data Services are preferred over the TABLE() subquery syntax.

Lesson 8 — Deleting All Rows from a Nested Table or Varray in Oracle 23ai covered full collection deletion — removing all elements from a nested table or varray — and introduced the operationally important distinction between a NULL collection and an empty initialized collection. For nested tables, Approach 1 uses DELETE FROM TABLE() without an outer WHERE clause, leaving the column in an initialized empty state that supports immediate subsequent INSERT INTO TABLE() DML. Approach 2 uses UPDATE SET col = NULL, which sets the column to NULL — subsequent TABLE() DML will fail until the column is reinitialized using the empty constructor. For varrays, Approach 3 uses UPDATE SET col = NULL to remove all elements; reinitialization with UPDATE SET col = type() is required before extending in PL/SQL. Approach 4 covers PL/SQL reinitialization of an in-memory varray variable using the empty constructor assignment. The lesson also demonstrated the PL/SQL .DELETE collection method for in-memory nested table variables — which removes all elements from the variable but does not affect the database until the variable is written back via UPDATE — and presented a full deletion comparison table covering all six collection state combinations across both types.

Key Principles from This Module

Across the eight lessons, several principles emerged that govern collection DML in Oracle 23ai and should inform both schema design and application code.

The storage architecture of a collection type determines every DML constraint that follows from it. Nested tables are stored in a separate storage table linked to the parent row by a hidden NESTED_TABLE_ID. This out-of-line storage is what makes the TABLE() operator necessary and what enables element-level INSERT, UPDATE, and DELETE. Varrays are stored inline with the parent row as a single unit. This inline storage is what prevents element-level DML and requires full collection replacement at the SQL level, with PL/SQL required for element-level modification.

The distinction between a NULL collection and an empty initialized collection matters at every stage of the collection lifecycle. A NULL collection cannot be targeted by the TABLE() operator for any DML operation. An empty collection initialized with the type constructor — DETAIL_TABLE() or PHONE_ARRAY() — is a valid non-NULL collection that supports all subsequent DML appropriate to its type. This distinction should be enforced at insert time by always using the empty constructor when collection data is not yet available, rather than inserting a NULL.

The FORALL statement with BULK COLLECT is the correct pattern for applying nested table DML across many parent rows. Executing one INSERT, UPDATE, or DELETE per parent row inside a PL/SQL loop sends one SQL round trip per iteration. FORALL sends the entire batch in a single round trip, reducing context switches between the PL/SQL and SQL engines to one regardless of collection size.

Collection type selection should be driven by the application's access pattern rather than by familiarity. If element-level INSERT, UPDATE, or DELETE is required, or if the collection size is unknown or unbounded, nested tables are the appropriate choice. If the collection has a known fixed maximum size, is typically accessed or replaced as a complete unit, and element ordering by index position matters, varrays are the appropriate choice. Choosing a varray for a use case that requires frequent element-level modification creates unnecessary complexity in the update and delete code.

varray Concepts - Quiz