PL/SQL Object Types – Syntax, Examples, Features & Best Use Cases

What Are Object Types in PL/SQL?

Object Types in Oracle PL/SQL allow you to apply object-oriented programming (OOP) principles within the database. They are user-defined data types that encapsulate attributes (data) and methods (procedures/functions) within a single structure—similar to classes in Java or C++.

Object types bring the real-world modeling power of OOP to Oracle databases, enhancing code reusability, modularity, and abstraction.

Why Use Object Types?

Object Types provide:

  • Abstraction: Hide complex relational structures behind meaningful objects.

  • Reusability: Write once, use across multiple applications and procedures.

  • Modularity: Bundle related procedures and data into logical units.

  • Data Integrity: Reduce risk of data corruption via encapsulated access.

Components of an Object Type

PL/SQL object types have two parts, similar to packages:

  1. Object Type Specification

    • Declares the attributes and method headers.

    • It is public and accessible from SQL*Plus and other tools.

  2. Object Type Body

    • Implements the logic of methods declared in the spec.

    • Like a package body, but specific to that object.

Syntax: Creating an Object Type

➤ Step 1: Create the Object Type Specification

CREATE OR REPLACE TYPE book_obj AS OBJECT (
   title VARCHAR2(100),
   pages NUMBER,
   MEMBER PROCEDURE display_info,
   MEMBER FUNCTION short_title RETURN VARCHAR2
);

➤ Step 2: Create the Object Type Body

CREATE OR REPLACE TYPE BODY book_obj AS
   MEMBER PROCEDURE display_info IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Title: ' || title || ', Pages: ' || pages);
   END;

   MEMBER FUNCTION short_title RETURN VARCHAR2 IS
   BEGIN
      RETURN SUBSTR(title, 1, 10);
   END;
END;

➤ Step 3: Use the Object Type in a Block

DECLARE
   my_book book_obj;
BEGIN
   my_book := book_obj('Oracle PL/SQL Mastery', 450);
   my_book.display_info;
   DBMS_OUTPUT.PUT_LINE('Short Title: ' || my_book.short_title);
END;

Features of Object Types

  • Inheritance: One object type can inherit attributes/methods from another.

  • Overriding Methods: Subtypes can override base methods.

  • Polymorphism: Allows dynamic method resolution.

  • Type Evolution: Modify object types without data loss.

When to Use Object Types

  • When modeling real-world entities like Customer, Order, Book, etc.

  • When you want to encapsulate business logic inside your data model.

  • When building data-centric applications directly in PL/SQL.

  • In applications that demand modular and maintainable architecture.

Real-World Example Use Cases

  1. Library System: Use a book_obj type to manage books and author details.

  2. E-commerce App: product_obj can handle product info, tax logic, discounts.

  3. University DB: A student_obj type can track grades, courses, and evaluations.

FAQ Section

An object type is a user-defined type that encapsulates both data (attributes) and logic (methods) in a single unit.

Object types support OOP principles like inheritance and encapsulation, while packages group procedural logic.

Yes, object types can be used as column datatypes or variables in PL/SQL and SQL.

📘 AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.

Facebooktwitterredditpinterestlinkedinmailby feather
Scroll to Top