Modifying Nested Tables and Varrays in Oracle 23ai

This module covers the DML operations available for Oracle's two storable collection types — nested tables and varrays. Because Oracle stores nested table data and varray data differently, the syntax and constraints for inserting, updating, and deleting records differ between the two types. Understanding those differences is the foundation for writing correct and maintainable collection DML in Oracle 23ai.

Module Objectives

When you have completed this module, you will be able to:

1. Insert, update, and delete records from nested tables
2. Insert, update, and delete elements from a varray
3. Explain the way Oracle stores data within nested tables and varrays

Oracle Collection Types

Oracle PL/SQL provides three collection types. Two of them — nested tables and varrays — can be stored as columns in database tables and are the focus of this module. The third, associative arrays, exists only within PL/SQL programs and cannot be stored as a column in a database table.

The storage difference between nested tables and varrays is not merely technical — it directly determines what DML operations are available. A nested table stored in a separate storage table supports element-level INSERT, UPDATE, and DELETE through the TABLE() operator. A varray stored inline with its parent row does not support element-level deletion and requires either a NULL assignment or a full varray reconstruction to remove elements.

The Oracle Object-Relational Model

Oracle supports an object-relational model in which user-defined types serve as the building blocks for object tables and collection columns. A type defines the structure of an object — its attributes and optionally its methods — before any table that uses that type is created. This separation of type definition from table creation allows types to be reused across multiple tables and nested within one another to model complex real-world structures.

The CREATE TYPE ... AS OBJECT statement defines an object type in Oracle 23ai. The CREATE TYPE command supports inheritance through the UNDER clause, and type bodies support method overloading, providing polymorphism within the Oracle type system.

Object Type Definition — A Practical Example

To illustrate how Oracle object types work, consider a schema for storing photographs. A photograph has attributes including a topic, a date taken, a photographer, a negative number, a picture number, and a reference to the image file stored externally. These attributes map to a type definition:

CREATE TYPE picture_t AUTHID CURRENT_USER
AS OBJECT (
    negative#    NUMBER,
    picture#     NUMBER,
    topic        VARCHAR2(80),
    date_taken   DATE,
    photographer person_t,
    picture      BFILE
);

This type references two other types: person_t for the photographer attribute, and BFILE for the external image reference. BFILE is Oracle's binary file type for LOB data stored outside the database on the server file system. It is valid in Oracle 23ai for referencing external binary content such as image files.

The person_t type defines the photographer's identity:

CREATE TYPE person_t AUTHID CURRENT_USER
AS OBJECT (
    first_name   VARCHAR2(32),
    last_name    VARCHAR2(32),
    middle_init  VARCHAR2(3),
    gender       CHAR(1),
    address      address_t
);

The person_t type itself references address_t, which defines the address structure:

CREATE TYPE address_t AUTHID CURRENT_USER
AS OBJECT (
    address_line1    VARCHAR2(80),
    address_line2    VARCHAR2(80),
    street_name      VARCHAR2(30),
    street_number    NUMBER,
    city             VARCHAR2(30),
    state_or_province VARCHAR2(2),
    zip              NUMBER(5),
    zip_4            NUMBER(4),
    country_code     VARCHAR2(20)
);

Type Creation Order

When creating interdependent types, the order of creation is critical. A type cannot reference another type that does not yet exist in the database. For the photograph example, the creation order must follow the dependency chain from the most atomic type to the most inclusive:

1. address_t — no dependencies
2. person_t — depends on address_t
3. picture_t — depends on person_t
4. CREATE TABLE pictures OF picture_t — depends on picture_t

Attempting to create person_t before address_t exists will produce a compilation error. Oracle does support incomplete type declarations for resolving circular dependencies, but in most schemas a clear dependency order can be established and followed directly.

Once the type hierarchy is defined, the object table is created using the OF keyword:

CREATE TABLE pictures OF picture_t (
    CONSTRAINT pk_pictures PRIMARY KEY (negative#, picture#)
    USING INDEX TABLESPACE indexes
);

This creates a table whose rows conform to the structure of picture_t. Each row in the pictures table is an instance of the picture_t object type.

AUTHID CURRENT_USER and AUTHID DEFINER

The AUTHID clause in a type definition controls the privilege model used when methods defined in the type body are executed. Oracle 23ai supports two options:

For types that will be deployed across multiple schemas or used in multi-tenant Oracle 23ai environments, AUTHID CURRENT_USER is generally the appropriate choice. It ensures that method execution respects the privilege boundary of the calling context rather than running with the elevated privileges of the type owner.

Methods and Type Bodies

A type definition in Oracle 23ai can include method declarations alongside attribute definitions. When methods are declared in the type specification, a separate type body must be created to provide the method implementations. Type bodies in Oracle 23ai support method overloading, which means multiple methods can share the same name as long as their parameter signatures differ — the basis of polymorphism in Oracle's object-relational model.

Methods declared in a type and implemented in a type body can operate on the type's attributes directly, providing encapsulated behavior alongside the data structure. This is the mechanism by which Oracle's object types approximate object-oriented programming patterns within a relational database engine.

The next lesson describes the key concepts for modifying nested tables and varrays, establishing the operational rules that govern INSERT, UPDATE, and DELETE behavior for each collection type before the subsequent lessons demonstrate the specific DML syntax.