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.
