Lesson 3	Inserting a row into an object table
Objective	Write PL/SQL to insert a row into an Object Table

Inserting a Row into an Object Table

This lesson covers the techniques for writing INSERT statements against Oracle object tables. Object tables store rows as typed object instances rather than as flat collections of scalar values, which introduces syntax requirements that differ from standard relational table inserts. You will also see how Oracle's PL/SQL record-level insert extension simplifies certain insert patterns. For this module, NULL is inserted into columns based on VARRAYs or nested tables — a dedicated module covers DML against those collection types.

Two Categories of Object Table INSERT

Oracle object table inserts fall into two categories based on the structure of the object type:

Object tables with associated object types — the table stores complete object instances. Each row is an instance of a user-defined object type, and the INSERT statement must call the object type constructor to supply attribute values.
Object tables with references to other objects — the table contains a REF column that stores a typed pointer to a row in another object table. The INSERT statement must use a SELECT subquery with the REF() function to obtain and store the reference value.

The key difference is data storage. Object tables with associated types store the full object data inline. Reference-based tables store only a pointer to an object that lives in another table, reducing redundancy when the same object is referenced from multiple places.

Inserting into Object Tables with Associated Object Types

When a column is defined using a user-defined object type, the INSERT statement must call the object type constructor — a method that shares the type name and accepts attribute values in positional order. Oracle does not accept a bare list of scalar values without the constructor call.

The CUSTOMER_OBJ_TABLE table has a FULL_ADDRESS column based on the ADDRESS_TYPE object type. The following example inserts a complete customer record using the outer customer_type constructor wrapping an inner Address_type constructor call:

INSERT INTO customer_obj_table
VALUES (customer_type(26, 'Lester', 'Lee',
        Address_type('197 Betty Lane', NULL, 'Hollywood',
        'CA', '10293'),
        NULL, DATE '2026-03-16', 'HENRY'));

The nested constructor call for Address_type supplies all address attributes including the optional second address line as NULL. The outer constructor call supplies the remaining customer attributes. The DATE '2026-03-16' literal uses ISO 8601 format and is independent of the session NLS_DATE_FORMAT setting, making it portable across all Oracle 23ai environments.

Variation 1 — INSERT with Object Type Constructor

1) Insert into object tables, which have columns with associated object types. — Insert into object tables that have columns with associated object types. The object type name and its attributes must be specified within the `INSERT` statement using the constructor method.

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

Example of an INSERT statement for an object table with an associated object type. The Address_type constructor supplies all address attributes. The DATE '2026-03-16' literal is session-format independent.

Inserting into Object Tables with References to Other Objects

When an object table contains a REF column, the INSERT statement must retrieve the reference using the REF() function inside a SELECT subquery. The REF() function accepts a table alias and returns the object reference for the matched row. This reference is then stored in the REF column of the target table.

The PET_CARE_LOG_OBJ_TABLE is based on PET_CARE_OBJ_TYPE, which contains a reference to PRODUCT_TYPE stored in PRODUCT_OBJ_TABLE. The following INSERT statement retrieves the reference for a specific product and inserts it along with the remaining column values:

INSERT INTO pet_care_log_obj_table
SELECT REF(p),
       TIMESTAMP '2026-03-16 12:30:00',
       'JANET',
       'New puppy needs shots.',
       TIMESTAMP '2026-03-17 13:30:00'
FROM   product_obj_table p
WHERE  p.product_id = 22;

The table alias p is required — the REF() function must receive a table alias, not a table name directly. All column values for the target table must be supplied, even those that are NULL, because the SELECT form of INSERT does not support partial column lists when using object reference retrieval.

Variation 2 — INSERT with SELECT and REF()

3) Insert into object tables with reference to other objects can be done as a sub-query for the reference column only, then listing the data for the other columns — Insert into object tables that contain references to other objects. Use a `SELECT` statement with `REF()` to retrieve the object reference, then supply values for the remaining columns. All column values must be provided, even if `NULL`.

INSERT INTO sale_header
SELECT 636,
       REF(c),
       117.3,
       .87,
       210.47,
       2.3,
       TIMESTAMP '2026-03-16 15:03:00',
       detail_table()
FROM   customer_obj_table c
WHERE  c.cust_id = 1;

