PL/SQL Object Types: Understanding Object-Oriented Programming in Oracle

Introduction

Oracle PL/SQL supports object-oriented programming through object types, which allow developers to define reusable data structures that combine attributes and methods. Attributes represent an object's state, while methods define its behavior. This approach supports the development of modular and reusable database applications (Feuerstein & Pribyl, 2014; Oracle, n.d.-a).

An Oracle object type can contain attributes, member procedures, member functions, and other methods. Unlike a conventional PL/SQL record, an object type can be created at the database schema level and reused in different database objects and applications.

This article explains the main concepts of Oracle object types, including object creation, instantiation, constructors, member methods, comparison methods, inheritance, method overriding, and abstract object types.

1. Creating an Object Type

An object type is created using the CREATE TYPE statement. Its specification defines the attributes and methods that belong to the object. If the type includes methods, their implementations are generally provided in a separate type body using CREATE TYPE BODY (Oracle, n.d.-a).

Consider an address object containing several attributes:

CREATE OR REPLACE TYPE address_t AS OBJECT
(
    house_no VARCHAR2(10),
    street   VARCHAR2(30),
    city     VARCHAR2(20),
    state    VARCHAR2(30),
    pincode  VARCHAR2(10)
);
/

The address_t object type defines five attributes. Each attribute represents a property of an address.

An object type can also contain another object type as one of its attributes. For example, a customer object can contain an address object:

CREATE OR REPLACE TYPE customer_t AS OBJECT
(
    code       NUMBER(5),
    name       VARCHAR2(30),
    contact_no VARCHAR2(20),
    addr       address_t,
    MEMBER PROCEDURE display
);
/

In this example:

code, name, and contact_no are scalar attributes.
addr is an attribute whose data type is another object type, address_t.
display is a member procedure that defines an operation associated with the customer object.

The implementation of the member procedure is provided in the type body:

CREATE OR REPLACE TYPE BODY customer_t AS

    MEMBER PROCEDURE display IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Customer Code: ' || code);
        DBMS_OUTPUT.PUT_LINE('Customer Name: ' || name);
        DBMS_OUTPUT.PUT_LINE('Contact Number: ' || contact_no);
        DBMS_OUTPUT.PUT_LINE('City: ' || addr.city);
    END display;

END;
/

This demonstrates how an object can combine data and behavior in a single database type.

2. Instantiating an Object

Defining an object type creates its structure, but an object instance is needed to work with its attributes and methods.

Oracle provides a system-defined constructor for object types. The constructor has the same name as the object type and accepts values corresponding to its attributes. The dot (.) operator is used to access an object's attributes and methods (Oracle, n.d.-c).

The following example creates an instance of address_t:

DECLARE
    residence address_t;
BEGIN
    residence := address_t(
        '103A',
        'M.G. Road',
        'Jaipur',
        'Rajasthan',
        '201301'
    );

    DBMS_OUTPUT.PUT_LINE('House No: ' || residence.house_no);
    DBMS_OUTPUT.PUT_LINE('Street: ' || residence.street);
    DBMS_OUTPUT.PUT_LINE('City: ' || residence.city);
    DBMS_OUTPUT.PUT_LINE('State: ' || residence.state);
    DBMS_OUTPUT.PUT_LINE('Pincode: ' || residence.pincode);
END;
/

The variable residence represents an instance of the address_t object type. The constructor initializes its attributes, and dot notation allows the program to retrieve their values.

An object can also be instantiated with a nested object:

DECLARE
    customer customer_t;
BEGIN
    customer := customer_t(
        1001,
        'John Smith',
        '1234567890',
        address_t(
            '103A',
            'M.G. Road',
            'Jaipur',
            'Rajasthan',
            '201301'
        )
    );

    customer.display;
END;
/

This example demonstrates how object types can be composed to represent more complex business entities.

3. Member Methods

Member methods define the behavior of an object. They can be procedures or functions and are declared in the object type specification.

Their implementations are written in the corresponding type body using the CREATE TYPE BODY statement (Oracle, n.d.-c).

For example, the customer_t type contains a member procedure named display. Its implementation accesses the attributes of the current object and displays their values.

A member method can access the current object through the implicit SELF parameter. Oracle provides this parameter automatically, although developers can explicitly declare it when needed.

