Describe Key Concepts for Modifying Object Tables

Object tables in Oracle store rows as object instances rather than as flat collections of scalar values. Each row is an object whose structure is defined by a user-defined object type. While you can use standard DML — INSERT, UPDATE, DELETE — to modify object tables, the presence of object types, nested collections, and object references introduces additional syntax requirements and behavioral constraints that do not apply to relational tables. This lesson covers the key concepts you need to understand before writing DML against object tables in Oracle 19c and 23ai.

How Object Tables Differ from Relational Tables

In a relational table, each column holds a scalar value of a built-in SQL type such as NUMBER, VARCHAR2, or DATE. In an object table, each row is an instance of a user-defined object type, and the columns correspond to the attributes of that type. This structural difference introduces three behavioral differences when writing DML:

1. When a column is associated with an object type, you must use the object type constructor to supply values for insert and update operations. You cannot supply a plain list of scalar values the way you would for a relational row.
2. Updating attributes of an object within an object table requires the use of a table alias. Without the alias, Oracle cannot resolve the attribute reference unambiguously.
3. When an object table contains columns that reference other objects using Oracle's object reference mechanism, you must use the REF operator to supply or retrieve those reference values in insert and update statements.

There are two general approaches to supplying data in DML against object tables. You can specify all attribute values directly in the constructor call, or you can use a subquery within an INSERT or UPDATE statement to derive the values from existing data.

Object Types and the Constructor Method

Every user-defined object type in Oracle has an implicitly generated constructor method that shares the type name and accepts attribute values as positional arguments. When inserting a row into an object table, you call this constructor to produce the object value:

-- Define the object type
CREATE TYPE address_t AS OBJECT (
  street   VARCHAR2(100),
  city     VARCHAR2(50),
  postcode VARCHAR2(10)
);

-- Create an object table
CREATE TABLE addresses OF address_t;

-- Insert using the constructor
INSERT INTO addresses
VALUES (address_t('123 Main St', 'Detroit', '48201'));

The constructor call address_t(...) is required. Oracle does not accept a bare list of values without the type name because it needs to know which object type to instantiate.

Using ALTER TYPE to Evolve Object Types

After an object type has been created and used as the basis for object tables or stored in columns, it can be modified using ALTER TYPE. You can add or drop attributes and member methods:

-- Add an attribute to an existing object type
ALTER TYPE address_t ADD ATTRIBUTE country VARCHAR2(50) CASCADE;

The CASCADE clause propagates the change to all dependent object tables and columns. Without it, Oracle will refuse the alteration if dependents exist. Use INVALIDATE instead of CASCADE when you want to defer revalidation — dependent objects are marked invalid and recompiled on next access.

Oracle also supports type versioning through ALTER TYPE ... COMPILE and the type evolution mechanism, which allows applications that store object instances in the database to upgrade stored data to the new type definition without requiring an immediate full table scan. This is particularly relevant for long-lived schemas where object types evolve alongside the application.

Updating Object Table Rows

When updating attributes of a row in an object table, you must assign a table alias and use dotted attribute notation:

-- Correct: table alias required for attribute update
UPDATE addresses a
SET    a.city = 'Ann Arbor',
       a.postcode = '48103'
WHERE  a.street = '123 Main St';

-- Incorrect: no alias — Oracle cannot resolve the attribute reference
UPDATE addresses
SET    city = 'Ann Arbor';  -- ORA-00904 or type mismatch

You can also replace the entire object value using the constructor:

UPDATE addresses a
SET    VALUE(a) = address_t('456 Oak Ave', 'Ann Arbor', '48103', 'USA')
WHERE  a.street = '123 Main St';

The VALUE() function retrieves or sets the entire object instance for a row. It is the object table equivalent of selecting or replacing all column values in a relational row at once.

Object References and the REF Operator

Oracle object tables support object references — a typed pointer from one object to another, analogous to a foreign key but operating at the object level. A column of type REF object_type stores a reference to a row in the referenced object table.

-- A type that references address_t
CREATE TYPE person_t AS OBJECT (
  name    VARCHAR2(100),
  address REF address_t
);

CREATE TABLE persons OF person_t;

To insert a row with an object reference, use the REF() function to obtain the reference from the source object table:

