Years ago, in my Oracle years I wrote an article on Oracle (Object) Types, and how those make Pl/Sql so much more powerfull. It was in Dutch, since I wrote it for our monthly internal consulting magazine called 'Snapshot'. Since it was in Dutch and I regularly refer to it on our blog or in questions on forums, I wanted to rewrite it for years. So let's go. Oracle Types are introduced in the Oracle 8/8i era. And enabled me to build stuff that were not possible using regular Pl/Sql.

In Oracle 8/8i the implementation lacked constructors, but it was already very powerful. From Oracle 9i the possibilities were extended a lot and brought it to where it still is, I think. So everything that I post here is possible from Oracle 9i and later.

And you may ask: why bother an extension in Pl/Sql dated about 10 tot 15 years ago? And why if I'm into SOA Suite and/or OSB? Well, I really think that Pl/Sql in combination with Object Types is the best tool at hand for creating API's to Oracle Database applications. And the DB Adapter's capabilities of calling Pl/Sql functions with Object Type parameters is very strong. Together it is the best integration pattern for the Oracle Database. Even for data retrieval, it's much stronger than stand alone queries or views.

Do these Object Types make Pl/Sql an object oriented language? I'm not going to discuss that in extend. I think it is much more like Turbo Pascal 5.5 was OO: I think it's more a 3GL with Object-extensions. So if you're an OO-purist: you're right upfront, as far as I'm concerned. But object-types make the life of a Pl/Sql programmer a lot more fun. And I feel that still after all those years the capabilities aren't utilized as much as could be.

 So let's dive in to it. We start at the basics.

A type with a constructor

 A type with a constructor and some methods can be created as follows:

create or replace type car_car_t as object
(
  -- Attributes
     license   varchar2(10)                     
  ,  category  number(1)     
  ,  year      number(4)     
  ,  brand     varchar2(20)  
  ,  model     varchar2(30)  
  ,  city      varchar2(30)
  ,  country   varchar2(30)
  -- Member functions and procedures
  , constructor function car_car_t(p_license in varchar2)
    return self as result
  , member function daily_rate(p_date date) 
    return number
  , member procedure print
)
/

I don't intent to give a college on object orientation here,  but if you look at the type specification it is immediatly clear that an Oracle Type is a kind of record-type on it's own, but that besides attribute it also contains executable additions: methods.

Methods are functies and/or procedures that execute on the attributes of the type. Within the method you can see the attributes as 'global' package variables.

As said, a very convenient addition in the Types Implementation is the possibility to define your own  constructors. They're declared as:

  , constructor function car_car_t(p_license in varchar2)
    return self as result

A constructor starts with the keyword constructor and is always a function that returns the 'self' object as a result. Also, the constructor is always named the same as the type itself. Implicitly there's always a constructor with all attribute as a parameter. This was already the case in Oracle 8, but from Oracle 9i/10g onwards this is still delivered for free. But besides the default constructor you can define several of your own. br /> This enables you to instantiate an object based on a primary key value, for example. Based on that key you can do a select into the attributes from a particular table. Or instantiate a type based on the read of a file. Or parameter-less so that you can just instantiate a dummy object that can be assigned values in a process. This is especially convenient if you have a very large object, where not all the attributes are mandatory.
Often I add a print method or a to_xml or to_string method. This enables you to print all the attributes or return an XML with them, including the call of the same method in child objects. Child objects are attributes based on other types or collections.
The implementation of the methods are in the Type Body:

