Oracle : Type/Object Creation, Inheritance and Method Implementation with Examples

In this tutorial, we will try to go through a problem for modelling a system for storing polygon objects which have their own methods for computing the area or perimeter.

The objects that we should have are :

1. Point ( x , y )
2. Side ( Point, Point )
3. Polygon ( Sides)

Creating Objects in Oracle

1 Create a new type for point

create or replace type point as object(
 x number,
 y number
)

2 Create a new type for side

create or replace type side as object(
    a point,
    b point,
    member function get_length return integer
);

[info] Note that the side has a member method named get_length which should return an integer[/info]

Implementation of Methods

3 Need to add the implementation for get_length method for the side object using the syntax below. The self keyword, is the same as in Java which refers to the object itself.

self.a.x refers to the x value for the point a

create or replace type body side as  
   member function get_length return integer is
   begin 
    return sqrt(  ((self.a.x-self.b.x)*(self.a.x-self.b.x)) + ((self.a.y-self.b.y)*(self.a.y-self.b.y))  );
   end;
end;

Nested Tables

4 Because the polygon contains a set of sides, we need to create a nested table as new type for storing the set of sides.
[info] Consider Nested Tables in Oracle as a Vector in Java for storing Objects.[/info]

create or replace type side_ns as table of side;

Creating an object : shape

5 The shape object should have a name as well as a set of sides. Two methods should be defined within the creation of the type. The syntax is shown below:

create or replace type shape_tp as object(
   name varchar2(20),
   sns   side_ns,
   member function get_perimeter return integer,
   member function get_area return integer
   ) NOT FINAL;

6 The implementation of the two methods is shown below. Get_area returns 0 for now. There is a loop within the code.

[info] myres NUMBER; is a definition for a local variable[/info]

[info]The special method :dbms_output.put_line is the same as System.out.println in Java for displaying messages on the console.[/info]

create or replace type body shape_tp as 
  member function get_perimeter return integer is
  myres NUMBER;
  begin
        myres := 0;
        for ind IN 1..sns.count LOOP           
           dbms_output.put_line('Side  has length '|| sns(ind).get_length());
            myres :=myres + sns(ind).get_length();
        END LOOP;
       return myres;
  end;
  member function get_area return integer is
  begin
    return 0;
  end;
end;

Creating a table with nested table

7 We need to create a database table to store the shape objects.

[success]Because the shape object has a nested table as its attribute. The new table needs to be made aware by adding the NESTED TABLE keyword as shown in the syntax below:

create table shape_tb (
 ob shape_tp
)NESTED TABLE ob.sns store as sns;

Inserting Objects in Oracle

7 To insert some objects into the shape_tb table, we use the SQL code as below:

[info]If using Oracle XE, use one query at a time[/info]

insert into shape_tb values (
  new shape_tp(
		   'Square' , 
		    side_ns( 
			      new side( new point(0,0),new point(0,5)) , 
			      new side( new point(0,5),new point(5,5)),  
			      new side( new point(5,5),new point(5,0)) ,
			      new side( new point(5,0),new point(0,0))  
			    )
	      )
	 );

insert into shape_tb values (
  new shape_tp(
		   'Triangle' , 
		    side_ns( 
			      new side( new point(0,0),new point(0,10)) , 
			      new side( new point(0,10),new point(25,5)),  
			      new side( new point(25,5),new point(0,0)) 
			    )
	      )
	 );

Viewing data with Method Invocation

8 To view data, use the SELECT command. We can invoke methods for the objects defined as below:

select t.ob.name, t.ob.get_perimeter() as perimeter ,  t.ob.get_area() as area  from shape_tb t

Inheritance of Objects with Oracle

9 Because the function get_area is not implemented fully for the reason that every shape has its formula for computing the area. We need to derive a sub-type that would implement again (=override) the get_area implementation.

[info]Inheritance in Oracle, we use the keyword : under[/info]

create or replace type eshape_tp under shape_tp(
  OVERRIDING MEMBER FUNCTION get_area return integer
)

Overriding Methods

10 We override the implementation for get_area to return 10 for the new derived object.

create or replace type body eshape_tp as 
  OVERRIDING MEMBER FUNCTION get_area return integer is
  begin
    return 10;
  end;
end;

Inserting Sub-types or Sub-Objects

11 To insert again using the derived subclass, we use:

insert into shape_tb values (
  new eshape_tp(
		   'eSquare' , 
		    side_ns( 
			      new side( new point(0,0),new point(0,5)) , 
			      new side( new point(0,5),new point(5,5)),  
			      new side( new point(5,5),new point(5,0)) ,
			      new side( new point(5,0),new point(0,0))  
			    )
	      )
	 );

12 Try to view the data using the same previous SELECT query as:

select t.ob.name, t.ob.get_perimeter() as perimeter ,  t.ob.get_area() as area  from shape_tb t

[info] We observe that the value for the area for the sub object is 10 not 0 [/info]

Questions

1 Try to implement the area function to return the real value for shapes who have

1. 3 sides ( Triangle )
2. 4 sides  ( Square , Rectangle )