Oracle 26ai Object Types and Object Tables

Oracle AI Database 26ai supports an object-relational model^[3]: SQL object types and collections extend the relational database rather than replace it. This lesson introduces the vocabulary and query patterns you will use throughout the module. You will distinguish a reusable type definition from a particular object value, compare object columns with object tables, and see how SQL retrieves attributes, complete objects, and collection elements.

Imagine a customer application that represents an address as street, city, and postal code. You can store those values in ordinary columns, group them in an Oracle object type, or expose existing relational data through an object-oriented interface. Each choice changes the contract between database and application. A familiar application class is a starting point for discussion, but the database design still needs explicit keys, integrity rules, access patterns, and operational support.

The translation between application object graphs and relational data is often called impedance mismatch^[1]. Object types can make selected structures easier to exchange and reuse, but they do not remove every mapping decision. An address value, a shared customer identity, and a collection of order items have different lifecycles. Model those differences before choosing a storage representation.

Module objectives

By the end of this module, you should be able to:

1. Explain reasons to choose Oracle objects or ordinary relational tables.
2. Distinguish object types, instances, attributes, methods, row objects, and column objects.
3. Describe the SQL techniques used to query object tables.
4. Choose suitable collections, including variable-size arrays, or VARRAYs.
5. Explain how nested tables participate in SQL queries.
6. Write SQL that retrieves object-table data.

Established features, current terminology

Oracle8 introduced Oracle's object-relational capabilities in 1997; Oracle8i subsequently expanded them. They are established database features, not inventions of the current release. This lesson uses the Oracle 26ai documentation and current product name, while concentrating on concepts that remain useful across release changes. Historical code should still be reviewed for supported syntax, client behavior, and deployment requirements.

A SQL object type is a schema-level user-defined type, also described as an abstract data type, or ADT. Its attributes describe the values an instance contains; optional methods describe operations available on that instance or type. The type specification defines the interface. A type body supplies implementations for declared methods. Defining a type alone does not insert business data into a table.

An object becomes a persistent object^[2] when its state is stored in database tables. A variable holding an object during a PL/SQL call has a different lifetime from a stored row. This distinction helps prevent a common misunderstanding: creating a database type does not automatically persist every similarly named Java or application object.

A reusable object type and a relational table

The following examples are illustrative scripts checked against Oracle documentation, not transcripts of execution on an Oracle instance. Run them in order in a disposable training schema with the required CREATE TYPE and CREATE TABLE privileges and tablespace quota. The names use an m2_ prefix to reduce accidental collisions. Oracle DDL commits transactions; do not experiment in a schema containing unfinished business work.

This address type has three attributes and one formatting method. The method does not validate an address, verify postal delivery, or enforce a country's addressing rules. Keeping that purpose narrow makes the interface easy to understand.

CREATE TYPE m2_address_t AS OBJECT (
  street      VARCHAR2(100),
  city        VARCHAR2(50),
  postal_code VARCHAR2(20),
  MEMBER FUNCTION label_text RETURN VARCHAR2
);
/

CREATE TYPE BODY m2_address_t AS
  MEMBER FUNCTION label_text RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.street || ', ' || SELF.city || ' ' || SELF.postal_code;
  END label_text;
END;
/

In SQL*Plus or SQL Developer's script mode, the slash submits the completed type or PL/SQL definition. It is a client script command, not part of the SQL statement sent through every driver. Check the client's execution mode before copying scripts into an API. The specification and body must compile successfully before the method can be used.

CREATE TABLE m2_customers (
  customer_id   NUMBER PRIMARY KEY,
  customer_name VARCHAR2(100) NOT NULL,
  address       m2_address_t
);

INSERT INTO m2_customers (customer_id, customer_name, address)
VALUES (
  1001,
  'Acme Training',
  m2_address_t('123 Main Street', 'San Francisco', '94105')
);

SELECT c.customer_name,
       c.address.city AS city,
       c.address.label_text() AS address_label
FROM m2_customers c
WHERE c.customer_id = 1001;

m2_customers remains a relational table. Its address column contains a column object. The constructor call supplies attribute values in their declared order. The query uses the table alias c to qualify an attribute path and a method call; consistent aliases are particularly useful when SQL names and object attributes might otherwise be ambiguous.

For the inserted example, the city expression denotes San Francisco and the formatting method produces the combined address text. Those are expected values derived from the script. A client may display column headings and object values differently, so judge the returned data rather than requiring a particular screenshot format.

Adding a method does not force every writer to call it. A validation method can be useful to a controlled API, but callers may still insert or update values through another path. Define database constraints where supported and design write privileges and application interfaces deliberately. A method name alone is not an integrity guarantee.

An object table and whole-object retrieval

An object table has an object type as its row definition. Each row is a row object, instead of one object-valued column among other columns. The next example needs no type body because it declares attributes without methods.