Constructors

A constructor is a function that creates and initializes a new object instance.

Oracle provides a system-defined constructor for object types with attributes. The constructor name is identical to the object type name.

For example:

residence := address_t(
    '103A',
    'M.G. Road',
    'Jaipur',
    'Rajasthan',
    '201301'
);

This statement creates a new address_t instance and initializes its attributes.

Oracle also supports user-defined constructors, which allow developers to implement customized object initialization logic (Oracle, n.d.-c).

4. Comparing Object Instances: MAP and ORDER Methods

Object instances may contain several attributes, so Oracle needs a defined comparison mechanism when developers want to use relational operators such as >, <, or = to order objects.

Oracle supports two comparison methods:

MAP method: Converts an object into a scalar value that represents its position in an ordering.
ORDER method: Compares the current object directly with another object of the same type.

An object type can define either a MAP method or an ORDER method, but not both (Oracle, n.d.-b).

4.1 MAP Method

A MAP method is a member function that returns a scalar value. Oracle uses the returned value to compare object instances.

For example, consider a rectangle object with two attributes: length and width.

We can define a MAP method that calculates the rectangle's diagonal:

CREATE OR REPLACE TYPE rectangle_t AS OBJECT
(
    length NUMBER,
    width  NUMBER,

    MEMBER FUNCTION enlarge(inc NUMBER)
        RETURN rectangle_t,

    MEMBER PROCEDURE display,

    MAP MEMBER FUNCTION measure
        RETURN NUMBER
);
/

The type body implements the methods:

CREATE OR REPLACE TYPE BODY rectangle_t AS

    MEMBER FUNCTION enlarge(inc NUMBER)
        RETURN rectangle_t IS
    BEGIN
        RETURN rectangle_t(
            SELF.length + inc,
            SELF.width + inc
        );
    END enlarge;

    MEMBER PROCEDURE display IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Length: ' || SELF.length);
        DBMS_OUTPUT.PUT_LINE('Width: ' || SELF.width);
    END display;

    MAP MEMBER FUNCTION measure
        RETURN NUMBER IS
    BEGIN
        RETURN SQRT(
            SELF.length * SELF.length +
            SELF.width * SELF.width
        );
    END measure;

END;
/

The measure function calculates the diagonal using the Pythagorean theorem.

The MAP method returns this value, allowing Oracle to compare rectangle instances according to their diagonal lengths.

The following example demonstrates its use:

DECLARE
    r1 rectangle_t;
    r2 rectangle_t;
    r3 rectangle_t;

    inc_factor NUMBER := 5;
BEGIN
    r1 := rectangle_t(3, 4);
    r2 := rectangle_t(5, 7);

    r3 := r1.enlarge(inc_factor);

    DBMS_OUTPUT.PUT_LINE('Enlarged Rectangle:');
    r3.display;

    DBMS_OUTPUT.PUT_LINE('Larger Rectangle:');

    IF r1 > r2 THEN
        r1.display;
    ELSE
        r2.display;
    END IF;
END;
/

Expected output:

Enlarged Rectangle:
Length: 8
Width: 9

Larger Rectangle:
Length: 5
Width: 7

The comparison r1 > r2 implicitly invokes the MAP method for both objects. Oracle compares their returned diagonal measurements rather than comparing their attributes individually.

Important: This example defines the comparison criterion as diagonal length. It does not compare rectangle area. The comparison method should reflect the business rule or mathematical property that is meaningful for the application.

4.2 ORDER Method

An ORDER method provides another way to compare object instances.

Unlike a MAP method, which converts an object into a scalar value, an ORDER method directly compares the current object (SELF) with another object passed as a parameter.

The function must return:

A negative number when the current object is smaller.
Zero when both objects are equal according to the comparison rule.
A positive number when the current object is greater.

An object type can define only one ORDER method, and it cannot define both MAP and ORDER methods (Oracle, n.d.-b).

The following example defines a rectangle type using an ORDER method:

CREATE OR REPLACE TYPE rectangle_order_t AS OBJECT
(
    length NUMBER,
    width  NUMBER,

    MEMBER PROCEDURE display,

    ORDER MEMBER FUNCTION compare_rectangle(
        r rectangle_order_t
    ) RETURN NUMBER
);
/

