Inheritance in Oracle PL/SQL
CS744 LAB
HiLCoE
What is Inheritance
A core concept in Object-Oriented Programming (OOP).
Models an "is-a" relationship between objects.
A new class (a subtype) is created from an existing one (a
super type).
The subtype inherits all attributes and behaviours from its
super type.
The subtype can then add its own unique attributes and
behaviours.
Key benefits:
Code Reuse: Avoids writing the same code multiple times.
Polymorphism: Allows a single function to work with different
related object types.
17/08/2025 HiLCoE-CS744 LAB 2
Inheritance in Oracle Database
– Oracle's object-oriented PL/SQL supports object
type hierarchies.
– You create a parent object type (the super type)
first.
– You then create a child object type (the subtype)
using the UNDER keyword.
– For a type to be a super type, it must be declared
with the NOT FINAL clause.
– OVERRIDING key word used to override member
function from super types
17/08/2025 HiLCoE-CS744 LAB 3
Objet Type Hierarchy
• person_t super type
having id and name person_t
attributes and
get_details member
function and employee_t
employee_t sub type
having job_title and
salary attributes.
17/08/2025 HiLCoE-CS744 LAB 4
Super Type Implementation
CREATE OR REPLACE TYPE person_t AS OBJECT (
person_id NUMBER,
name VARCHAR2(50),
MEMBER FUNCTION get_details RETURN VARCHAR2
) NOT FINAL;
/
CREATE OR REPLACE TYPE BODY person_t AS
MEMBER FUNCTION get_details RETURN VARCHAR2 IS
BEGIN
RETURN 'ID: ' || person_id || ', Name: ' || name;
END get_details;
END;
/
17/08/2025 HiLCoE-CS744 LAB 5
Sub Type
• This is the child object type that inherits from
person_t.
• It adds new attributes (job_title, salary) and
can override inherited methods.
17/08/2025 HiLCoE-CS744 LAB 6
Sub type implementation
CREATE OR REPLACE TYPE employee_t UNDER person_t (
job_title VARCHAR2(50),
salary NUMBER,
OVERRIDING MEMBER FUNCTION get_details RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY employee_t AS
OVERRIDING MEMBER FUNCTION get_details RETURN VARCHAR2 IS
BEGIN
-- Calls the supertype's method and extends it
RETURN (SELF AS person_t).get_details() || ', Job: ' ||
job_title || ', Salary: ' || salary;
END get_details;
END;
/
17/08/2025 HiLCoE-CS744 LAB 7
(SELF AS person_t)
• What it is: A way for a subtype to temporarily
treat itself as its super type. It's called an
upcast or explicit cast.
• Why use it: It allows a subtype to call a
method or access an attribute from its parent
class.
17/08/2025 HiLCoE-CS744 LAB 8
Polymorphism and Method Overriding
• Polymorphism ("many forms") allows a single
name to be used for different types of objects.
– The get_details procedure works on both
person_t and employee_t objects.
– When a method is called, Oracle automatically
uses the correct, overridden version for the
object's actual type.
17/08/2025 HiLCoE-CS744 LAB 9
Person type table
• CREATE TABLE employees_tab OF person_t;
Note: the object table is a polymorphic container. It's a single
table that can store objects of different but related types, with
Oracle handling the complex storage management behind the
scenes.
17/08/2025 HiLCoE-CS744 LAB 10
Continued..
• INSERT INTO employees_tab
values(employee_t(1,'foo’,’Accountant’,7000));
• select e.get_details() from emoloyee_tab e;
17/08/2025 HiLCoE-CS744 LAB 11
IS OF TYPE
--IS OF Type
• --The IS OF type predicate tests object
instances for the level of specialization of their
type.
SELECT VALUE(e)
FROM employee_tab e
WHERE VALUE(e) IS OF
(employee_t);
17/08/2025 HiLCoE-CS744 LAB 12
TREAT function
• TREAT
The TREAT function performs down casting. It's
used to convert a super type object to one of its
subtypes.
• SELECT TREAT(VALUE(e) AS employee_t) FROM
employee_tab e WHERE VALUE(e) IS OF TYPE
(employee_t);
17/08/2025 HiLCoE-CS744 LAB 13
Exercise
PL/SQL Object-Oriented Inheritance Assignment
You've just been hired as a database architect for "Swift Fleet Logistics," a company that manages a
diverse collection of vehicles. The current system for tracking their fleet is outdated and rigid, with
separate tables for every type of vehicle. This makes it difficult to add new vehicle types or run
reports on the entire fleet.
Your manager, a forward-thinking CTO, wants you to propose a new database design using object-
oriented principles to make the system more flexible and maintainable. Your first task is to
demonstrate how you would model the existing fleet, which currently consists of Cars and Trucks,
using an inheritance hierarchy.
Your design must be able to:
Store the general information common to all vehicles, such as the manufacturer, model, and year.
Uniquely store information specific to each vehicle type (e.g., the number of doors for a car, or the
payload capacity for a truck).
Allow a single database query to access and display details for any vehicle, regardless of its specific
type.
Enable the system to correctly identify a vehicle's specific type at runtime and access its unique
attributes.
Your Mission:
Create a comprehensive PL/SQL implementation that showcases your solution. You must develop the
object types, their bodies, and a final anonymous PL/SQL block that demonstrates the power of your
design.
17/08/2025 HiLCoE-CS744 LAB 14