Oracle Abstract Data Types (ADTs)

Abstract Data Types (ADTs) extend the power of the relational model by allowing developers to create user-defined types that encapsulate both data and the operations that manipulate it. This approach blends the principles of object-oriented design with the relational model, enabling more natural representation of complex entities within a database.

Background and Evolution

While Oracle popularized ADTs, it was not the first to introduce them. Early research databases such as Postgres (1986) and INGRES (early 1990s) pioneered user-defined types (UDTs), laying the groundwork for object-relational extensions.

Oracle introduced ADTs with Oracle8 (1997), offering developers the ability to create structured objects that could be stored as table columns. Later RDBMS products, including IBM DB2 (1999) and Microsoft SQL Server (2005), adopted similar concepts.

• Postgres (1986): First system to integrate user-defined types and functions.
• Oracle8 (1997): Introduced object-relational features and standardized ADTs.
• Oracle 23c: Continues support for ADTs with modernized type management and JSON/LOB integration.

Limitations of Oracle7

Earlier Oracle releases, such as Oracle7, offered only a limited set of intrinsic data types like CHAR, VARCHAR2, NUMBER, and DATE. Developers could not extend these types or group related attributes together, resulting in tables with many individual columns. For example, representing a customer’s address required multiple fields:

CREATE TABLE customer (
    customer_first_name    CHAR(40),
    customer_last_name     CHAR(40),
    customer_street        CHAR(40),
    customer_city          CHAR(40),
    customer_state         CHAR(2),
    customer_zip_code      NUMBER(9)
);

To retrieve address information, developers needed to list every individual column in a query. This structure was functional but not modular or reusable.

Grouping Columns into Abstract Data Types

With ADTs, related columns can be encapsulated into reusable user-defined types, simplifying both schema design and query syntax. For instance:

CREATE TYPE name_type AS OBJECT (
    first_name  CHAR(40),
    last_name   CHAR(40)
);

CREATE TYPE full_address_type AS OBJECT (
    street_address CHAR(40),
    city           CHAR(40),
    state          CHAR(2),
    zip_code       NUMBER(9)
);

CREATE TABLE customer (
    cust_name     name_type,
    cust_address  full_address_type
);

By grouping related attributes into objects, queries become simpler and schemas more expressive:

SELECT cust_name, cust_address
FROM customer;

Advantages of Abstract Data Types

• Improved readability: Related fields are logically grouped and referenced as a single unit.
• Reusability: Once defined, types can be reused across multiple tables and PL/SQL programs.
• Encapsulation: ADTs can include methods that manipulate their data, similar to class methods in object-oriented programming.
• Performance benefits: Reduces the need for costly joins by embedding related data in a single structure.
• Compatibility: Fully supported in Oracle 12c–23c, and integrated with modern features like JSON and XML data types.

Each ADT consists of attributes (the data structure) and methods (the procedures that operate on it). Once stored in the data dictionary, these objects can be used as table columns or PL/SQL variables.

• In the static data dictionary view USER_OBJECTS, the OBJECT_TYPE of an ADT is TYPE.
• In USER_TYPES, the TYPECODE for an ADT is OBJECT.

CREATE TYPE Statement Overview

The CREATE TYPE statement defines a user-defined data type in Oracle. It can be used to create:

1. An abstract data type (object type)
2. A standalone VARRAY (variable-size array) type
3. A standalone nested table type
4. An incomplete object type (for forward references)

An incomplete type is declared by name but defined later. This allows circular references between types. Before an incomplete type can be instantiated or used in a table definition, it must be fully specified.

After the type specification, the CREATE TYPE BODY statement can define the methods that implement the type’s behavior.

• If the type includes only attributes (no methods), a type body is unnecessary.
• For SQLJ object types, implementation is provided as a Java class—no type body is required.
• Standalone stored types created with CREATE TYPE differ from PL/SQL package-level types, which exist only in memory.
• CREATE TYPE supports nested tables and VARRAYs, but not associative arrays (which can only exist in PL/SQL blocks).

In modern Oracle versions (19c–23c), these object types integrate with REST-enabled tables and JSON-based storage, extending ADTs into hybrid relational–object structures.
The next lesson demonstrates how to embed an Object Identifier (OID) in a table and reference it within other objects.

ADT - Exercise