Object Types (OO PL/SQL)

Oracle's object-relational model lets you define object types — user-defined types with attributes (data) and member methods (behaviour). This is Oracle's implementation of object-oriented programming in SQL and PL/SQL.

Creating an Object Type

An object type has two parts: the type specification (attributes + method signatures) and the type body (method implementations):

-- TYPE SPECIFICATION
CREATE OR REPLACE TYPE emp_type AS OBJECT (
    -- Attributes
    employee_id  NUMBER(6),
    first_name   VARCHAR2(20),
    last_name    VARCHAR2(25),
    salary       NUMBER(8,2),
    department_id NUMBER(4),

    -- Member method signatures
    MEMBER FUNCTION  full_name     RETURN VARCHAR2,
    MEMBER FUNCTION  annual_salary RETURN NUMBER,
    MEMBER PROCEDURE give_raise(p_pct IN NUMBER),

    -- MAP method for comparison / ORDER BY
    MAP MEMBER FUNCTION sort_key    RETURN NUMBER
) NOT FINAL;   -- NOT FINAL allows subtypes to inherit from it
/

-- TYPE BODY
CREATE OR REPLACE TYPE BODY emp_type AS

    MEMBER FUNCTION full_name RETURN VARCHAR2 IS
    BEGIN
        RETURN TRIM(SELF.first_name) || ' ' || TRIM(SELF.last_name);
    END full_name;

    MEMBER FUNCTION annual_salary RETURN NUMBER IS
    BEGIN
        RETURN NVL(SELF.salary, 0) * 12;
    END annual_salary;

    MEMBER PROCEDURE give_raise(p_pct IN NUMBER) IS
    BEGIN
        SELF.salary := ROUND(SELF.salary * (1 + p_pct / 100), 2);
        -- Note: cannot commit from here — caller must do it
        UPDATE employees
        SET    salary = SELF.salary
        WHERE  employee_id = SELF.employee_id;
    END give_raise;

    MAP MEMBER FUNCTION sort_key RETURN NUMBER IS
    BEGIN
        -- Objects are sorted by salary descending
        RETURN -SELF.salary;
    END sort_key;

END;
/

Using Object Types in PL/SQL

DECLARE
    v_emp  emp_type;
BEGIN
    -- Instantiate using the default constructor
    v_emp := emp_type(101, 'Neena', 'Kochhar', 17000, 90);

    -- Call member functions
    DBMS_OUTPUT.PUT_LINE('Name:          ' || v_emp.full_name());
    DBMS_OUTPUT.PUT_LINE('Annual salary: $' || v_emp.annual_salary());

    -- Call member procedure (modifies the object and the underlying row)
    v_emp.give_raise(10);
    DBMS_OUTPUT.PUT_LINE('New salary:    $' || v_emp.salary);

    -- Access attributes directly
    DBMS_OUTPUT.PUT_LINE('Dept:           ' || v_emp.department_id);
END;
/

Output:

Name:          Neena Kochhar
Annual salary: $204000
New salary:    $18700.00
Dept:           90

Constructor Methods

Oracle generates a default constructor with the same name as the type. You can override it by declaring a CONSTRUCTOR FUNCTION:

CREATE OR REPLACE TYPE address_type AS OBJECT (
    street   VARCHAR2(100),
    city     VARCHAR2(50),
    state    VARCHAR2(2),
    zip      VARCHAR2(10),
    country  VARCHAR2(30),

    -- Custom constructor with default country
    CONSTRUCTOR FUNCTION address_type(
        p_street  IN VARCHAR2,
        p_city    IN VARCHAR2,
        p_state   IN VARCHAR2,
        p_zip     IN VARCHAR2
    ) RETURN SELF AS RESULT,

    MEMBER FUNCTION formatted RETURN VARCHAR2
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY address_type AS

    CONSTRUCTOR FUNCTION address_type(
        p_street  IN VARCHAR2,
        p_city    IN VARCHAR2,
        p_state   IN VARCHAR2,
        p_zip     IN VARCHAR2
    ) RETURN SELF AS RESULT IS
    BEGIN
        SELF.street  := p_street;
        SELF.city    := p_city;
        SELF.state   := p_state;
        SELF.zip     := p_zip;
        SELF.country := 'USA';  -- default
        RETURN;
    END address_type;

    MEMBER FUNCTION formatted RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.street || ', ' || SELF.city || ', ' ||
               SELF.state || ' ' || SELF.zip ||
               CASE WHEN SELF.country != 'USA'
                    THEN ', ' || SELF.country
                    ELSE ''
               END;
    END formatted;

END;
/

-- Use the custom constructor (no country needed)
DECLARE
    v_addr address_type := address_type('100 Oracle Way', 'Redwood Shores', 'CA', '94065');
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_addr.formatted());
END;
/

