Write SQL to Delete Rows from an Object Table

In Oracle PL/SQL, deleting rows from an object table follows the same transactional rules as deleting rows from a relational table. However, object tables introduce additional considerations such as object identifiers (OIDs), object references (REF), and dependency relationships with other schema objects. This lesson demonstrates how to write DELETE statements against object tables and how those statements behave inside PL/SQL blocks under Oracle 23ai.

At its simplest, deleting a single row from an object table uses a standard DELETE statement with a precise predicate. For example:

DELETE FROM product_obj_table
WHERE product_id = 34;

This statement removes the row whose product_id equals 34. As with all DELETE operations, accuracy depends on the WHERE clause. Without it, Oracle removes all rows from the table, which is rarely the intended result in production environments.

Object tables often store relationships using REF values rather than foreign keys. In those cases, deletion criteria frequently depend on subqueries. For example:

DELETE FROM product_obj_table
WHERE package_id = (
  SELECT REF(pot)
  FROM product_obj_table pot
  WHERE pot.product_id = 39
);

Here, the row is deleted by matching the package_id reference to a derived REF value from another object instance.

Similarly, multiple rows can be deleted when the predicate evaluates to multiple matches. For example:

DELETE FROM sale_header
WHERE cust_ref = (
  SELECT REF(cot)
  FROM customer_obj_table cot
  WHERE cot.cust_id = 26
);

In this case, Oracle removes all rows from SALE_HEADER whose CUST_REF corresponds to the selected object reference from CUSTOMER_OBJ_TABLE.

There are two primary approaches to deleting rows from object tables:

• Using a direct predicate in the WHERE clause
• Using a subquery to resolve object references or derived conditions

DELETE FROM product_obj_table
WHERE pet_flag = 'N';

DELETE FROM pet_care_log_obj_table
WHERE product_id = (
  SELECT REF(pot)
  FROM product_obj_table pot
  WHERE pot.product_id = 11
);

When working with object tables in Oracle 23ai, developers frequently combine DELETE logic with procedural safeguards such as cursor loops, exception handling, and logging to ensure consistency in larger workflows.

PL/SQL is a precompiled language environment. During compilation, Oracle validates dependencies against schema objects such as tables, views, and synonyms. These dependency checks apply primarily to object definitions—not the data manipulated by DML.

As a result:

• DML operations like DELETE execute safely inside PL/SQL blocks.
• DDL operations (CREATE, DROP, ALTER) are not permitted directly in static PL/SQL because they change dependencies at runtime.

The following example demonstrates a DELETE operation inside a PL/SQL block:

SET SERVEROUTPUT ON
DECLARE
  v_author AUTHORS%ROWTYPE;
BEGIN
  SELECT *
  INTO v_author
  FROM authors
  WHERE id = 54;

  DELETE FROM authors
  WHERE id = v_author.id;

  DBMS_OUTPUT.PUT_LINE('Author ' || v_author.first_name || ' deleted');

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END;
/

This block demonstrates key transactional principles:

• DML changes require an explicit COMMIT to become permanent.
• ROLLBACK and SAVEPOINT allow controlled recovery.
• Exception handling ensures predictable behavior during runtime errors.