create or replace type body car_car_t is
  
  -- Member procedures and functions
  constructor function car_car_t(p_license in varchar2)
    return self as result
  is
  begin
    select license
    ,      category
    ,      year
    ,      brand
    ,      model
    ,      city
    ,      country
    into   self.license
    ,      self.category
    ,      self.year
    ,      self.brand
    ,      self.model
    ,      self.city
    ,      self.country
    from cars
    where license = p_license;
    return;
  end;
  member function daily_rate(p_date date)
  return number
  is
    l_rate number;
    cursor c_cae( b_license in varchar2
                , b_date    in date)
    is select cae.dailyrate
       from   carsavailable cae
       where  b_date between cae.date_from and nvl(cae.date_to, b_date)
       and    cae.car_license = b_license
       order by cae.date_from;
    r_cae c_cae%rowtype;
  begin
    open c_cae( b_license => self.license
              , b_date    => p_date);
    fetch c_cae into r_cae;
    close c_cae;
    l_rate := r_cae.dailyrate;
    return l_rate;
  end;
  member procedure print
  is
    l_daily_rate number;
  begin
    dbms_output.put_line( 'License   : '||self.license);
    dbms_output.put_line( 'Category  : '||self.category);
    dbms_output.put_line( 'Year      : '||self.year);
    dbms_output.put_line( 'Brand     : '||self.brand);
    dbms_output.put_line( 'Model     : '||self.model);
    dbms_output.put_line( 'City      : '||self.city);
    dbms_output.put_line( 'Country   : '||self.country);
    l_daily_rate := daily_rate(p_date => sysdate);
    if l_daily_rate is not null
    then
      dbms_output.put_line('Daily Rate: '||l_daily_rate);
    else
      dbms_output.put_line('No cars available');
    end if;
  end;
  
end;
/

Here you see that I used a primary key based constructor to do a select from the cars table into the attributes. And do a simple return. I do not have to specify what I want to return, since it somehow does return 'itself'. That is: an instance of the type. So the return variable is more or less implicit.

The print method enables me to test the object easily after the instantiation:

declare 
  -- Local variables here
  l_car car_car_t;
begin
  -- Test statements here
  l_car := car_car_t(:license);
  l_car.print;
end;

Collections 

An object don't come alone very often. Same counts for Object-instances. We talk with the database so in most cases we have more than one instance of an entity

Een a set of object-instances is called a collection. And is defined as:

create or replace type car_cars_t as table of car_car_t;

So a collection is actually a table of objects  of a certain type. Oracle is even able to query on such a collection, but I'll elaborate on that later.

Note, by the way, that there now is a reference to, or put otherwise, a dependency to the particular object type. This means that the object-specification of in this case 'car_car_t' can't be changed anymore, without dropping all the references to it. This may become quite inconvenient when changing a large hierarchy of object types. So you'd better create an install script right away, that can recreate (drop and create) the complete tree.

The 'body', the source code, can be recompiled. This is important, because the specification defines the  structure of the object (the class) and other objects are to depended on this interface. Maybe Oracle should define an interface type, so this can be made a bit more loosly coupled.

This counts especially when it comes to table definitions (in the database) where you can add an object-type based column. After all, a physical table can't become invalid. What should become of the data in that case? That could become 'undefined'.  For the rest, you can see Collections an ordanary Pl/Sql table, comparable to an "index by binary_integer"-table. But  with the difference that it is an (stand-alone) object in itself, containing other objects. This means that to be able to use a Collection it has to be instantiated. This can be done implicitly by means of a query:

select cast(multiset(
select license
,      category
,      year
,      brand
,      model
,      city
,      country 
from cars
) as car_cars_t)
from dual

What this actually does is that the result set of the select on the table cars is being redefined as a Collection. The Multiset-function denotes that what is returned is actually a data-set of zero or more rows. The Cast-function is used to denote as what datatype/objecttype the multiset should be considered. You could say that over the result set a collection layer is layed. I haven't been able to test it, but I am curious about the performance effects. What would be the difference between this query and for example a for-cursor-loop? Now it is seasoned with a collection-sauce you can handle this resultset as were it a Pl/Sql-table in memory after all.


Of course you can instantiate and fill the collecation more explicitly like:

declare 
  l_cars car_cars_t;
begin
  l_cars := car_cars_t();
  for r_car in (select license from cars)
  loop
    l_cars.extend;
    l_cars(l_cars.count) := car_car_t(r_car.license);
  end loop;
  if l_cars.count > 0
  then
    for l_idx in l_cars.first..l_cars.last
    loop
      dbms_output.put_line('Car '||l_idx||':');
      l_cars(l_idx).print;
    end loop;
  end if;  
end;

In this case in the collection is instantiated in the first line. Then a for loop is started based on the select of the primary key on the cars table, which is the license column. Then in the loop, on each iteration the collection is extended. And then a new instance of car_car_t, using the primary key constructor with the car's license is assigned to the last row of the collection, denoted with the implicit count attribute of the collection.
In the second loop an example is given, which shows how easily you can traverse the collection and print each row-object.

Object-Functions and Views The creation and propagation of a collection can also be put in a function of course:

create or replace function get_cars 
return car_cars_t is
  l_cars car_cars_t;
begin
  select cast(multiset(
  select license
  ,      category
  ,      year
  ,      brand
  ,      model
  ,      city
  ,      country
  from cars
  ) as car_cars_t)
  into l_cars
  from dual;
  return(l_cars);
exception
  when no_data_found then
    l_cars := car_cars_t();
    return l_cars;
end get_cars;
/

This function get_cars has no input parameters, you could restrict the query based on model or year for instance. But it returns the car_cars_t collection. If there are no cars available this query raises a no_data_found exception, since it does a select-into. But since it happens in a function, the nice thing is that you can catch the exception and just return an empty collection.

But the fun part is that you can use the result of that function as the source of a query. So, you see in the function that you can lay a collection-sause over a result-set, but the other way around is also possible: a collection can be queried:

select car.license
,      car.category
,      car.year
,      car.brand
,      car.model
,      car.city
,      car.country
,      car.daily_rate(sysdate) daily_rate
from
table(get_cars) car

The trick is in the table function. That directs Oracle to consider the outcome of the function as a result set. The example also shows that the methods are also available. But of course only if it's a function. By the way, in this example the attributes and the method-results are simple scalair datatypes, but they also could be types or collection. And those are available in the query. The attributes of object-attributes can be referenced in the query with the dot-notiation ('.'). In other words: hierarchical deeper attributes can be fetched as a column value and returned this way.

In this case we use a function as the base for the query. In that case it is also possible to create a view on top of it. As long as the function and the object-types that are returned  are 'visible' for the user/the schema that is owner ovthe view and/or uses the view.

But to stretch it some more: not only the result of a function can be used as the base of a function. Also a local variable or package-variable can be supplied as the source of a query:

declare
  l_cars car_cars_t;
  l_rate number;
begin
  l_cars := get_cars;
  select car.daily_rate(sysdate - 365) daily_rate
  into l_rate
  from table(l_cars) car
  where license = '79-JF-VP';
  dbms_output.put_line( l_rate);
end;

Isn't this a lot easier than to traverse a pl/sql-tabel in search for that one particular row?
Now, you could think: isn't this a full-table scan then? Yes indeed. But this fulltable scan is done completely in memory and therefor very fast. And let's be honest: who created a pl/sql-table of more than a gigabyte? Although using the examples above this can be done quite easily. So a bit of  performance-aware programming is recommended.

Pipelining

In the previous paragraph I already mentioned performance. With the collection-function-methodevan above you could program your own 'External Tables' in Oracle 8i already (External Tables were introduced in 9i). So you could, for example, read a file in a Pl/Sql-function using UTL_File and process it into a collection and return this.

Then you could create a view around it with the table-functie and do a query on a file! Impressive, huh? A very important disadvantage of this method is that the function is executed as a logical/functional unite completely. So the complete file is read, the complete collection is built and returned to the caller as a whole. That means that doing a sleect on that function, the function is executed completely, before you'll get your result. This is especially inconvenient when the after-processing on the result of the function is time-expensive as well. This is why in Oracle 9i pipelining is introduced.

A pipelined function is functionally identical to the collection-returning-function as I described above. The most important difference is that is denoted that is about pipelined function (duh!), but more-over that the in-between=results are piped (sorry, in Dutch this is funny, but I did not made up the term) and thus returned as soon as they become available.

This looks like:

create or replace function get_cars_piped(p_where in varchar2 default null)
return car_cars_t
pipelined
is
  l_car car_car_t;
  type t_car is record
  ( license cars.license%type);
  type t_cars_cursor  is ref cursor;
  c_car t_cars_cursor;
  r_car t_car;
  l_query varchar2(32767);
begin
  l_query := 'Select license from cars '||p_where;
  open c_car for l_query;
  fetch c_car into r_car;
  while c_car%found
  loop
    l_car := car_car_t(p_license => r_car.license);
    pipe row(l_car);
    fetch c_car into r_car;
  end loop;
  close c_car;
  return;
end get_cars_piped;

So you see indeed the keyword 'pipelined' in the specification of the function, and after that in the loop that each separate object using the 'pipe row' statement is returned. You could say that 'pipe row' is like an intermediate return. Besides that you get in this function, completely for free and on the house, an example of the use of a ref-cursor. With this it is possible to build up a flexibele cursor of which you can adapt the query. You can call this function as follows:

select *
from table(get_cars_piped('where license != ''15-DF-HJ'''))

I found that is is not possible to call this function in a pl/sql-block directly. If you think about it, it seems logical What happens in the statemnt is that the sql-engine calls the pl/sql-function and receives each row directly and is able to process it. This way it is possible to execute the function and process the result simultaneously. Pl/Sql in it self does not support threads or pipe-lines. Pl/Sql expects the result of a functie-call as a whole and can advance with processing only if the function is completely done.

Object Views

Now you have seen how to create a Collection sauce over a resultset and how a Collection can be queried using Select-statements. An other important addition Oracle 9i are the so called  object views (I say important, but I haven't seen them much out in the open). Object views are views that can return object-instances. This contrast to regular views that return rows with columns.
An object view is defined as follows:

create or replace view car_ov_cars
of car_car_t
with object oid (license)
as
select license
,      category
,      year
,      brand
,      model
,      city
,      country
from   cars

Typical to an object view is that you denote what the object-type is where the view is based upon and what the object-identifier (oid) is. That is actually the attribute or set of attributes that count as a  primary-key of the object.

You could query this view as a regular view, but the strength is in the ability to fetch a row in the form of an object. This is done using the function 'value':

declare
  l_car car_car_t;
begin
  select value(t)
  into l_car
  from car_ov_cars t
  where license = '79-JF-VP';
  l_car.print;
end;

This delivers you an object-instantie from the view without any hassle. Very handy if you're using the objects extensively.

References

When you have a extensive object model, than you might run into objects with one or more collections  as attributes. Those collections can also have multiple instances of other object types. This can become quite memory intensive. Besides that, you can run into the need to implement circulaire-references. For example a department with a manager is an employee him/her self and directs one or more other empoyees. It could be that you wanted to model that as an employee with an attribute typed as a collection type on the employee-type. It could be convenient to have a more louse coupling  between objects.

For that a concept of References is called into live. In fact, a reference is nothing more than a pointer to another object-instance. And that uses less memory than a new object instance. You could refer to an object-instance in a object-table of or an object-view. And than the object-identifier from the previous paragraph comes in handy.

An collection of references is defined as:

create or replace type car_cars_ref_t as table of ref car_car_t;

You can propagate this with the make_ref function:

declare
 l_cars car_cars_ref_t;
 l_car  car_car_t;
begin
  -- Build a collection with references
  select cast(multiset(select make_ref( car_ov_cars
                                      , cae.car_license
                                      )
                       from carsavailable cae) as car_cars_ref_t)
  into l_cars
  from dual;
  -- Process the collection
  if l_cars.count > 0
  then
    for l_idx in l_cars.first..l_cars.last
    loop
      dbms_output.put_line( 'Car '||l_idx||': ');
      -- Get object-value based on the reference 
      select deref(l_cars(l_idx))  
      into l_car
      from dual;
      -- Druk object af
      l_car.print;
    end loop;
  end if;
end;

Here you see that the make_ref needs a reference to an object-view and the particular object identifier. The underlying query than delivers a reference to the objects that need to be processed. That query can be different to the query of the object-view.

What it actually means is that you first determine which  objects are to be taken into account. For those objects you determine a reference/pointer based on the  object-view. And than you can get the actual instance using the reference in a later stage.

The latter is done using the deref-function. This deref-function expects a reference and delivers the actual object instance. The deref is only available in a SQL-function taste, by the way. You cannot use it in Pl/Sql directly. Under water a  'select deref()'-query is translated to a  select on the object-view.

It is important then, to design your object model and object view in a way that the actual query on that object view is indexed properly. The experience learns that it can be quite difficult to determine why the optimiser does or doesn't use the index with derefs.  In that the deref is a nasty abstraction.

The ref that you see in the ref-collection declaration, you can use in the declaration of attributes as well. When you want to use an object as an attribute in another object, for instance an object car in the object garage, than you can use the keyword ref  to denote that you don't want the object itself but a reference:

create or replace type car_garage_t as object
(
  car ref car_car_t
)

Then there is also a ref function that creates references to seperate objects:

select ref(car) reference
,      license
from car_ov_cars car

This function is actually a counterpart of the value-function.
The difference between the functions ref and make_ref is actually that 'ref'  gets the object as a parameter for which a reference must be determined. Make_ref, however is based on an object-view or object-table and determince the reference based upon the primary-key or object-id in the object-view or -table.

The ref-function is used when you ned to create a reference to an object that is a direct result of a query on the object-view. But if you want to determine the primary keys of objects you want to process,  based upon a query on other tabels and/or views than make_ref comes in handy. Because then you deliver the primary-keys of the objects to process separately and  then make_ref uses the object-view and the primary-key values to determine the references.

MAP and Order methods

Now sometimes you need to order a objects. Which one is bigger or smaller and how do I sort them? Obviously this is important when comparing objects but also when querying object-views and object-tables.
For the comparison of objects you can create a map-method:

map member function car_size
return number
is
begin
  return 1000; -- or a calculation of the content of the car, or the prize or fuel-consumption
end; 

In the implementation you can do a calculation on the attributes of the object. The result needs to be of a scalair datatype (number, date, varchar2) and 'normative' for the object with regards to other objects of the same  object-type. The map-method can then be used by Oracle to do comparisons like  l_car1 > l_car2, and comparisons that are implied in  select-clausules as: DISTINCT, GROUP BY, and ORDER BY. Imagine how compact your code can be if you implement methods like these.

You can also make use of an Order method:

order member function car_order(p_car car_car_t)
return number
is
  l_order   number := 0;
  c_smaller constant number := -1;
  c_larger  constant number := 1;
begin
  if licence < p_car.license
  then
    l_order := c_smaller;
  elsif  licence > p_car.license
  then
    l_order := c_larger;
  end if;
  return l_order;
end;

The difference with the map-method is that the map-method returns a value that only has meaning for the object it self. The implicit parameter is only the 'self'-object. Oracle determines the results of the map-method for the two objects to be compared and compares the two results. With the order-method Oracle will provide one object as a parameter to the order-method of the other object. Therefor the order method always needs an extra parameter besides the implicit self-parameter. In the function's implementation you code the comparison between those two objects yourself. And that can be a lot more complex then above. Then you provide a negative value if the self-object is smaller then the provided object and a positive value if the self-object turns out larger. A value of 0 denotes an equalness of the two objects.  The Order-method is used with l_car1 > l_car2 comparisons and always need to have a numeral return datatype.

An object can have only one map-method and one order-method.

Conclusion

Maybe it dazzles you by now. But if you got through to here, then I'm impress. It might seem like a bit boring stuf. And it might seem quite devious if you start with it. Most functionality you need to build can be done in the Oracle 7 way. But certain solution can become a lot more powerful if you do it using object-types. I use them thankfully for years now. But then I am someone who likes to solve a similar problem in a different way the next time it comes around.

Because of object-types Pl/Sql becomes a lot more powerful and it provides you with more handles to solve some nasty performance-problems. Or pieces of functionality that really aren't solvabale int the Oracle 7 way.

And as said in the intro: Oracle Types are really the a game-saver for SOA Suite and Service Bus integrations with the Database Adapter. Because using a hierarchy of objects you'll be able to fetch a complete piece of database with one database call. I even created a Type-Generation-framework (I called it Darwin-Hibernate) that can create types based on the datamodel in the database. It then creates constructors and collection-objects over foreign-keys that allows you to instantiate a complete structure based on the constructor of the top-level object. For instance a Patient with all it's medical records, addresses, etc.

Al the examples already work with Oracle 9i. But under 10g, 11g, 12c it will run a lot smother and faster because of the performance optimalisations of the Pl/Sql-engine.(Oracle 9i was not quite a performance topper).

This wasn't a story about Object Oriented Pl/Sql, actually. I didn't talk about super and sub-types. you can read about that in Chapter 12 of the Pl/Sql User's Guide enReference van Oracle 10g (I really ran into that page when Googling on it...). Or this page in 11g.
But I wanted you to get started with Object Types, and show you what you can do with it and how powerful Pl/Sql has become with it.

For some more advanced stuff you can read my earlier article about type inheritance, regarding EBS, but interesting enough for non-EBS developers. And another one. And yet another one.

Have fun with Pl/Sql (you might think by now that I really feel Pl/Sql needs this uplift), because I think with Object Types Pl/Sql is really fun. The scripts and datamodel for the examples can be found here.