Type Inheritance (UNDER)

-- Subtype inherits all attributes and methods from emp_type
CREATE OR REPLACE TYPE manager_type UNDER emp_type (
    -- Additional attribute
    team_size     NUMBER(4),

    -- Overriding method
    OVERRIDING MEMBER FUNCTION annual_salary RETURN NUMBER,

    -- New method
    MEMBER FUNCTION team_budget RETURN NUMBER
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY manager_type AS

    -- Override the parent's annual_salary to include management bonus
    OVERRIDING MEMBER FUNCTION annual_salary RETURN NUMBER IS
    BEGIN
        RETURN (SELF.salary * 12) + (SELF.salary * 0.20 * 12);  -- +20% bonus
    END annual_salary;

    MEMBER FUNCTION team_budget RETURN NUMBER IS
    BEGIN
        -- Approximate: manager's salary * team size
        RETURN SELF.salary * SELF.team_size;
    END team_budget;

END;
/

-- Usage
DECLARE
    v_mgr  manager_type;
BEGIN
    v_mgr := manager_type(100, 'Steven', 'King', 24000, 90, 15);

    DBMS_OUTPUT.PUT_LINE('Full name:   ' || v_mgr.full_name());   -- inherited
    DBMS_OUTPUT.PUT_LINE('Annual pay:  $' || v_mgr.annual_salary());  -- overridden
    DBMS_OUTPUT.PUT_LINE('Team budget: $' || v_mgr.team_budget());    -- new
END;
/

Object Tables

Store objects directly in a table:

-- Object table: each row IS an emp_type
CREATE TABLE emp_objects OF emp_type (
    employee_id PRIMARY KEY
);

-- Insert using the constructor
INSERT INTO emp_objects VALUES (emp_type(200, 'John', 'Smith', 5000, 60));
INSERT INTO emp_objects VALUES (emp_type(201, 'Jane', 'Doe',   6000, 60));
COMMIT;

-- Query object tables — call methods in SQL
SELECT e.employee_id,
       e.full_name()        AS name,
       e.annual_salary()    AS annual_pay
FROM   emp_objects e
ORDER BY e.sort_key();

-- VALUE() returns the object itself
SELECT VALUE(e) AS emp_object FROM emp_objects e;

REF — Object References

REF is a pointer to a row in an object table:

DECLARE
    v_emp_ref  REF emp_type;
    v_emp      emp_type;
BEGIN
    SELECT REF(e) INTO v_emp_ref
    FROM   emp_objects e
    WHERE  e.employee_id = 200;

    -- Dereference with DEREF
    SELECT DEREF(v_emp_ref) INTO v_emp FROM dual;
    DBMS_OUTPUT.PUT_LINE(v_emp.full_name());
END;
/

MAP vs ORDER Methods

Method	Purpose
MAP MEMBER FUNCTION	Returns a scalar value; Oracle sorts/compares objects by this value
ORDER MEMBER FUNCTION	Takes another object of the same type as a parameter; returns -1, 0, or 1

Only one of MAP or ORDER can be defined per type. MAP is more efficient for bulk sorting.

Summary

• Object types combine attributes and methods — Oracle's OO mechanism.
• Create the type spec (attributes + signatures) and type body (implementations) separately.
• SELF refers to the current object instance inside member methods.
• Custom CONSTRUCTOR FUNCTION overrides the default attribute-list constructor.
• Subtypes (UNDER) inherit all parent attributes and methods; OVERRIDING replaces them.
• Object tables store typed objects; query with VALUE() and reference with REF/DEREF.
• MAP or ORDER methods enable comparison and sorting of objects in SQL.