Update an Object Table in Oracle

UPDATE statements against Oracle object tables follow the same transaction control rules as INSERT statements, but the presence of object types introduces two syntax requirements that do not apply to relational tables. First, a table alias is mandatory when updating attributes of an associated object type. Second, dot notation is used to traverse from the alias through the object type column to the specific attribute. This lesson covers both patterns, the full UPDATE statement syntax, transaction control with COMMIT and ROLLBACK, and a PL/SQL block example demonstrating update within a transaction.

Updating a Nested Object Attribute

The CUSTOMER_OBJ_TABLE table has a FULL_ADDRESS column based on the ADDRESS_TYPE object type. To update an attribute within that nested object type, the UPDATE statement must use a table alias and dot notation to navigate to the specific attribute:

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

The dot notation chain `cot.full_address.zip` maps directly to the syntax template:

table_alias      → cot
object_type_name → full_address
attribute_name   → zip

Without the table alias, Oracle cannot resolve the attribute reference and raises an error. The alias is not optional when navigating into an object type column.

Updating a Scalar Column on an Object Table

When the column being updated is a scalar attribute rather than a nested object type, the statement is simpler. A table alias is still recommended for consistency, but dot notation into an object type is not required:

UPDATE product_obj_table p
SET    p.product_name = 'Dog Food for Pups'
WHERE  p.product_id = 4;

PRODUCT_NAME is a direct scalar attribute of the object type underlying PRODUCT_OBJ_TABLE, not an attribute of a nested object type. No dot notation chain is needed beyond the single alias prefix.

Syntax Template for Object Table UPDATE

UPDATE <table_name> <table_alias>
SET table_alias.object_type_name.attribute_name = <variable>
WHERE <clause>;

Worked Example — Updating a Nested Attribute

Full UPDATE Statement Syntax

The complete Oracle UPDATE syntax supports scalar values, bind variables, function return values, and subqueries in the SET clause:

UPDATE table_name [alias]
SET column1 = {value | function_call | select_statement}
  , column2 = {value | function_call | select_statement}
  , column(n+1) = {value | function_call | select_statement}
WHERE list_of_comparative_operations
[RETURNING column_name INTO :bind_variable];

Key points about this syntax in Oracle 23ai:

• The WHERE clause is optional but its absence updates every row in the table — always include it for targeted updates unless a full-table update is intentional.
• The WHERE CURRENT OF cursor_name clause updates the row most recently fetched by a cursor declared with FOR UPDATE, eliminating the need to re-identify the row by primary key in cursor-based processing.
• The RETURNING INTO clause captures the updated column value into a bind variable — particularly useful when updating LOB columns or when the new value is generated by a sequence or function.
• Unlike the SELECT clause alias syntax, the UPDATE alias must not use the AS keyword — doing so raises ORA-00971: missing SET keyword.

Correlated Subquery in UPDATE

A correlated subquery in the WHERE clause allows the update to be conditioned on data from the same table evaluated row by row:

UPDATE contact c1
SET    c1.middle_initial = 'B'
WHERE  EXISTS (
  SELECT NULL
  FROM   contact c2
  WHERE  c1.contact_id = c2.contact_id
  AND    c2.last_name  = 'Vizquel'
  AND    c2.first_name = 'Oscar'
);

The correlated subquery references c1.contact_id from the outer query, which Oracle evaluates for each candidate row. This example could be simplified by moving the name comparisons directly into the outer WHERE clause — the correlated form is shown here to illustrate that multiple approaches produce equivalent results.

PL/SQL UPDATE Within a Transaction Block

The following PL/SQL block demonstrates a complete update transaction — selecting the current value, modifying it in a PL/SQL variable, applying the update, and committing. Exception handling rolls back the transaction if any error occurs:

SET SERVEROUTPUT ON
DECLARE
  v_num_pages BOOKS.NUM_PAGES%TYPE;
  v_isbn      BOOKS.ISBN%TYPE := '72230665';