The type body implements the comparison logic:

CREATE OR REPLACE TYPE BODY rectangle_order_t AS

    MEMBER PROCEDURE display IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Length: ' || SELF.length);
        DBMS_OUTPUT.PUT_LINE('Width: ' || SELF.width);
    END display;

    ORDER MEMBER FUNCTION compare_rectangle(
        r rectangle_order_t
    ) RETURN NUMBER IS

        current_measure NUMBER;
        other_measure   NUMBER;

    BEGIN
        current_measure :=
            SELF.length * SELF.length +
            SELF.width * SELF.width;

        other_measure :=
            r.length * r.length +
            r.width * r.width;

        IF current_measure < other_measure THEN
            RETURN -1;

        ELSIF current_measure > other_measure THEN
            RETURN 1;

        ELSE
            RETURN 0;
        END IF;

    END compare_rectangle;

END;
/

The method compares the squared diagonal measurements. Using squared values avoids the need to calculate square roots.

The following block demonstrates the ORDER method:

DECLARE
    r1 rectangle_order_t;
    r2 rectangle_order_t;
BEGIN
    r1 := rectangle_order_t(23, 44);
    r2 := rectangle_order_t(15, 17);

    DBMS_OUTPUT.PUT_LINE('Rectangle 1:');
    r1.display;

    DBMS_OUTPUT.PUT_LINE('Rectangle 2:');
    r2.display;

    DBMS_OUTPUT.PUT_LINE('Larger Rectangle:');

    IF r1 > r2 THEN
        r1.display;
    ELSIF r1 < r2 THEN
        r2.display;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Both rectangles are equal.');
    END IF;
END;
/

The comparison operator automatically invokes the ORDER method.

In this implementation, two rectangles are considered equal when their squared diagonal measurements are equal, even if their individual dimensions differ. This illustrates why choosing an appropriate comparison rule is important.

4.3 MAP Versus ORDER: Which One Should You Use?

Both methods provide a way to define how Oracle compares object instances, but they work differently.

Feature	MAP Method	ORDER Method
Comparison approach	Maps an object to a scalar value	Directly compares two objects
Parameters	No explicit parameters	One object parameter
Return value	Scalar value	Negative, zero, or positive number
Typical use	Sorting and ordering many objects	Complex pairwise comparison
Performance	Generally more efficient for repeated comparisons	May require repeated method calls

Oracle recommends considering MAP methods when many object instances need to be sorted or compared. An ORDER method can be appropriate when the comparison logic is more complex and cannot easily be represented by a scalar value (Oracle, n.d.-b).

5. Inheritance in PL/SQL Object Types

Inheritance allows developers to create new object types based on existing object types.

The original type is called the supertype or parent type, while the derived type is called the subtype or child type.

A subtype inherits the attributes and methods of its parent type. It can also introduce additional attributes and methods or override inherited methods.

To support inheritance, the parent object type must be declared NOT FINAL. By default, object types are FINAL, meaning that they cannot be extended through inheritance (Oracle, n.d.-d).

5.1 Creating a Parent Object Type

Consider the following rectangle object type:

CREATE OR REPLACE TYPE rectangle_base_t AS OBJECT
(
    length NUMBER,
    width  NUMBER,

    MEMBER FUNCTION enlarge(inc NUMBER)
        RETURN rectangle_base_t,

    MEMBER PROCEDURE display
)
NOT FINAL;
/

The NOT FINAL clause allows other object types to inherit from rectangle_base_t.

The type body provides the implementation:

CREATE OR REPLACE TYPE BODY rectangle_base_t AS

    MEMBER FUNCTION enlarge(inc NUMBER)
        RETURN rectangle_base_t IS
    BEGIN
        RETURN rectangle_base_t(
            SELF.length + inc,
            SELF.width + inc
        );
    END enlarge;

    MEMBER PROCEDURE display IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Length: ' || SELF.length);
        DBMS_OUTPUT.PUT_LINE('Width: ' || SELF.width);
    END display;

END;
/

5.2 Creating a Child Object Type

A child object type is created using the UNDER keyword.

The following example creates a tabletop_t object type that inherits from rectangle_base_t:

CREATE OR REPLACE TYPE tabletop_t
UNDER rectangle_base_t
(
    material VARCHAR2(20),

    OVERRIDING MEMBER PROCEDURE display
);
/

