Modifying Object Tables in Oracle — Module Summary

This module covered the complete set of DML operations — INSERT, UPDATE, and DELETE — as they apply to Oracle object tables. Object tables introduce syntax requirements that do not exist in standard relational DML: object type constructors for insert, table aliases and dot notation for update, and REF subqueries when columns store typed object references. The following sections summarize the key concepts from each lesson.

DML on Object Tables — What Changes and What Does Not

Oracle treats object tables as tables first and objects second. The same transactional model that governs relational DML applies without change — COMMIT, ROLLBACK, and SAVEPOINT behave identically. What changes is the syntax required to interact with the object-oriented layer. Three structural differences from relational DML apply consistently across all lessons in this module:

1. When a column is associated with an object type, the object type constructor must be used to supply attribute values in INSERT and UPDATE statements. Oracle does not accept a bare list of scalar values without the constructor call.
2. Updating an attribute of a nested object type requires a table alias. Without the alias, Oracle cannot resolve the dotted attribute reference and raises an error.
3. When a column stores a REF — a typed pointer to a row in another object table — the REF() function must be used inside a subquery to retrieve the reference value. A REF column cannot be assigned a scalar literal.

Lesson 1 — Introduction to DML on Object Tables

The module opened by establishing that DML operations on object tables are syntactically similar to relational DML but require understanding how object types interact with SQL statements. Object tables store rows as instances of user-defined object types. Columns correspond to object attributes. When complex structures such as REF columns, nested tables, or VARRAY collections are present, DML must use specialized syntax to navigate and manipulate those structures.

All DML inside PL/SQL blocks executes within a transactional context. Changes are not permanent until COMMIT is issued. If an error occurs before the commit, ROLLBACK restores the database to its pre-transaction state. Exception handling in PL/SQL blocks ensures that unexpected errors do not leave the database in a partially modified state — control transfers immediately to the exception section, where the block can log the error, roll back, and re-raise.

Lesson 2 — Key Concepts for Modifying Object Tables

Lesson 2 established the foundational concepts before any DML syntax was introduced. The constructor method — an implicitly generated method that shares the object type name and accepts attribute values in positional order — is the mechanism for instantiating object values in INSERT and UPDATE statements. Named parameter notation is not valid for object type constructors; positional arguments are required.

Object types can be evolved after creation using ALTER TYPE ... ADD ATTRIBUTE CASCADE, which propagates changes to all dependent object tables. The VALUE() function retrieves or replaces an entire object instance in a single operation. The REF() and DEREF() functions navigate object references — REF() stores the pointer, DEREF() retrieves the referenced object's attributes. Dangling references — pointers to deleted objects — are detected with the IS DANGLING predicate.

Java stored procedures that perform DML carry runtime restrictions: they cannot be called from SELECT statements, cannot modify the same tables as the calling DML statement, and cannot perform transaction management or DDL. These restrictions are enforced at runtime, not compile time.

Lesson 3 — Inserting Rows into Object Tables

Two categories of object table INSERT were covered. The first uses the object type constructor to supply all attribute values inline:

INSERT INTO customer_obj_table
VALUES (909, 'John', 'Turn',
        Address_type('1981 Potter Ave', NULL,
        'Neotu', 'CA', '88951'),
        NULL, DATE '2026-03-16', 'HENRY');

The second uses SELECT REF() to retrieve a reference from another object table and store it in a REF column. All column values must be supplied even when some are NULL. The DATE and TIMESTAMP literals replace legacy to_date() calls for session-format independence.

PL/SQL's record-level INSERT extension allows a %ROWTYPE record variable to be used directly in the VALUES clause when the record structure matches the target table exactly. VARRAY columns require the full VARRAY constructor in a single call — individual elements cannot be inserted independently.

Lesson 4 — Inserting Rows Using a Subquery