CREATE TYPE m2_contact_t AS OBJECT (
  contact_id NUMBER,
  full_name  VARCHAR2(100)
);
/

CREATE TABLE m2_contacts OF m2_contact_t (
  contact_id PRIMARY KEY
);

INSERT INTO m2_contacts
VALUES (m2_contact_t(101, 'Asha Patel'));

SELECT c.contact_id, c.full_name
FROM m2_contacts c
WHERE c.contact_id = 101;

SELECT VALUE(c) AS contact_object
FROM m2_contacts c
WHERE c.contact_id = 101;

SELECT REF(c) AS contact_reference
FROM m2_contacts c
WHERE c.contact_id = 101;

These queries answer different questions. The first projects ordinary scalar attributes. VALUE(c) returns the complete object value. REF(c) returns a reference to the row object; it does not return a formatted contact or copy the contact's name. DEREF retrieves the object referred to by a reference. Choose the expression according to the interface consuming the result.

Object tables use object identifiers, or OIDs, for referenceable row identity. System-generated OIDs are the default, and primary-key-based OIDs are an alternative selected explicitly when creating the table. The primary-key constraint in this example does not change that default. Business keys, OIDs, and the displayed bytes of a reference serve different purposes; application code should not depend on an OID's internal representation.

A column object such as the customer's address has no independent row-object identity. If several entities must refer to one shared object with its own lifecycle, evaluate an object table and references. Also compare that design with a conventional table and foreign keys. A reference-based model needs deliberate integrity rules and handling for references whose targets become unavailable.

Using the Oracle 26ai documentation correctly

Oracle 26ai supports these object and collection patterns. Its SQL language also supports BOOLEAN, so older blanket statements that Boolean values exist only in PL/SQL are no longer suitable. This lesson uses a character-returning method to keep the demonstration focused. If your deployment targets another release or driver, check its actual datatype support.

For syntax, use the 26ai CREATE TYPE reference and the collection operations chapter. The broader Object-Relational Developer's Guide explains the model. A release label is not evidence that a particular method became faster or that every complex datatype supports every security feature.

When reviewing alternatives, start with the required interface. A document API, relational reporting workload, and graph traversal workload ask different questions of the same business data. Object types may suit a stable, typed database contract. They should be compared with alternatives using concrete requirements and prototypes, rather than selected merely because an application language supports classes.

Design and operating decisions

Before adopting a SQL object schema, write down who owns each value, how it changes, and which clients must consume it. An address copied into a historical order may intentionally be a value snapshot. A customer account shared by many transactions has independent identity. Treating both as interchangeable objects can obscure update and audit requirements.

Use representative queries to compare designs. Nested-table storage can use indexes; unnesting does not make indexing universally unavailable. Examine execution plans, collection sizes, predicates, and storage-table access before drawing performance conclusions. A simple example demonstrates syntax, not a throughput guarantee. Include reporting and batch operations in the comparison, rather than evaluating only one application's read path.

Plan type changes as schema changes. Tables, other types, and program units can depend on a type's definition. Review dependency impact and Oracle's type-evolution rules before altering a used type. Test supported changes with representative data and clients, and preserve a recoverable deployment path. Replacing definitions casually is different from designing a controlled migration.

Also verify the client contract. A tool that handles scalar columns may need explicit mapping for object values or collections. Do not assume an ORM automatically maps Oracle object types to application classes. A projection such as the contact query can provide a simpler reporting interface while keeping the underlying type available for other consumers.

PL/SQL exceptions and diagnostic objects

The same vocabulary needs care in error handling. A SQL object with attributes such as error code, message, and context can carry diagnostic data. It is not a native PL/SQL exception and cannot be passed to RAISE as though it were a Java throwable. PL/SQL exceptions control error propagation; an optional diagnostic object is data captured by a handler.

This illustrative block uses the earlier contact table. The sample has no contact 999999, so the query is expected to raise NO_DATA_FOUND. The handler prints diagnostic information and re-raises the original exception:

SET SERVEROUTPUT ON
DECLARE
  l_contact m2_contact_t;
BEGIN
  SELECT VALUE(c) INTO l_contact
  FROM m2_contacts c
  WHERE c.contact_id = 999999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Contact lookup failed: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;
/

DBMS_OUTPUT is a demonstration channel, not durable production logging. SQLCODE, SQLERRM, and the DBMS_UTILITY diagnostic functions provide native error information. A bare RAISE inside the handler propagates the current exception. Consult Oracle's exception-raising reference when designing application error handling.

For the next lesson, focus on three distinctions: a type defines a contract, an instance supplies values, and its storage determines whether it is a column object or a row object. Those distinctions make the later SQL query techniques easier to reason about.

[1] Impedance mismatch: Differences between application object models and relational data representations that require deliberate mapping.

[2] Persistent object: An object value whose state is stored in the database beyond one program invocation.

[3] Object-relational: An extension of the relational model with typed objects, references, and collections, while retaining SQL and relational operations.