Object Type Enhancements in Oracle9i Database

Oracle9i has moved more closer to Object Model. Apart from being a successful Relational database management system, Oracle is trying to become an Object oriented database management system also. This is very much evident from the enhancements made in Oracle9i. It provides the following new features compared with Oracle8i.

Overloading methods
Static methods in Object Types
Type Evolution
Type Inheritance
Dynamic Method Dispatch

Overloading methods

This feature allows two or more methods to have the same name as long as either data types are different or number of parameters is different.

The following example demonstrates how to use method overloading. There are two versions of GETSALARY method. First version doesn’t take any parameter and returns total salary of the employee. Second version takes the no. of days and returns the salary for the given number of days.

create or replace type Employee_Type as Object
(  empno number(5),
   ename varchar2(20),
   bs    number(5),
   member function GetSalary return number,
   member function GetSalary(ndays number) return number
);


create or replace type body Employee_Type  as
    member function getsalary  return number is
    begin
        return  bs;
    end;

    member function getsalary(ndays number) return number is
    begin
        return  bs / 30 * ndays;
    end;
end;

Here is a PL/SQL program to call both the methods. E is declared as an object of EMPLOYEE_TYPE. Then constructor of object type is used to initialize members of the object. When GETSALARY method is called without any parameter, first version is called. When GETSALARY invoked with a parameter then second version is called.

declare
  e  employee_type;
begin
    e := employee_type(101,'Praneeth',10000);
     -- get total salary 
    dbms_output.put_line( e.getsalary);
    -- get salary for 25 days
    dbms_output.put_line( e.getsalary(25));
end;

In the following cases methods can be overloaded:

When corresponding parameters are of different types
When number of parameters is different

Note: Two methods cannot be differentiated just based on return value.

Static methods in Object Types

A static method is related to object type and not instances of the object type. A static method is invoked using the name of the object type, whereas non-static methods are invoked using objects.

A static method doesn’t have SELF parameter so it cannot access any data related to either object or invoke any non-static method.

The following example shows how to create and use static method. GETMINBAL method is a static method that takes the type of account and returns the minimum balance to be maintained for account of that type. This method can be called without creating any object of the object type.

create or replace type account_type as object
(  ano 	    number(10),
   customer varchar2(30),
   curbal       number(10),
   static  function GetMinBal(atype varchar2) return number
);

create or replace type body account_type as
   static  function GetMinBal(atype varchar2) return number
   is
   begin
         if  atype = 'S' then
             return  1000;
         else
             return 2000;
         end if;
   end;
end;

The following PL/SQL block invokes the static method to get minimum balance for account of type C.


Begin
     dbms_output.put_line (  account_type.getminbal('C'));
end;

Type Evolution

Object types can be modified after they are created. New attributes and methods can be added and existing attributes and methods can be dropped.

The process of changing an Object Type according to the requirement is called as Type Evolution. This allows applications to be evoluved without having to be desinged entirely in advance.

The following few example will show how to alter an object type.

Frist, let us create a new object type called - STUDENT_TYPE as follows:

create or replace type student_type as object
( rollno    number(5),
  name    varchar2(30)
);

Then let us create an object based on this object type.

create table students of  student_type;

The object type STUDENT_TYPE doesn't contain some details that we need to have for a student. For example, it doesn't have course that student has taken and fee the student has paid so far. So the following ALTER TYPE command will add two attributes to STUDENT_TYPE object type. As the object type is evolving based on the need it is called as Type Evolution.

alter type student_type  add attribute( course varchar2(5), feepaid   number(6) ) cascade;

CASCADE specifies that all dependents of STUDENT_TYPE object type must be affected by the change. Now, the structure of the object type looks like the following:


SQL> desc student_type
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLLNO                                             NUMBER(5)
 NAME                                               VARCHAR2(30)
 COURSE                                             VARCHAR2(5)
 FEEpaid                                            NUMBER(6)

The values in attributes - COURSE and FEEPAID in STUDENTS table that is created on STUDENT_TYPE will be null for existing rows.

Then let us see how to add a method to an existing object type. Say, we have to add a method that returns the course fee based on the course. The following ALTER TYPE followed by CREATE TYPE BODY command will achieve it.

alter type student_type add member function GetFee return number cascade;

create or replace type body student_type  as
  member function GetFee return number 
  is
  begin
     return  case  course 
                 when 'Ora'  then  10000
                 when 'ODba' then 20000
                 when 'Vb' then 12000
              end;
  end;
end;

The following select command makes use of attributes and method of STUDENT_TYPE to provide the complete information.


SQL>select  name,course, value(s).getfee()  totalfee, fee  from students s;