When a REF column value must be retrieved at insert time, an inline subquery using REF(alias) is placed inside the VALUES clause. The subquery must return exactly one row — filter on a primary key or unique column. The table alias passed to REF() is mandatory; passing the table name directly is not valid.

INSERT INTO sale_header
VALUES (500,
  (SELECT REF(c) FROM customer_obj_table c WHERE c.cust_id = 1),
  89.45, 3.58, 15, 108.03,
  DATE '2026-03-16',
  detail_table()
);

For high-volume inserts, FORALL with BULK COLLECT replaces row-by-row processing with a single bulk operation, reducing context switches between the SQL and PL/SQL engines. The SAVE EXCEPTIONS clause on FORALL captures individual row errors in SQL%BULK_EXCEPTIONS without aborting the entire bulk operation.

Lesson 5 — Updating Object Tables

Two update patterns were covered. Updating a nested object attribute requires a table alias and dot notation to navigate from the alias through the object type column to the specific attribute:

UPDATE customer_obj_table cot
SET    cot.full_address.zip = '09982'
WHERE  cot.cust_id = 1;

Updating a scalar column requires the alias prefix but not dot notation into a nested type. The full UPDATE syntax supports the WHERE CURRENT OF cursor_name clause for cursor-based row processing and the RETURNING INTO clause for capturing updated values into bind variables.

Transaction boundaries were demonstrated using the CANINE table example. ROLLBACK undoes all uncommitted DML back to the last COMMIT — not just the most recent statement. WHEN OTHERS exception handlers should always re-raise with RAISE after rolling back to ensure the calling environment receives notification of the failure.

Lesson 6 — Updating Object Tables Using a Subquery

A REF column cannot be assigned a literal value — the reference must be retrieved using REF(alias) inside a SET clause subquery. The subquery must return exactly one row, and the outer and inner table aliases must be distinct even in self-referential updates:

UPDATE product_obj_table p1
SET    p1.package_id = (SELECT REF(p2)
                        FROM   product_obj_table p2
                        WHERE  p2.product_id = 14)
WHERE  p1.product_id = 22;

After a REF column is updated, the previously referenced object is not affected — Oracle replaces the pointer only. If the previously referenced object is later deleted, the column becomes a dangling reference detectable with IS DANGLING. In Oracle 23ai, a REFERENCES constraint on the REF column definition prevents deletion of referenced objects, providing foreign key equivalent protection at the object level.

Lesson 7 — Deleting Rows from Object Tables

DELETE from an object table uses standard syntax — no additional object type syntax is required unless the WHERE clause must resolve a REF value:

-- Direct predicate delete
DELETE FROM product_obj_table
WHERE  product_id = 34;

-- REF-based delete using subquery
DELETE FROM sale_header
WHERE  cust_ref = (
  SELECT REF(cot)
  FROM   customer_obj_table cot
  WHERE  cot.cust_id = 26
);

DDL statements cannot be used inside static PL/SQL blocks because Oracle validates schema dependencies at compile time — DDL changes those dependencies at runtime. Cursor-based deletion using WHERE CURRENT OF cursor_name supports conditional row-by-row deletion during procedural cursor iteration. Always validate DELETE predicates with a SELECT preview before executing in production, and consider object reference dependencies before removing rows that may be referenced by REF columns in other tables.

Module Summary

After completing this module you are able to:

1. Define the key concepts for modifying Oracle object tables — constructors, REF columns, dot notation, and type evolution
2. Write INSERT statements using object type constructors for inline object values and REF() subqueries for reference columns
3. Insert rows using inline and outer-SELECT REF subquery patterns, and use FORALL with BULK COLLECT for high-volume inserts
4. Write UPDATE statements using table aliases and dot notation for nested object attributes, and subqueries for REF column reassignment
5. Write DELETE statements against object tables using direct predicates and REF-resolving subqueries, within proper transactional and exception handling structure

The next module covers DML operations against object tables that contain VARRAY and nested table collections, extending these patterns to collection-specific insert, update, and delete operations.

Glossary