BEGIN
  SELECT num_pages
  INTO   v_num_pages
  FROM   books
  WHERE  isbn = v_isbn;

  DBMS_OUTPUT.PUT_LINE('Number of pages before: ' || v_num_pages);

  v_num_pages := v_num_pages + 200;

  UPDATE books
  SET    num_pages = v_num_pages
  WHERE  isbn = v_isbn;

  DBMS_OUTPUT.PUT_LINE('Number of pages after: ' || v_num_pages);
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
    RAISE;  -- re-raise so the caller knows an error occurred
END;
/

Three design points worth noting in this block:

1. ISBN stored as VARCHAR2 — correct. ISBNs must never be stored as NUMBER because leading zeros are significant and ISBNs can contain hyphens or the letter X in older ISBN-10 format.
2. RAISE after ROLLBACK — the exception handler re-raises after rolling back. Without RAISE, the exception is swallowed after printing, and the calling environment receives no indication that the operation failed. Oracle best practice since 11g requires re-raising from WHEN OTHERS handlers.
3. COMMIT inside the block — acceptable for standalone scripts but prevents the operation from participating in a larger outer transaction. In application code, consider moving COMMIT to the calling layer.

Transactions — COMMIT and ROLLBACK

A transaction in Oracle is a logical unit of work composed of one or more DML statements — INSERT, UPDATE, and DELETE — that must succeed or fail as a complete unit. A transaction begins with the first DML statement and ends with either a COMMIT or a ROLLBACK. ROLLBACK undoes all uncommitted DML back to the last COMMIT or session start, not just the most recent statement.

The following example demonstrates transaction boundaries using a CANINE table. The key teaching point is that MISTY is permanently lost — it was inserted after the COMMIT and before the final ROLLBACK:

-- Initial state
SELECT * FROM canine;  -- returns: TRAUBLE

-- Transaction 1: all DML rolled back entirely
INSERT INTO canine VALUES ('SPECS');
INSERT INTO canine VALUES ('BLACKIE');
INSERT INTO canine VALUES ('FRED');
INSERT INTO canine VALUES ('MISTY');
UPDATE canine SET name = 'TROUBLE' WHERE name = 'TRAUBLE';
ROLLBACK;

SELECT * FROM canine;  -- returns: TRAUBLE (unchanged)

-- Transaction 2: committed permanently
UPDATE canine SET name = 'TROUBLE' WHERE name = 'TRAUBLE';
INSERT INTO canine VALUES ('SPECS');
INSERT INTO canine VALUES ('BLACKIE');
INSERT INTO canine VALUES ('FRED');
COMMIT;

-- Transaction 3: rolled back
INSERT INTO canine VALUES ('MISTY');
ROLLBACK;

-- Final state
SELECT * FROM canine;
-- Returns: TROUBLE, SPECS, BLACKIE, FRED
-- MISTY never appears — inserted after COMMIT, rolled back before next COMMIT

Oracle also supports autonomous transactions — transactions that commit or roll back independently within the context of a larger transaction. Autonomous transactions are declared with the PRAGMA AUTONOMOUS_TRANSACTION directive and are important for operations such as audit logging that must persist regardless of whether the outer transaction commits or rolls back.

Summary

Updating object tables in Oracle requires a table alias and dot notation when the target column is a nested object type attribute. Scalar columns on object tables can be updated with a standard alias-prefixed SET clause. The full UPDATE syntax supports scalar values, function calls, subqueries, correlated subqueries, and the WHERE CURRENT OF cursor clause. Within PL/SQL blocks, always re-raise from WHEN OTHERS after rolling back, store ISBNs and similar identifiers as VARCHAR2, and consider whether COMMIT belongs inside the block or at the calling layer. ROLLBACK undoes all uncommitted DML back to the last COMMIT — understanding transaction boundaries is essential for writing correct and reliable DML in Oracle 23ai. The next lesson covers updating records using a subquery.