Oracle 26ai Object Terms: From Types to Stored Instances

1. Object types define structure and behavior

An object type is similar to a class as a teaching analogy: it describes what instances contain and what operations they can perform. Oracle defines a SQL object type as a schema object using DDL. The definition can be used by multiple programs with the appropriate privileges. It is not a local record declaration hidden inside one anonymous PL/SQL block.

The following specification matches the first diagram. Use a practice schema with the required privileges and names that are not already in use. The examples build on one another in the order presented. Slash terminators are intended for a SQL*Plus-style script runner; other clients may submit each statement differently. These are illustrative examples, not a report of database execution.

CREATE TYPE address_t AS OBJECT (
  street VARCHAR2(120),
  city   VARCHAR2(60),
  MEMBER FUNCTION get_city RETURN VARCHAR2
);
/

address_t is the type name. street and city are its attributes. The declared function is a method, whose implementation comes later. This definition stores metadata describing the type; it does not store an address such as 10 Oak St in Boston. Those values belong to an instance.

Methods are optional. A type used mainly to carry structured values may have attributes without application-defined methods. Also, the class analogy has limits: Oracle SQL object attributes are publicly accessible to programs that can use the object. Adding a method does not establish private fields or force every assignment through that method. Validation and access control must be designed explicitly.

2. Attributes can represent scalar and composite values

Each attribute has one declared data type. Scalar examples include numbers, character strings, and timestamps. A composite attribute can itself contain an object value or a collection. This allows an application to model a customer as a structured value while keeping the types of its components explicit.

In the second diagram, full_address uses address_t. The customer therefore contains an address value rather than just an untyped block of text. Its phone_list attribute uses a separate collection type. Define that collection before defining the customer type that depends on it:

CREATE TYPE phone_list_t AS VARRAY(3) OF VARCHAR2(25);
/

CREATE TYPE customer_t AS OBJECT (
  cust_id         NUMBER,
  first_name      VARCHAR2(50),
  last_name       VARCHAR2(50),
  full_address    address_t,
  phone_list      phone_list_t,
  last_update     TIMESTAMP,
  updated_by_user VARCHAR2(128)
);
/

The seven attributes describe the shape of a customer value. Names such as cust_id or last_update do not create rules automatically. The first becomes a required unique identifier when the table's primary-key constraint is defined. The timestamp changes only when application code, a trigger, or another explicit mechanism sets it. Similarly, a character field holding a user name is not automatically an authenticated audit trail.

The example collection can hold up to three phone strings. A particular initialized collection can contain zero, one, two, or three elements. The maximum is a teaching choice, not a general recommendation for every customer system. A VARRAY preserves its element positions, whereas a SQL query still needs an appropriate ORDER BY when result ordering matters. Collections can be accessed using SQL features as well as PL/SQL operations.

Embedding an address demonstrates composition: a customer has an address value. It does not demonstrate inheritance. Inheritance uses a declared supertype and subtype relationship; composition uses an attribute whose type is another type. The address card in the composition diagram shows its attributes only. It still has the get_city method declared in the first figure.

3. Methods implement operations on objects

A method declaration states its name, parameters, and result where applicable. For a PL/SQL implementation, a type body supplies the executable code. Separating the specification and body lets programs use the public interface while the body defines how the operation works.

CREATE TYPE BODY address_t AS
  MEMBER FUNCTION get_city RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.city;
  END;
END;
/

SELF identifies the instance invoking the member method. If the instance contains Boston in its city attribute, the function returns Boston. Another instance containing Denver returns Denver through the same implementation. The method belongs to the type, while the data used for a particular call belongs to that instance.

Here is a complete anonymous block constructing a value and calling the method. In SQL*Plus or SQLcl, enable output before running it. A worksheet in another client may require enabling its DBMS Output panel instead.

SET SERVEROUTPUT ON

DECLARE
  a address_t := address_t('10 Oak St', 'Boston');
BEGIN
  DBMS_OUTPUT.PUT_LINE(a.get_city());
END;
/