NAME                           COURS   TOTALFEE        FEE
------------------------------ ----- ---------- ----------
Srikanth                       ODba       20000      15000
Praneeth                       Ora        10000       5000

Type Inheritance

Inheritance allows a new object type to be created from an existing object type. We can first create object type that is more generic and then create object types that are more specific from generic object type. For example, you can create an object type for a person called PERSON_TYPE and then inherit it into another object type called STUDENT_TYPE.

The new object type is called as SUBTYPE and the existing one is called as SUPERTYPE.

All the attributes and methods of supertype are inherited into subtype. In addition to that, subtype can add new attributes and methods or even override existing methods.

Oracle9i supports single inheritance. It means a subtype can be derived from only one supertype.

The following example creates a PERSON_TYPE and then STUDENT_TYPE that is derived from PERSON_TYPE.

First create PERSON_TYPE that represent a person.

create or replace type person_type as object
( 
    name varchar2(20),
    address varchar2(100),
    age     number(3),
    final member function GetType return varchar2
)  NOT FINAL;

create or replace type body person_type as
    member function GetType return varchar2
    is 
    begin
          if  age < 15 then
             return 'Child';
         elsif  age < 25 then
             return  'Teens';
         elsif  age < 45 then
             return  'Middleaged';
         else
             return 'Old';
         end if;
    end;
end;

If an object type is to be used as super type then it must be declared as NOT FINAL.

FINAL object types are final and they cannot be inherited. When a method is declared FINAL then the method cannot be overridden in subtype. GETTYPE function of PERSON_TYPE is declared as FINAL and it cannot be overridden.

Methods are not final by default. They can be overridden in the subtype.

A member can also be defined as FINAL; it is not to be overridden by subtypes.

Note: By default object type is final and subtypes cannot be derived from it. So giving NOT FINAL for objects that are to be inherited is mandatory.

Creating Subtype

A subtype is derived from a supertype. Subtype is created using UNDER keyword followed by the name of supertype. Subtype inherits all the attributes and methods of supertype.

The following is the creation of subtype called STUDENT_TYPE that is derived from PERSON_TYPE. It adds two attributes – COURSE and BALANCE and a method – PAYMENT.

It also overrides TOSTRING method of PERSON_TYPE. Whenever TOSTRING method is called using an object of STUDENT_TYPE then TOSTRING method of subtype is invoked and not that of supertype.

create or replace type student_type under person_type
( 
   course varchar2(20),
   balance number(5),
   member procedure payment(amt number),
   overriding member function ToString return varchar2
)  NOT FINAL;

create or replace type body student_type 
as
    member procedure payment(amt number)
    is 
    begin
        balance := balance - amt;
    end;
    overriding member function ToString return varchar2
    is
    begin
          return  name || ':' || to_char(age) || ':' || course || ':' || to_char(balance);      
    end;
end;

UNDER keyword is used to create a new object type from an existing object type. Oracle9i uses this keyword to implement inheritance.

OVERRIDING keyword specifies that the subtype is overriding a method with the same signature in the supertype.

STUDENT_TYPE is adding two attributes and a method to PERSON_TYPE. The following table show attributes and methods of these two object types.

Object Type	Attributes	Methods
Person_type	Name, address, age	GetType
Student_type	Name, address, age, course,balance	GetType, payment

The following PL/SQL block shows how to use supertype and subtype.

declare
   p person_type;
   s student_type;
begin
   p := person_type('Larry Ellison','California', 50);  
   s := student_type('Alen king','Newyork',22,'MS CS',10000);  

   dbms_output.put_line( p.gettype);
   dbms_output.put_line( s.gettype);


   dbms_output.put_line( p.tostring);
   dbms_output.put_line( s.tostring);

   s.payment(2000);

   dbms_output.put_line( s.tostring);
end;

The output of the above block is as follows:

Old
Teens
Larry Ellison:50
Alen king:22:MS CS:10000
Alen king:22:MS CS:8000

The constructor of STUDENT_TYPE takes five parameters as it inherits 3 attributes from PERSON_TYPE and added two more to them. When you call TOSTRING function of P then TOSTRING function of PERSON_TYPE is called. But when TOSTRING is invoked using S then TOSTRING function of STUDENT_TYPE is invoked. So Oracle calls the method from the object type whose object is used to invoke the method.

Dynamic Method Dispatch

When supertype and subtype have same methods (overriding), Oracle9i determines which method to call based on the type of instance is used to invoke the method.

As Oracle determines to which method – supertype’ method or subtype’s method – the control should be dispatched at runtime, it called as dynamic method dispatch. The term dynamic means the activity that happens at runtime and not at compile time.

