Four Oracle Data Constructs with a Description of Nested Tables

Oracle’s object-relational feature set includes several “data constructs” that work together: defining custom types, identifying objects, referencing objects, and dereferencing those references. These constructs remain supported in Oracle 23ai, even though many modern application-facing designs now prefer JSON and JSON Relational Duality Views for document-style modeling.

This lesson describes four constructs—ADT, OID, DEREF, and CREATE TYPE—and then connects them to one of Oracle’s classic collection types: the nested table.

CREATE TYPE is the SQL statement used to define user-defined types in the database. It can define:

• Object types (ADTs): structured records that can be stored in columns and tables
• Collection types: TABLE OF ... (nested table types) and VARRAY(...) (varray types)

-- Object type (ADT)
CREATE TYPE emp_ot AS OBJECT (
  emp_name  VARCHAR2(30),
  emp_phone VARCHAR2(20),
  sal       NUMBER(9,2)
);
/

-- Collection type (nested table) of the object type
CREATE TYPE emp_nt AS TABLE OF emp_ot;
/

In Oracle terminology, an ADT is an object type created with CREATE TYPE ... AS OBJECT. It encapsulates a set of attributes and can optionally include methods. In practice, ADTs are used when you want a strongly-typed, reusable structure that can be:

• Stored in a column (as an object column)
• Used as the element type of a collection (nested table or varray)
• Referenced via REF in object-relational designs

-- Using an ADT as a column type (object column)
CREATE TABLE emp_obj_table (
  emp_id  NUMBER PRIMARY KEY,
  emp     emp_ot
);

An OID is an object identifier used to uniquely identify object instances in object-relational features. OIDs appear when you use object tables and REF relationships. Conceptually, an OID plays a role similar to a surrogate key for object instances.

In many modern schemas you will not design around OIDs directly; you will use relational primary keys. However, OIDs remain part of the object-relational machinery and still matter when maintaining legacy object tables or REF-based designs.

The DEREF operator is used to dereference a REF and return the referenced object value. This is relevant only when you are using object tables and REF columns.

-- Example pattern (illustrative): REF column + dereference
-- (Object-relational designs commonly use object tables and REF relationships.)

-- Suppose dept_ot is an object type and dept_obj_table is an object table.
-- A table may store a REF to a department object:
--   dept_ref REF dept_ot SCOPE IS dept_obj_table

-- You can dereference the REF to access object attributes:
-- SELECT DEREF(e.dept_ref).dept_name
-- FROM   emp_ref_table e;

Practical guidance: if you are not using object tables and REF columns, you generally do not need DEREF. In relational modeling, you typically use foreign keys and joins instead.

A nested table is an Oracle collection type that can hold an unbounded number of elements. Nested tables can be used in two common ways:

• PL/SQL collections for procedural logic (dynamic sizing, easy element access, iteration)
• SQL storage as a column type where Oracle persists the collection in a separate store table and exposes it with TABLE()

When you fetch a nested table into PL/SQL, Oracle assigns consecutive indexes starting at 1 for element access (for example, names(1)). Do not assume index stability across store/retrieve cycles when the collection is persisted in SQL storage.

Example: Nested table local type in PL/SQL

DECLARE
  TYPE roster_nt IS TABLE OF VARCHAR2(15);  -- local nested table type
  names roster_nt := roster_nt('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

  PROCEDURE print_names(p_heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(p_heading);

    IF names IS NULL OR names.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('[empty]');
    ELSE
      FOR i IN names.FIRST .. names.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(names(i));
      END LOOP;
    END IF;

    DBMS_OUTPUT.PUT_LINE('---');
  END;
BEGIN
  print_names('Initial Values:');

  names(3) := 'P Perez';
  print_names('Current Values:');

  names := roster_nt('A Jansen', 'B Gupta');
  print_names('Current Values:');
END;
/

ORA-06533 occurs when PL/SQL references an element index that does not exist in a collection. This is most often caused by:

1. Using an index greater than COUNT
2. Assuming an element exists after deleting elements or reconstructing the collection
3. Operating on an uninitialized (NULL) collection

Correct pattern: define a collection type and validate bounds

DECLARE
  TYPE num_va IS VARRAY(3) OF NUMBER;
  my_array num_va := num_va(10, 20, 30);
BEGIN
  -- Safe access check:
  IF my_array.COUNT >= 3 THEN
    DBMS_OUTPUT.PUT_LINE(my_array(3));
  END IF;

  -- This would raise ORA-06533 (index 4 does not exist):
  -- DBMS_OUTPUT.PUT_LINE(my_array(4));
END;
/

Defensive practice: check IS NULL, COUNT, and use FIRST/LAST when iterating over nested tables, especially after modifications that may change element availability.

• Use nested tables when you need an Oracle-native collection for PL/SQL workflows or when maintaining legacy object-relational schemas.
• Prefer relational tables + foreign keys for most normalized transactional designs.
• Prefer JSON (and Duality Views where appropriate) when your primary requirement is a document interface with relational storage.