Inherit the Database Oracle9i's Support for Object Type Inheritance by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition 10/29/2002

Editor's note: In this article, Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition, offers a quick review of object types and then shows how you can build object-type hierarchies by taking advantage of inheritance.

With the release of Oracle8 (my, does that seem like a long time ago), Oracle became an object-relational database. It supports both a traditional, relational model and one based on object types (known in other languages as classes). Now, it's entirely possible that you did know that because the Oracle8 (and Oracle8i) implementation of these object types or classes was woefully inadequate. In particular, one of the main drawbacks with the first iteration of Oracle's object implementation was its lack of support for "inheritance." Inheritance refers to the ability of an object type to inherit attributes and methods from previously defined object types. As a result, very few development shops ever used object types.

That's all about to change, though. With Oracle9i, Oracle has fixed one of the most glaring problems with object types by adding support for inheritance.

Quick Review of Object Types

Object types consist of a combination of attributes (think "column" from relational tables) and methods (think "procedures" and "functions" from packages). Members are also commonly referred to as methods. To use an object type, you instantiate a specific object from the type. Here's a very simple example of such a process:

1. Define an object-type specification. A person object type has three attributes: name, weight, and date of birth (defined using the new TIMESTAMP datatype of Oracle9i). It has a single member, a function that returns the age of a person (relying on the new INTERVAL datatype of Oracle9i).

   CREATE OR REPLACE TYPE person_ot
   IS OBJECT 
   (
      NAME    VARCHAR2 (100),
      weight  NUMBER,
      dob     TIMESTAMP (3),
    
      MEMBER FUNCTION age 
         RETURN INTERVAL YEAR TO MONTH
   );

2. Define an object-type body that contains the implementation of the object-type members.

   CREATE OR REPLACE TYPE BODY person_ot
   IS 
      MEMBER FUNCTION age 
         RETURN INTERVAL YEAR TO MONTH
      IS
         retval INTERVAL YEAR TO MONTH;
      BEGIN
         RETURN (SYSDATE - SELF.dob) 
            YEAR TO MONTH;
      END;
   END;

3. Instantiate an object and run some code: Notice that I call a constructor function (same name as the object type) to initialize the object. Then, if Steven is more than 18 years old, it's time for him to get a job.

   DECLARE
      steven person_ot :=
         person_ot (
            'Steven', 175, '23-SEP-1958');
      poppa person_ot :=
         person_ot (
            'Sheldon', 200, '07-NOV-1929');
   BEGIN
      IF steven.age > 
            INTERVAL '18' YEAR 
      THEN
         DBMS_OUTPUT.PUT_LINE (
            'Time for ' || steven.name || 
            ' to get a job!');
      END IF;
   END;

Notice that I was also able to instantiate or declare two persons from the same object type: Steven and his father, Sheldon. This is one of the key advantages of object types over packages: packages are static chunks of code. Object types are templates for individual objects to which the members are applied.

What Is Inheritance?

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Inheritance is well understood in the real world. When a person dies, his or her worldly assets can be passed on to or inherited by other people, usually the children of that person. Inheritance in the virtual world of software programming is much more benign. No death is required to inherit assets. Instead, I can define a hierarchy of object types (similar to the genealogy of a family tree), in which there are supertypes (ancestors, more general types) and subtypes (descendents, more specific types).

With inheritance, subtypes inherit all of the attributes and methods from their supertypes (and not just the immediate subtype, but any subtype or ancestor in the hierarchy). What's the big deal about this? Inheritance allows you to implement business logic at low levels in the hierarchy and then make them automatically available in all object types derived from those supertypes. You don't have to code that business rule multiple times to make it available in the different object types in the hierarchy.

Consider the following hierarchy, where I define a root object type of living thing. From it, I have two subtypes of person and fish. A person, in turn, can be a citizen corporation (legal precedent in this country has granted many of the rights of persons to corporations) or employee. A subtype of citizen is a war criminal. There are two kinds of employees: salaried and hourly. There are three kinds of salaried employees: management, administrative, and professional.

Why would I bother to define such a hierarchy? Regardless of the type of person I'm working with (citizen, employee, war criminal), they all share common properties, such as their age and name. But there are also specific characteristics of a citizen that aren't shared with all persons, such as nationality. On the employee side of things, I identify management as a separate type of employee, since they have a very different compensation model and different types of responsibilities.

One other powerful aspect of the object-type hierarchy is that it's active. Subtypes automatically reflect any changes made to attributes or methods in any parent.