The following code uses the same object to refer to an object of PERSON_TYPE first and then an object of STUDENT_TYPE.

Oracle allows an object reference of supertype to refer to an object of subtype. However, we can invoke only the method that are common between supertype and subtype using this object reference.

declare
   p person_type;
begin
   p := person_type('Larry Ellison','California', 50);  
   dbms_output.put_line( p.tostring);
   p := student_type('Alen king','Newyork',22,'MS CS',10000);  
   dbms_output.put_line( p.tostring);

end;

In the above example, we created an object reference (P) of PERSON_TYPE. Then first we made it referring to an object of PERSON_TYPE and then we call TO_STRING method using P. As it references an object of PERSON_TYPE, method TO_STRING of that object type is called.

After that we made P pointing to an object of STUDENT_TYPE. This is permitted in Oracle (in any other object oriented programming for that matter) as P is an object reference of supertype and STUDENT_TYPE is a subtype of PERSON_TYPE. When we call TOSTRING function, Oracle calls TOSTRING function of STUDENT_TYPE.

Whenever we invoke P.TOSTRING, Oracle determines which method to invoke depending on the type of object P is referencing.

However, note that you can invoke only the methods that are part of supertype (PERSON_TYPE) alone and methods that are overridden in subtype. In the above example, calling PAYMENT method using P even though P is referencing an object of STUDENT_TYPE is invalid.

  1  declare
  2     p person_type;
  3  begin
  4     p := student_type('Alen king','Newyork',22,'MS CS',10000);
  5     dbms_output.put_line( p.tostring);
  6     p.payment(2000);
  7* end;
  8  /
   p.payment(2000);
     *
ERROR at line 6:
ORA-06550: line 6, column 6:
PLS-00302: component 'PAYMENT' must be declared
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored

NOT INSTANTIABLE types and methods

When an object type is declared as NOT INSTANTIABLE then no instance of this object type can be instantiated. These object types are used only as supertypes of other object types.

When NOT INSTANTIABLE is used with a method then that method is to be overridden in all subtypes of the object type. If a not instantiable method is not overridden in subtype then subtype is also to be declared as NOT INSTANTIABLE.

The following example shows how to use NOT INSTANTIABLE types and methods.

EMPLOYEE_TYPE is a NOT INSTANTIABLE object type. It also contains a method GETSALARY that is NOT INSTANTIABLE.

You need not define body for non instantiable methods.

create or replace type employee_type as object
(  empno number(5),
    ename varchar2(20),
    NOT INSTANTIABLE member function  GetSalary return number
)  NOT INSTANTIABLE  NOT FINAL;

Now let us create TEMPEMP_TYPE that is derived from EMPLOYEE_TYPE. As the supertype has a non instantiable method, that method must be overridden in this subtype.

create or replace type tempemp_type UNDER employee_type
(
   nohours number(3),
   hourrate number(4),
   overriding member function  GetSalary  return number
 );

create or replace type body tempemp_type
as
  overriding member function  GetSalary  return number
  as
  begin
          return  nohours * hourrate;
  end;

end;

If TEMPEMP_TYPE doesn’t override GETSALARY method of EMPLOYEE_TYPE then this object type also becomes non instantiable.

The following PL/SQL code is used to create an object of TEMPEMP_TYPE and call GETSALARY method.

declare
  e1  tempemp_type;
 begin
     e1 := tempemp_type(1,'xyz',10,20);
     dbms_output.put_line( e1.getsalary );
 end;

If you try to create an object EMPLOYEE_TYPE then it will result in the following error, as it not an object type that can have an object.


SQL>  declare
  2    e1  employee_type;
  3   begin
  4       e1 := employee_type(1,'xyz');
  5   end;
  6  /
     e1 := employee_type(1,'xyz');
           *
ERROR at line 4:
ORA-06550: line 4, column 12:
PLS-00713: attempting to instantiate a type that is NOT INSTANTIABLE
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored

However, it is possible to create an object reference of EMPLOYEE_TYPE and make it pointing to one of its subtypes such as TEMPEMP_TYPE and call the common methods of supertype and subtype.


SQL>  declare
  2    e1  employee_type;
  3   begin
  4       e1 := tempemp_type(1,'xyz',20,100);
  5       dbms_output.put_line( e1.getsalary());
  6   end;
  7  /
2000

PL/SQL procedure successfully completed.

Conclusion

It is sure Oracle9i has improved object types substantially. The addition of overloading, static methods, inheritance and dynamic method dispatch is a big move towards object oriented database. Of course knowledge of an OOPL, especially like Java, will be of great help while you are trying to understand this new area of Oracle9i.