Lesson 1

Throughout this course you have executed many SQL queries against Oracle object tables in order to retrieve information. In this module the focus shifts from querying data to modifying it. Specifically, we will examine how Data Manipulation Language (DML)^[1] statements operate when applied to object tables in Oracle.

In relational databases, DML consists of three core statements:

• INSERT – adds rows to a table
• UPDATE – modifies existing rows
• DELETE – removes rows from a table

When working with Oracle’s object-relational features, these same DML operations still apply. However, because object tables are based on user-defined object types, additional considerations arise. Attributes of the object type must be managed properly, object instances may be constructed using type constructors, and relationships between objects can be maintained using reference pointers (REF) rather than simple foreign keys.

This module introduces the syntax and concepts required to safely manipulate rows within object tables using SQL and PL/SQL. While the overall syntax resembles traditional relational operations, understanding how object types interact with SQL statements is essential for writing reliable object-relational code in Oracle 23ai.

After completing this module, you will be able to:

1. Define the key concepts involved in modifying Oracle object tables
2. Write SQL statements that insert rows into object tables
3. Insert object instances using constructors and subqueries
4. Update attributes within object table rows
5. Delete rows using SQL while maintaining object relationships

At a fundamental level, DML statements on object tables behave similarly to DML statements on conventional relational tables. A row can be inserted, updated, or deleted using SQL statements that follow standard Oracle syntax. However, object tables introduce an object-oriented layer that changes how data is represented internally.

An object table stores rows that represent instances of a defined object type. Each row therefore corresponds to a complete object instance, whose attributes map directly to columns in the table. When inserting data into an object table, developers often create a new instance of the object type using its constructor.

For example, if a table is defined using a product object type, an INSERT statement may instantiate the object and insert the resulting object instance into the table. Similarly, UPDATE statements modify the attributes of those object instances.

Oracle object tables can also contain more advanced structures, such as:

• Object references (REF) to other objects
• nested tables
• VARRAY collections

When these structures are present, DML statements must often reference nested attributes or use specialized syntax to manipulate the underlying data structures. For example, deleting rows that reference another object may require a subquery that resolves the REF value pointing to that object.

Despite these differences, the fundamental principles of DML remain unchanged. Oracle treats object tables as tables first and objects second. This means that SQL statements can still manipulate the data using the same transactional controls that apply to relational tables.

All DML operations executed in Oracle occur within a transactional context. When DML statements are issued inside PL/SQL blocks, the changes are not permanently stored until a COMMIT statement is issued. If an error occurs before the commit, the operation can be reversed using ROLLBACK.

This transactional behavior becomes especially important when multiple DML statements execute as part of a single procedure or program unit.

Consider the following simplified PL/SQL procedure:

PROCEDURE change_data IS
BEGIN
  DELETE FROM employees WHERE ... ;
  UPDATE company SET ... ;
  INSERT INTO company_history
  SELECT * FROM company WHERE ... ;
END;

In this example, several DML statements are executed sequentially. Because they run inside a single PL/SQL block, Oracle treats them as part of one transaction. Developers must decide when to commit the changes or whether to roll them back if an exception occurs.

Exception handling plays a critical role when modifying object tables through PL/SQL programs. If an exception occurs during execution of a PL/SQL block, control immediately transfers to the exception section of the block. Once an exception is raised, normal execution cannot resume within that same block.

This behavior ensures that unexpected errors do not allow partial or inconsistent data modifications to occur. Instead, developers can respond to the error by logging diagnostic information, rolling back transactions, or invoking corrective logic.

When procedures perform multiple DML operations across different tables, proper exception handling ensures that the database remains in a consistent state. Oracle’s transactional model therefore provides both reliability and data integrity when modifying object tables.

In the lessons that follow, we will explore each type of DML operation—INSERT, UPDATE, and DELETE—in detail, focusing on how they apply specifically to Oracle object tables and object-relational structures.

[1] DML – Data Manipulation Language: SQL statements used to modify table data, including INSERT, UPDATE, and DELETE.