The expected output is Boston. The constructor address_t(...) initializes the two attributes in declaration order; a.get_city() invokes behavior on the resulting value. A default attribute-value constructor is supplied for this type. Applications can also define constructors for more specialized initialization requirements.

Not all methods have the same purpose. Member methods operate on instances, static methods provide operations associated with the type without a particular instance's SELF, and constructors create values. Oracle also supports specialized comparison methods. This introductory example uses only a member function, so its terminology should not be taken as a requirement that every method reads or modifies instance data.

PL/SQL is a common implementation language, but Oracle also supports other method implementations through the appropriate language facilities. Choosing those facilities requires additional deployment and security decisions. The essential distinction here is between declaring an operation and implementing it, not between database methods and ordinary application code.

Object values must be initialized

A declaration without construction is different from a constructed value whose attributes happen to be null. For example, a address_t; initially represents an atomically null object. Reading an attribute of that value evaluates to null, but assigning an individual attribute requires an initialized object. Construct the value before assigning its components or invoking its member method.

DECLARE
  a address_t;
BEGIN
  a := address_t(NULL, NULL);
  a.street := '10 Oak St';
  a.city := 'Boston';
  DBMS_OUTPUT.PUT_LINE(a.get_city());
END;
/

After construction, the two attributes can be assigned independently. Before those assignments, the initialized object exists with null attribute values. This is useful when reading errors: a missing object and a missing city value are different conditions. A null collection likewise differs from an initialized empty collection such as phone_list_t().

4. Instances can be used in variables and tables

The fourth diagram shows three places for values of the same type. A PL/SQL variable holds a temporary working value. An object table holds row objects. An ordinary relational table can contain an object-valued column alongside separate scalar columns. Defining the type does not force a particular storage choice.

CREATE TABLE customer_obj_table OF customer_t (
  CONSTRAINT customer_obj_pk PRIMARY KEY (cust_id)
);

INSERT INTO customer_obj_table VALUES (
  customer_t(
    101, 'Avery', 'Lee',
    address_t('10 Oak St', 'Boston'),
    phone_list_t('617-555-0100'),
    TIMESTAMP '2026-10-09 09:00:00',
    'DEMO_USER'
  )
);

COMMIT;

SELECT c.cust_id, c.first_name, c.full_address.city
FROM customer_obj_table c
WHERE c.cust_id = 101;

SELECT VALUE(c)
FROM customer_obj_table c
WHERE c.cust_id = 101;

A relational table can contain a column object

A different design stores an address value in one column of an ordinary table. Other columns remain independent table columns, as the fourth diagram illustrates:

CREATE TABLE contacts (
  contact_id NUMBER PRIMARY KEY,
  address    address_t
);

INSERT INTO contacts VALUES (
  101, address_t('10 Oak St', 'Boston')
);
COMMIT;

SELECT c.contact_id, c.address.city, c.address.get_city()
FROM contacts c
WHERE c.contact_id = 101;

Here contact_id belongs to the table, while street and city belong to its address value. The embedded address is a column object, not an independently referenceable row object with its own OID. A customer's embedded address has the same distinction. This is why “an object table can have any extra attributes” is misleading: separate ordinary columns are a feature of this relational-table design.

Choose storage according to relationships, lifecycle, queries, and integrity requirements. Object types can express useful structured interfaces, but they do not guarantee faster queries or better normalization. A conventional relational design can represent the same customer domain. Measure important workloads and use constraints to enforce the rules that matter.

Check your understanding

When reading an unfamiliar example, identify the definition, the value, and the storage separately. In this lesson, address_t is a type; a is a variable holding an instance; city is an attribute; get_city is a method; and customer_obj_table stores row objects. full_address demonstrates composition, while phone_list demonstrates a collection-valued attribute.

You should be able to explain why CREATE TYPE creates no customer rows, why a method body needs its declaration, and why VALUE(c) returns something different from a single attribute projection. You should also distinguish an initialized object with null attributes from an atomically null variable. These distinctions prepare you for the next lesson, which develops the techniques for querying object tables.

For details, consult Oracle's object-relational model overview, object methods reference, collection types guide, and PL/SQL execution-rights documentation.