The child type inherits the length and width attributes and the enlarge method from its parent. It also introduces a new attribute named material.

The OVERRIDING keyword indicates that the child type provides a new implementation of the inherited display method.

5.3 Overriding an Inherited Method

Method overriding allows a subtype to replace the implementation of an inherited member method.

The following type body implements the overridden method:

CREATE OR REPLACE TYPE BODY tabletop_t AS

    OVERRIDING MEMBER PROCEDURE display IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Length: ' || SELF.length);
        DBMS_OUTPUT.PUT_LINE('Width: ' || SELF.width);
        DBMS_OUTPUT.PUT_LINE('Material: ' || SELF.material);
    END display;

END;
/

When display is invoked on a tabletop_t instance, Oracle uses the implementation provided by the child type.

This behavior allows different object types to share a common structure while implementing specialized behavior (Oracle, n.d.-d).

5.4 Using the Child Object Type

The following example creates two tabletop instances:

DECLARE
    t1 tabletop_t;
    t2 tabletop_t;
BEGIN
    t1 := tabletop_t(20, 10, 'Wood');
    t2 := tabletop_t(50, 30, 'Steel');

    DBMS_OUTPUT.PUT_LINE('First Tabletop:');
    t1.display;

    DBMS_OUTPUT.PUT_LINE('Second Tabletop:');
    t2.display;
END;
/

Expected output:

First Tabletop:
Length: 20
Width: 10
Material: Wood

Second Tabletop:
Length: 50
Width: 30
Material: Steel

This example demonstrates inheritance and method overriding in Oracle PL/SQL. The child object reuses the attributes inherited from the parent and adds its own specialized attribute and behavior.

6. Abstract Object Types in PL/SQL

Oracle supports abstract-style object types through the NOT INSTANTIABLE clause.

A type declared NOT INSTANTIABLE cannot be instantiated directly. It is intended to serve as a parent type from which concrete subtypes can be derived.

A member method can also be declared NOT INSTANTIABLE. Such a method is declared without an implementation in the parent type and is expected to be implemented by an appropriate subtype.

A type containing a non-instantiable method must itself be declared NOT INSTANTIABLE (Oracle, n.d.-d).

Consider the following example:

CREATE OR REPLACE TYPE shape_t AS OBJECT
(
    length NUMBER,
    width  NUMBER,

    NOT INSTANTIABLE MEMBER PROCEDURE display
)
NOT INSTANTIABLE NOT FINAL;
/

In this example:

shape_t cannot be instantiated directly.
The display method is declared but has no implementation in the parent type.
The type is declared NOT FINAL, allowing subtypes to inherit from it.
A concrete subtype must provide an implementation of the inherited non-instantiable method before it can be instantiated.

This approach is useful when several object types share common attributes or a common interface but require different implementations of particular operations.

7. Conclusion

Oracle PL/SQL object types provide object-oriented capabilities within the database environment. By combining attributes and methods, developers can create reusable and structured database components.

The main concepts covered in this article include:

Object types: Define reusable structures containing attributes and methods.
Object instances: Represent individual objects created from a type.
Constructors: Initialize new object instances.
Member methods: Define operations associated with objects.
MAP and ORDER methods: Define how object instances are compared.
Inheritance: Allows new object types to reuse existing attributes and methods.
Method overriding: Enables subtypes to provide specialized implementations.
Abstract object types: Define non-instantiable parent types and methods.

Understanding these concepts helps developers design more modular and reusable database applications using Oracle PL/SQL.

References

Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL programming (6th ed.). O'Reilly Media.

Oracle. (n.d.-a). CREATE TYPE statement. Oracle Database PL/SQL Language Reference (19c). https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/CREATE-TYPE-statement.html

Oracle. (n.d.-b). Object methods. Oracle Database Object-Relational Developer's Guide (19c). https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/object-methods.html

Oracle. (n.d.-c). System-defined and user-defined constructors. Oracle Database Object-Relational Developer's Guide (19c). https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/system-defined-and-user-defined-constructors.html

Oracle. (n.d.-d). Inheritance in SQL object types. Oracle Database Object-Relational Developer's Guide (19c). https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/inheritance-in-sql-object-types.html

Pages

Object Oriented