INSERT using SELECT REF() to retrieve an object reference from customer_obj_table. The TIMESTAMP literal replaces the legacy to_date() call. The detail_table() constructor initializes the nested table column as an empty collection.

Variation 3 — INSERT with Subquery for REF Column Only

5) Insert into object tables with reference to other objects can be done as a sub-query for the reference column only, then listing the data for the other columns — A subquery can be used for the `REF` column only, with scalar values supplied for the remaining columns directly in the `VALUES` clause.

INSERT INTO pet_care_log_obj_table
VALUES (
  (SELECT REF(p) FROM product_obj_table p WHERE p.product_id = 22),
  TIMESTAMP '2026-03-16 12:30:00',
  'JANET',
  'New puppy is allergic to PUPPY CHOW.',
  TIMESTAMP '2026-03-17 13:30:00'
);

The REF column value is supplied via an inline subquery inside the VALUES clause. The remaining columns receive scalar values directly. Both TIMESTAMP literals use ISO 8601 format and are NLS_DATE_FORMAT independent.

Record-Level INSERT in PL/SQL

From Oracle 9i Database Release 2 onward, PL/SQL supports a record-level INSERT extension that allows a PL/SQL record variable to be used directly in the VALUES clause instead of a column list. This simplifies inserts where the record structure already matches the target table row exactly:

-- Declare a record matching the table structure
DECLARE
  v_rec rain_forest_history%ROWTYPE;
BEGIN
  v_rec.country_code   := 'BR';
  v_rec.analysis_date  := DATE '2026-03-16';
  v_rec.size_in_acres  := 1250000;
  v_rec.species_lost   := 47;

  INSERT INTO rain_forest_history
    (country_code, analysis_date, size_in_acres, species_lost)
  VALUES v_rec;

  COMMIT;
END;
/

The record variable must have a field for every column named in the column list, with compatible data types. Fields corresponding to columns with NOT NULL constraints must not be NULL at the time of the insert. This form of insert is a PL/SQL extension and is not available in plain SQL — it must execute within a PL/SQL block.

INSERT Statement Syntax Reference

Insert Into SQL — The `insert_into_clause` syntax diagram. The `values_clause` accepts either a column value list or, in PL/SQL contexts, a record variable name. The `t_alias` provides an alias for the target table expression.

The key semantic elements of the insert_into_clause are:

dml_table_expression_clause — typically the target table name, but can also be a view or a subquery that resolves to a modifiable result set.
t_alias — an alias for the table expression, required when using object-oriented constructs such as VALUE() or when the target is a complex expression.
values_clause record — in PL/SQL, the name of a record variable of type RECORD or %ROWTYPE. The record must represent a row of the target table — every named column must have a corresponding field with a compatible data type.

Inserting Records into a VARRAY Column

When a table column is defined using a VARRAY type, you must use the VARRAY constructor method to supply all elements in a single call. Oracle automatically generates a constructor method for every user-defined type, including VARRAYs, that shares the type name and accepts the element values as positional arguments.

The BORROWER table has two columns: NAME and TOOLS, where TOOLS is a VARRAY using the TOOLS_VA datatype. The following insert supplies three tool values using the constructor:

INSERT INTO borrower
VALUES ('JED HOPKINS',
        TOOLS_VA('HAMMER', 'SLEDGE', 'AX'));

If the VARRAY element type is itself a user-defined object type, the constructor calls must be nested — the outer VARRAY constructor wraps individual object type constructors for each element. The entire VARRAY value must be supplied as a single unit; individual elements cannot be inserted or updated independently. To modify a single element, the entire VARRAY column must be replaced using an UPDATE statement with a new constructor call containing all elements.

Summary

Inserting rows into Oracle object tables requires constructor method calls for object type columns, the REF() function with a table alias for reference columns, and TIMESTAMP or DATE literals instead of to_date() for portable date handling. Three insert patterns cover the common scenarios: constructor-only for fully inline object values, SELECT REF() for reference retrieval across all columns, and subquery-for-REF with scalar values for the remaining columns. PL/SQL's record-level insert extension further simplifies inserts when a %ROWTYPE record already matches the target table structure. The next lesson covers writing INSERT statements using subqueries.