INSERT INTO persons
SELECT person_t('John Doe', REF(a))
FROM   addresses a
WHERE  a.street = '456 Oak Ave';

To retrieve the referenced object's attributes through the reference, use the DEREF() function:

SELECT p.name, DEREF(p.address).city AS city
FROM   persons p;

Dangling references — references that point to a deleted object — can be detected with the IS DANGLING predicate and should be handled in production code to avoid unexpected NULL results when dereferencing.

Nested Tables and VARRAYs

Object types can include collection attributes — nested tables or VARRAYs. These require additional handling in DML. To modify the contents of a nested table column, use the TABLE() operator to treat the nested table as a queryable and modifiable collection:

-- Add an element to a nested table column
INSERT INTO TABLE(
  SELECT p.phone_numbers FROM customers p WHERE p.id = 101
) VALUES ('313-555-0199');

VARRAY elements cannot be modified individually — the entire VARRAY must be replaced with a new value using UPDATE and the VARRAY constructor. This makes VARRAYs more appropriate for small, fixed-size collections where bulk replacement is acceptable, while nested tables are better suited for variable-length collections that require individual element operations.

Type Inheritance and Polymorphism

Oracle supports single-level type inheritance through the UNDER clause. A subtype inherits all attributes and methods of its parent type and can add new ones:

CREATE TYPE employee_t UNDER person_t (
  employee_id NUMBER,
  department  VARCHAR2(50)
);

An object table defined on the parent type can store instances of both the parent and any of its subtypes — this is Oracle's implementation of table polymorphism. When querying a polymorphic object table, use TREAT() to cast a row to a specific subtype and access subtype-specific attributes:

SELECT TREAT(VALUE(p) AS employee_t).department
FROM   persons p
WHERE  VALUE(p) IS OF TYPE (employee_t);

Changes to a parent type propagate to all subtypes and to tables that store subtype instances. Altering a parent type therefore requires careful impact analysis before execution in production.

Transaction Management and Performance

DML against object tables participates in Oracle's standard transaction model — COMMIT, ROLLBACK, and SAVEPOINT all behave as expected. For large-scale modifications to object tables, the same performance strategies that apply to relational tables are relevant: batch processing with bulk collect and FORALL, appropriate indexing on object attributes, and partitioning for large object tables.

Function-based indexes can be created on expressions involving object attributes, allowing the optimizer to use index range scans for queries that filter on specific attribute values. In Oracle 23ai, improvements to the Cost Based Optimizer's handling of user-defined types reduce the need for manual optimizer hints in many common object table query patterns.

Restrictions on DML-Enabled Java Stored Procedures

If you perform DML inside a Java stored procedure and call that procedure from SQL, Oracle enforces the following restrictions to prevent reentrancy and transaction conflicts:

1. The procedure cannot be called from a SELECT statement or from a parallelized DML statement.
2. It cannot be called from an INSERT, UPDATE, or DELETE statement if that statement modifies the same tables as the stored procedure.
3. It cannot be called from any DML statement if it performs transaction management operations such as commit() or rollback(), or if it executes DDL statements, which Oracle commits automatically.
4. It cannot be called from a DML statement if it performs system or session control operations.

Violations of these restrictions produce a runtime error, not a compile-time error. Design Java stored procedures that interact with object tables with these constraints in mind from the outset — retrofitting transaction management out of a procedure that has already been embedded in application SQL is significantly more disruptive than designing correctly from the start.

Security and Privilege Considerations

Object tables require the same DML privileges as relational tables — INSERT, UPDATE, DELETE, and SELECT granted on the table or through a role. In addition, executing object type constructors and methods requires EXECUTE privilege on the object type itself. In schemas where object types are owned by one user and object tables by another, both the table privilege and the type execute privilege must be granted to the user performing DML.

Summary

Modifying object tables in Oracle requires understanding the constructor syntax for INSERT, the alias requirement for attribute-level UPDATE, and the REF and DEREF operators for reference-typed columns. Collections within object types — nested tables and VARRAYs — each have their own DML patterns. Type evolution through ALTER TYPE with CASCADE propagates changes to dependent objects. Java stored procedures that perform DML are subject to strict calling-context restrictions enforced at runtime. In Oracle 23ai, improvements to type handling, optimizer support for user-defined types, and the native SQL BOOLEAN type expand the practical utility of object-relational features for modern application development.