ABIS Infor - 2011-11

Oracle and Objects

Sandy Schillebeeckx (ABIS) - 16 September 2011

Abstract

In Oracle you can create objects. And in this case I don't mean tables or views or stuff like that. I am talking about real OBJECTS like in Java, C++,... How do you do this, and why would you bother? That is what I am going to explain to you in this article.

Why would you bother?

While writing applications (in an object oriented language) that are communicating with relational databases, you probably bumped into object-relational mapping problems.

Wouldn't it be nice to be able to avoid this? Using objects in Oracle is one of the ways of doing it. Objects can be stored inside tables, and they can even have "methods" defined inside of them. That's why Oracle is called an object-relational database. With this technique you can even save yourself some joins.

But don't overdo it. There are limitations.

Creating Objects in Oracle

Objects in Oracle are created as User Defined Types.

In the following example we will create a "courseobject" type, instead of using a table to do the same thing.

Each course is defined with an id (cid), a title (ctitle), a duration (cdur) and a price per day (cpriceperday). We will add a function ("method") to calculate the full price.

create or replace type courseobject as object
(cid char(4),
 ctitle char(45),
 cdur number(2,0),
 cpriceperday number,
 member function getTotprice return number);

The function's implementation is declared in the type body. Like this you can also add procedures and much more. It is comparable with package declarations. In this case we use PL/SQL as language (but you can as well write them in java if you'd wish).

create or replace
type body courseobject as 
 member function getTotprice return number
 is
 begin
   return cdur*cpriceperday;
 end;
end;

Now we are going to create the SESSIONS table which is having a course of type courseobject as one of its columns.

Each session has a session id (sno), a location (sloc_cono, FK to a company), an instructor (sins_pno, FK to a person), and a course (scourse).

 create table sessions
 (sno number primary key,
  sloc_cono number,
  sins_pno number,
  scourse courseobject);

To insert data into this table, you have to define courseobjects via a "constructor".

insert into sessions values (1,45,19,courseobject('7900','oracle',3,200));

In a PL/SQL program, you could even store them in variables, e.g.

course1:= NEW courseobject('7900','oracle',3,200), with course1 defined as courseobject in the declare block.

This is the way to store objects in the table. To get things out again, you can for instance use the following query which is showing the session number, the course title and the total price of the course.

select sno, s.scourse.ctitle, s.scourse.getTotprice()
from sessions s 

The alias "s" is obligatory. You use the dot-notation to get variables out of the object and to call the methods.

In case the courses where defined in their own table, you would use the following query:

select sno, ctitle, cdur*cpriceperday
from sessions inner join course on s_cid=cid

(presuming s_cid the foreign key in sessions)

Conclusion

By using objects in your tables, you can bring your table design and object design closer together. It could even give you some performance benefits (less joins). But be aware, referential integrity (for instance) is much harder to control.

In any case, it is a nice technique that Oracle is offering you.