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.

Triggered by a comment on an older article I wrote a little article yesterday on determining the instance of an object. As far as known by me at least and seemingly by Google there is no java instanceOf counterpart in Oracle Pl/Sql. In SQL Where clauses you can use the 'is of' expression. But that is not available in pl/sql. I tried some suggestions with embedded sql but I couldn't get it to work. The suggestion from a forum I found yesterday was to use function overloading. I played around a little with it and it seems I stumbled on a fairly simple method. Let's say I've a type like the following:

CREATE OR REPLACE TYPE "DWN_PERSON" AS OBJECT 
( 
name varchar2(40)
, member function instance_of return varchar2
) not final;
/
CREATE OR REPLACE TYPE BODY "DWN_PERSON" AS
  member function instance_of return varchar2 AS
  BEGIN
    return 'DWN_PERSON';
  END instance_of;
END;
/

I just created a simple Instance_of method that does nothing but returning the name of the object. It was my first step in making things spiffier later on. Keeping in mind that it probably should become an overloading function with a parameter. Then I created a Natural Person type:

  CREATE OR REPLACE TYPE "DWN_NATURAL_PERSON" under DWN_PERSON 
( surname varchar2(100)
, overriding member  function instance_Of return varchar2 
) ;
/
CREATE OR REPLACE TYPE BODY "DWN_NATURAL_PERSON" AS
  overriding member  function instance_Of return varchar2 AS
  BEGIN
    RETURN 'DWN_NATURAL_PERSON';
  END instance_Of;
END;
/

And because I was going so well, I created a Not Natural Person:

CREATE OR REPLACE TYPE "DWN_NOT_NATURAL_PERSON" under DWN_PERSON 
( companyname varchar2(100)
, overriding member  function instance_Of return varchar2 
) ;
/
CREATE OR REPLACE TYPE BODY "DWN_NOT_NATURAL_PERSON" AS
  overriding member  function instance_Of return varchar2 AS
  BEGIN
    RETURN 'DWN_NOT_NATURAL_PERSON';
  END instance_Of;
END;
/

Then I created the following little script:

declare
l_person1 dwn_person;
l_person2 dwn_person;
l_person3 dwn_person;

function create_person(p_name varchar2, p_surname varchar2, p_comany_name varchar2) return dwn_person
as
l_person dwn_person;
begin
  if p_surname is not null then
  l_person := dwn_natural_person(p_name, p_surname);
  elsif p_comany_name is not null then
    l_person := dwn_not_natural_person(p_name, p_comany_name);
  else
  l_person := dwn_person(p_name);
  end if;
 return l_person;
end;

begin
  l_person1 := create_person('Flip', 'Fluitketel', null);
  dbms_output.put_line('l_person1 is a '||l_person1.instance_of);
  l_person2 := create_person('Hatseflats', null, null);
  dbms_output.put_line('l_person2 is a '||l_person2.instance_of);
  l_person3 := create_person('Hatseflats', null, 'Hatseflats Inc.');
  dbms_output.put_line('l_person3 is a '||l_person3.instance_of);
end;

All three persons are declared as a 'DWN_PERSON'. So when I call the instance_of method of the particular object I expected that the method of the particular declared type is called. But, a little to my surprise, it turns out that Oracle uses the method of the Instantiated type:

l_person1 is a DWN_NATURAL_PERSON
l_person2 is a DWN_PERSON
l_person3 is a DWN_NOT_NATURAL_PERSON

Simple, but apparently very effective.

Already four and a half year ago I wrote an article on Oracle Type inheritance and type casting.  See here. But although Object types are supported by Oracle since Oracle 8, it seems to me that there is still little familiarity on the subject.
I'm not really a Pl/Sql programmer on daily basis anymore, the language is still close to me. And working with object types is a favorite subject to me ever since I discovered the endless use in Oracle 8i. Know Object types and you can do everything in Oracle.

Today I got a comment on the article, asking on if there is a solution on "downcasting" types. The subject in the mentioned article was that from an API (in the example from an EBusiness Suite12  API) you get an object type to which you want to add functionality without re-implementing/changing the delivered object type. The only thing to do it is to sub-type the delivered type. But how to come from an instance of a supertype to the use it as the subtype. Well, the answer is two fold:

1. If the instance of the super type is actually instantiated as the supertype then you actually can't. As explained in the mentioned article, the only way is to instantiate the subtype with the attributes of the supertype. For what I think I found a smart generic solution.
2. If you get an instance of the supertype, that was actually instantiated as the subtype then you can copy it again to a variable based on the subtypehttp://blog.darwin-it.nl/ and your good to go.

Lets elaborate on the second option. I'll do a dry swim, so the code presented is not tested in a database, so might not be free from syntax errors.

In Java there is a  statement called "InstanceOf". In Oracle SQL you have the "Is Of"construct. I found a forum post with the question about an "InstanceOf" analogy in Pl/Sql. As in the forum is stated the "Is Of" clause is in fact a possible where clause condition in a query. See also the documentation.

However in the post I saw a smart comment: create an overloaded procedure:

create procedure p_x(p_obj super_type);

do supertype code here



create procedure p_x(p_obj sub_type);

do subtype code here 

Now let's say we have a type named 't_car' and a subtype say 't_opel_zafira under t_car':

create type t_car as object
(license_plate varchar2(10) )
/

create type t_opel_zafira under t_car 
(model varchar2(10))
/

Then you could create an overloaded function like

create function instance_of(p_obj t_car ) returns varchar2
as
begin
  return 't_car';
end;

create function instance_of(p_obj t_opel_zafira ) returns varchar2
as
begin
  return 't_opel_zafira';
end;

Then you could indeed do something like:

declare
  l_car t_car;
  l_opel_zafira t_opel_zafira;
begin
  l_car := instance_opel_zafira_as_car(...); -- This functions returns a value of type t_car but is in fact an Opel Zafira
  if instance_of(l_car) = 't_opel_zafira' then
    dbms_output.put_line('Enjoy your Opel Zafira');
  end if;
end;

I should try it, but you should even be able to add a such an instance_of member function to each object type. Going to try that tomorrow.

If you try to test a service generated and deployed using the Service Provider in SOA Gateway, you might run into problems while the response message does not provide the appropriate messages on the functional errors.
You passed the security authentication and the apps-authorisation and the in the SOA Monitor the webservice shows a succesful execution. But there might be fnctional errors like a lookup violation that are not shown in the response message.

Then it might be usefull to just call the corresponding pl/sql api from a test script.

Below you'll find a script to test the Create Person of the Public Party API. It's a Pl/Sql Developer test script that you might adapt to an sql plus or sqldeveloper script.

declare
  -- FND UserName
  c_user_name          constant varchar2(30) := 'ASADMIN';
  c_responsibility_key constant varchar2(30) := 'HZ_TCA_MANAGER';
  -- Type for repsonsibility record
  type t_responsibility_rec is record(
    user_id            fnd_user.user_id%type,
    user_name          fnd_user.user_name%type,
    responsibility_key fnd_responsibility.responsibility_key%type,
    responsibility_id  fnd_responsibility.responsibility_id%type,
    appplication_id    fnd_responsibility.application_id%type);
  g_responsibility xxx_profile.t_responsibility_rec;
  -- Person Record
  p_person_rec hz_party_v2pub.person_rec_type;
  -- Error Message Fields
  l_error_text varchar2(32767);
  l_msg_count  number;
  cursor c_usr(b_user_name in fnd_user.user_name%type) is
    select usr.user_id, usr.user_name
      from fnd_user usr
     where usr.user_name = b_user_name;
  r_usr c_usr%rowtype;
  cursor c_rsp(b_responsibility_key in fnd_responsibility.responsibility_key%type) is
    select rsp.application_id,
           rsp.responsibility_id,
           rsp.responsibility_key
      from fnd_responsibility rsp
     where rsp.responsibility_key = b_responsibility_key;
  r_rsp            c_rsp%rowtype;
begin
  -- Query Responsibility Id's
  open c_usr(b_user_name => c_user_name);
  fetch c_usr
    into r_usr;
  if c_usr%found then
    g_responsibility.user_id   := r_usr.user_id;
    g_responsibility.user_name := r_usr.user_name;
    open c_rsp(b_responsibility_key => c_responsibility_key);
    fetch c_rsp
      into r_rsp;
    if c_rsp%found then
      g_responsibility.responsibility_key := r_rsp.responsibility_key;
      g_responsibility.responsibility_id  := r_rsp.responsibility_id;
      g_responsibility.appplication_id    := r_rsp.application_id;
    end if;
    close c_rsp;
  end if;
  close c_usr;
  -- Set Apps context
  fnd_global.apps_initialize(user_id      => g_responsibility.user_id,
                             resp_id      => g_responsibility.responsibility_id,
                             resp_appl_id => g_responsibility.appplication_id);
  -- Set Person Record
  p_person_rec.PERSON_FIRST_NAME          := 'Jean';
  p_person_rec.PERSON_MIDDLE_NAME         := 'Michel';
  p_person_rec.PERSON_LAST_NAME           := 'Jarre';
  p_person_rec.PERSON_INITIALS            := 'JM';
  p_person_rec.PERSON_NAME_PHONETIC       := 'sjan misjel sjar';
  p_person_rec.PERSON_FIRST_NAME_PHONETIC := 'sjan';
  p_person_rec.PERSON_LAST_NAME_PHONETIC  := 'sjar';
  p_person_rec.MIDDLE_NAME_PHONETIC       := 'misjel';
  p_person_rec.DATE_OF_BIRTH              := to_date('1948-08-26',
                                                     'yyyy-mm-dd');
  p_person_rec.PLACE_OF_BIRTH             := 'Lyon';
  p_person_rec.GENDER                     := 'MALE';
  p_person_rec.DECLARED_ETHNICITY         := 'French';
  p_person_rec.created_by_module          := 'HZ_WS';
  -- Call the procedure
  hz_party_v2pub.create_person(p_init_msg_list    => :p_init_msg_list,
                               p_person_rec       => p_person_rec,
                               p_party_usage_code => :p_party_usage_code,
                               x_party_id         => :x_party_id,
                               x_party_number     => :x_party_number,
                               x_profile_id       => :x_profile_id,
                               x_return_status    => :x_return_status,
                               x_msg_count        => :x_msg_count,
                               x_msg_data         => :x_msg_data);
  l_msg_count  := fnd_msg_pub.Count_Msg;
  l_error_text := '';
  if l_msg_count = 1 then
    l_error_text := 'API Error: ';
  end if;
  if l_msg_count >= 1 then
    for i in 1 .. l_msg_count loop
      l_error_text := nvl(l_error_text, ' ') || chr(10) || i || '. ' ||
                      fnd_msg_pub.get(p_encoded => fnd_api.g_false);
    end loop;
  
  end if;
  :error := l_error_text;
end;

A few years ago I wrote an article on Oracle Object types. You can find it on our whitepaper page, referenced as "Object Oriented Pl/sql". It's still Dutch, maybe I should make some time to translate it.

In E-Business Suite nowadays (R12) the developers also found the advantage of using object types. One of the comments I had on the TCA (Tracing Community Architecture)-API's I had was that they were too granular. From BPEL PM you had to do several (about 4) calls with the EBS or DB-adapter to add a Party with it's address. Now there are BO (Business Object) API's, based on hierarchies of types.

These types are quite sober, consisting only of attributes and sometimes a static function that returns an instantiated type (somehow they choose not to create a constructor).

I wanted to extend some of these types to add functionality. Basically it's not too hard, but I ran into some challenges.

Extending base types
First let's create a parent type as an example.

create or replace type xxx_parent as object
(
-- Author  : MAKKER
-- Created : 17-03-2009 08:10:32
-- Purpose :

-- Attributes
id number,
name varchar2(30),
description varchar2(100),
-- Member functions and procedures
constructor function xxx_parent return self as result
)
/
create or replace type body xxx_parent is

-- Version : $Id$
/* Member constructor, procedures and functions  */
constructor function xxx_parent return self as result is
begin
  return;
end;
end;
/

As you can see it's a simple type with a few attributes and a parameterless constructor. I'm used to allways add such a parameterless constructor. It often turns out handy in Pl/Sql since a type allways has a default constructor with all the attributes as a parameter. But often you want to instantiate a type and fill (a few of) the attributes later on.

I want to extend this type with a child. But to do so it must be 'not final'. By default, because of backwards compatibility, it is declared final.
You can declare a type explicitly being not final by adding the 'not final' keywords to the type specification. For example (from the oracle docs):

CREATE TYPE person_typ AS OBJECT (
idno           NUMBER,
name           VARCHAR2(30),
phone          VARCHAR2(20),
MAP MEMBER FUNCTION get_idno RETURN NUMBER,
STATIC FUNCTION show_super (person_obj in person_typ) RETURN VARCHAR2,
MEMBER FUNCTION show RETURN VARCHAR2)
NOT FINAL;
/

But in the end I want to extend the Oracle EBS BO-types, without specifically modifying the source. The other way is by altering the type:

alter type xxx_parent not final;

This command will be added as a seperate line to the source. Try for example:

select type, text from user_source where name = 'XXX_PARENT'
order by type, line

Now I can create a child object that extends this one:

create or replace type xxx_child under xxx_parent
(
-- Author  : MAKKER
-- Created : 17-03-2009 08:12:43
-- Purpose :

member procedure show,

-- Member functions and procedures
constructor function xxx_child return self as result
)
/
create or replace type body xxx_child is
-- Version : $Id$
/* Member constructor, procedures and functions  */
member procedure show is
begin
  dbms_output.put_line('Id: ' || self.id || ', name: ' || self.name ||
  ', description: ' || self.description);
end;
constructor function xxx_child return self as result is
begin
  return;
end;
end;
/

This type extends the parent by adding just a show method, showing the attributes.

Casting super-types to sub-types v.v.
Now the following works:

declare
  l_child xxx_child;
  l_parent xxx_parent;
begin
  l_child := xxx_child(id => 1, name => 'Martien', description => 'Dad');
  l_child.show;
end;

With as output:

Id: 1, name: Martien, description: Dad

However, what I wanted to achieve was something like this:

declare
  l_child xxx_child;
  l_parent xxx_parent;
begin
  l_parent := xxx_parent(id => 1, name => 'Martien', description => 'Dad');
    l_child := l_parent; -- PLS-00382: expression of wrong type
  l_child.show;
end;

But you cannot assign a super-type to a sub-type, when it's instantiated as a super-type.
You can do this though:

declare
  l_child xxx_child;
  l_parent xxx_parent;
begin
  l_parent := xxx_child(id => 1, name => 'Martien', description => 'Dad');
  l_child := treat(l_parent as xxx_child);
  l_child.show;
end;

In the assignment you explicitly tell Pl/Sql to treat the parent as a child object. You can do this only if the parent was explicitly instantiated as a child on before hand. If you instantiate it as a parent and try to treat is as a child you get the message: "ORA-06502: PL/SQL numeric or value error: cannot assign supertype instance to subtype".

Challenge: how to cast a super-type to a sub-type
My problem is thus: "How do I cast my super-type to a sub-type". When I create for example a customer account site in EBS using the BO-API's then I have no problem. I create a sub-type of my own extending the EBS BO and when I instantiate it as my own child object type and hand it over to the api, it should work. Since it's instantiated as my sub-type. But the problem lies in the update and get API's. For doing an update I should first do a get, retrieving the BO from EBS. The get-api will instantiate a type as an EBS BO-type. And I want to cast that to my own custom types to use my own methods. And basically this is impossible.

However, I found myself a trick. What I should do is to instantiate a child object with the attribute values of the parent. So if I have a collection of parents like:

create or replace type xxx_parent_tbl is table of xxx_parent

I can do:

declare
  l_child xxx_child;
  l_parent xxx_parent;
  l_parent_tbl xxx_parent_tbl;
begin
  l_parent_tbl := xxx_parent_tbl();
  l_parent := xxx_parent(id => 1, name => 'Martien', description => 'Dad');
  l_parent_tbl.extend;
  l_parent_tbl(l_parent_tbl.count) := l_parent;
  select xxx_child( ID,NAME,DESCRIPTION)
  into l_child
  from table(l_parent_tbl)    
  where rownum = 1;
  l_child.show;
end;

Now since this works fine, I want to do it more dynamically. The objects of EBS can have a large number of attributes and I don't want to name them explicitly. An upgrade of EBS would force me to upgrade my custom types too. Also I want my casting solution portable and reusable.

To get the attributes of my parent I can do the following query:

select attr_name
from user_type_attrs att
where att.type_name = 'XXX_PARENT'
order by attr_no;

The order by is important, since the default constructor has all the attributes as a parameter in this order.

To do an execute immediate selecting from my parent-table into my child object I need a sql statement like the following:

begin select xxx_child( ID,NAME,DESCRIPTION) into :1  from table(:2)  where rownum = 1;   end;

The where clause is not necessary because my collection will contain only one entry. But I added it to explicitly guarantee that the select into will result in only one row.
Now I create a function that generates this sql:

create or replace function xxx_get_type_cast_sql(p_parent in varchar2,  p_child  in varchar2) return varchar2 is
  l_sql varchar2(32767);
  cursor c_att(b_parent varchar2) is
  select *
  from all_type_attrs
  where type_name = b_parent
  order by attr_no;
begin
  l_sql := 'begin
  select '||p_child||'( ';
  for r_att in c_att(b_parent=>p_parent) loop
    if c_att%rowcount = 1 then
      l_sql := l_sql || r_att.attr_name;
    else
      l_sql := l_sql ||','|| r_att.attr_name;
    end if;
  end loop;
  l_sql := l_sql || ') into :1  from table(:2)  where rownum = 1;   end;';
  return(l_sql);
end xxx_get_type_cast_sql;

With this I can add another constructor to my child object with the parent object as a parameter:

create or replace type xxx_child under xxx_parent
(
-- Author  : MAKKER
-- Created : 17-03-2009 08:12:43
-- Purpose :

member procedure show,

-- Member functions and procedures
constructor function xxx_child return self as result,
constructor function xxx_child(p_parent xxx_parent) return self as result
)
/
create or replace type body xxx_child is

-- Version : $Id$
/* Member constructor, procedures and functions  */
member procedure show is
begin
  dbms_output.put_line('Id: ' || self.id || ', name: ' || self.name ||
  ', description: ' || self.description);
end;
constructor function xxx_child return self as result is
begin
  return;
end;
constructor function xxx_child(p_parent xxx_parent) return self as result is
  l_sql        varchar(32767);
  l_parent_tab xxx_parent_tbl;
begin
  -- Put parent in a collection;
  l_parent_tab := xxx_parent_tbl();
  l_parent_tab.extend;
  l_parent_tab(l_parent_tab.count) := p_parent;
  -- Create the sql
  l_sql := xxx_get_type_cast_sql(p_parent => 'XXX_PARENT'
  ,p_child  => 'XXX_CHILD');
  -- executed it dynamically
  execute immediate l_sql
  using in out self, in out l_parent_tab;
  return;
end;
end;
/

And now I can succesfully cast my parent to a child and use the childs methods:

declare
  l_parent xxx_parent;
  l_child xxx_child;
begin
  l_parent := xxx_parent(1, 'Berend', 'Son');
  l_child :=  xxx_child(p_parent=>l_parent);
  l_child.show();
end;

Which shows the following output:

Id: 1, name: Berend, description: Son

Conclusion and final thoughts
Casting from super-types to sub-types is not possible. Actually I'm not casting of course, but dynamically instantiating a child from the attributes of the parent. The way I do it requires that the child does not add attributes, or at least has a constructor with the same parameters as the parent. Maybe with some extra thinking I could make it a little smarter to work away this requirement.

At each instantiation the sql-statement is build using the function and the query on user_type_attrs. Actually you have to do it only once. If you encounter performance problems you can run the function once and copy and paste the outcome into the source of the object.

Use execute immediate constructs with care. In this case I'm sure it will not lead in particular performance problems. The sql resulting from the query will allways be the same as long as the parent-object does not change in attributes. Since I also use bind-variables in the execute immediate (through the 'using...' clause) the sql will be in the SQL-Area only once.

Yesterday I wrote about how you can (re)compile an object with the debug option. Now it might be handy to recompile an object with the debug option off. But to do that automatically you need to know which objects are compiled with the debug option. For you don't want to recompile your whole schema do you? I don't want it, since my schema is the APPS schema of EBS and it contains a billion packages...

So how would you do that? I was looking for a neat view that gives me this information. And browsing through all the 'ALL_%' views I stumbled on the nice view: all_stored_settings. It contains all kinds of parameters about the stored pl/sql objects.
The one I wanted to query on is the plsql_debug parameter. Mark that the parameter names are lower-case while the True/False values are Upper-case.

Besides Owner, Object_Name, Object_Type columns it also has the Object_id column. So to getall the packages with the debug option on simply join the view with the all_objects view:

select obj.* 
from all_objects obj
, all_stored_settings ssg
where ssg.object_id = obj.object_id
and ssg.param_name = 'plsql_debug'
and ssg.param_value = 'TRUE'
and obj.object_type in ('PACKAGE', 'PACKAGE BODY')

So to recompile all these packages I changed the script of yesterday:

declare
  cursor c_obj 
  is select obj.* 
  from all_objects obj
  , all_stored_settings ssg
  where ssg.object_id = obj.object_id
  and ssg.param_name = 'plsql_debug'
  and ssg.param_value = 'TRUE'
  and obj.object_type in ('PACKAGE', 'PACKAGE BODY');
  l_command varchar2(32767);
  procedure put_line(p_text in varchar2) is
    l_text varchar2(32767) := p_text;
    l_line varchar2(255);
  begin
    while length(l_text) > 0 loop
      l_line := substr(l_text, 1, 255);
      dbms_output.put_line(l_line);
      l_text := substr(l_text, 256);
    end loop;
  end put_line;
begin
  for r_obj in c_obj
  loop
    begin
      l_command := 'alter package ' ||r_obj.object_name || ' compile';
      if r_obj.object_type = 'PACKAGE BODY' then
        l_command := l_command || ' body';
      end if;
      put_line('Executing: '||l_command);
      execute immediate l_command;
      put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name|| '.'));
    exception
      when others then
        put_line('Error compiling '||lower(r_obj.object_type||' '||r_obj.object_name));
    end;
  end loop;
end;

Of course this script can also easily be changed to add debug info for those packages that haven't yet...

The very main reason that I got to use and love Pl/Sql Developer years ago is it's debug capabilities. Back then I used Toad freeware like my colleagues while we had a PSD-license on the project. I found out that PSD had a debugger, a tool that I missed in writing Pl/Sql. Surprisingly I thought, because in my student-years I programmed Turbo Pascal under Dos and even that had a powerful debugger.

Before you can debug you have to add debug info to your pl/sql. By default Pl/Sql Developer compiles the packages with the debug option on. Until last week I did not know what the appropriate statement-clause was. But since PSD does it for me, I did not care.
It turns out to be:
alter package compile debug body

I'm currently working again with the Pl/Sql public api's of E-Business Suite. And I have to find out what particular values I have to pass. So being able to debug into the EBS packages turns out to be helpfull. However recompiling the ebs-packages invalidates referencing packages. Now that's not too much of a problem, but some of them fail with a "PLS-00123:program too large" error.
I was very surprised since I thought that was a pre-8i error (we're working with DB10gR2).

I did not dug to deep in it, but found it very unconvenient to find out what packages could and which couldn't be compiled with debug option. So today I created a script, actually a script that I created many times before, but now in a little smarter way.

It uses a cursor to list all the invalid packages and package-bodies that are invalid. In the loop it tries to compile them with the debug option. If that fails than it tries to compile them without the debug option. If that fails again, then there is something more problematic wrong.

Here is the script:

declare
  cursor c_obj 
  is select * from all_objects obj
  where obj.object_type in ('PACKAGE', 'PACKAGE BODY')
  and obj.status = 'INVALID';
  l_command varchar2(32767);
  procedure put_line(p_text in varchar2) is
    l_text varchar2(32767) := p_text;
    l_line varchar2(255);
  begin
    while length(l_text) > 0 loop
      l_line := substr(l_text, 1, 255);
      dbms_output.put_line(l_line);
      l_text := substr(l_text, 256);
    end loop;
  end put_line;
begin
  for r_obj in c_obj
  loop
    begin
      -- First try with debug
      l_command := 'alter package ' ||r_obj.object_name || ' compile debug';
      if r_obj.object_type = 'PACKAGE BODY' then
        l_command := l_command || ' body';
      end if;
      put_line('Executing: '||l_command);
      execute immediate l_command;
      put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name|| ' with debug option.'));
    exception
      when others then
      begin
        -- Then try without debug
        l_command := 'alter package ' ||r_obj.object_name || ' compile ';
        if r_obj.object_type = 'PACKAGE BODY' then
          l_command := l_command || ' body';
        end if;
        put_line('Executing: '||l_command);
        execute immediate l_command;
        put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name));
      exception
        when others then
          put_line('Error compiling '||lower(r_obj.object_type||' '||r_obj.object_name));
      end;
    end;
  end loop;
end;

As (probably) every other consultant I don't like the task of documenting my source. It's necessary, but boring. And what do you document and what not? And what if you add a parameter? It would be handy if you can generate your documentation, wouldn't it?
And it can: using the pl/sql doc plug-in of Pl/Sql Developer.

See http://www.allroundautomations.nl/plsplsqldoc.html.

Already a few years a I wrote an article on it. For our Dutch readers and our non-dutch readers who are that linguistic that they haven't any problems with our beautiful language, you can download it here.

I used to use XMLTypes with xml where I tend to prevent the usage of namespaces. Namespaces make things difficult where they are often unneeded. That is I experienced that it is hard. An expression like '/level1/level2/level3' is much simpeler then '/ns1:level1/ns2:level2/ns3:level3'. Especially if you don't know how to give in the particular namespaces.

Lately I wanted to store the results of a BPEL Process in the database to be able to query parts from that in a later process. Actually I processed several files in the one process and wanted to query the statuses in the later process again. If you work with BPEL you cannot avoid using namespaces. I also wanted to put the results in an XMLtype as is, since it prevented me to create a complete datamodel. A simple xmltype-table suffices.

I then created a pl/sql function with the filename as a parameter that fetched the right row in the results table and gave back the xmltype column in which the status of that file was stored. But then, how to query it with XPath?

I knew the xmltype.extract() function. But what next? You can avoid namespaces by using the local-name() xpath function, but then it is a little hard to get the value of the particular file.

Luckily the extract() function has another parameter, the namespace string:

extract(XMLType_instance IN XMLType,
XPath_string IN VARCHAR2,
namespace_string In VARCHAR2 := NULL) RETURN XMLType;

This namespace string can contain the namespace declarations to be used in an xpath expression. The namespace declarations look like:
namespace-shortage=uri
for example
ns1="http://www.example.org/namespace1"
You can have multiple declarations separated by white space.

An example is as follows:

declare
xp_no_data_found exception;
pragma exception_init(xp_no_data_found, -30625);
l_xpath varchar2(32767) := '/ns1:level1/ns2:level2/ns3:level3';
l_nsmap varchar2(32767) := 'ns1="http://www.example.org/namespace1" ns2="http://www.example.org/namespace2" ns3="http://www.example.org/namespace3"';
l_xml xmltype;
l_clob clob;
begin
l_xml := function_that_gets_an_xmltype_value();
l_clob := l_xml.extract(l_xpath, l_nsmap).getclobval();
end;

Read more about it on page 4.6 of: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259.pdf.

Mark also that there are actually two functions: extract() and extractvalue(). Extract() returns an xmltype, that can be 'sub-queried'. The extractvalue() function returns the datatype of the variable that the value is assigned to. With the xmltype functions getstringval(), getclobval(), etc. you can also get the particular value of a node of the xmltype. However, there is a slight difference between the result of the getstringval() and correspondingfunctions and Extractvalue. And that is that the Extractvalue returns the unescaped value of the node (the encoding entities are unescaped), while getstringval() returns the value with the entity encodings intact .

Recently I had to build, test and debug some Pl/Sql. I use my all time favourite tool Pl/Sql Developer. But it happens when debugging Pl/Sql that for no apparent reason the debug-session hangs. Especially when hitting the Break button in Pl/Sql Developer, it may happen.
When it happens you'll have to kill Pl/Sql Developer the hard way, using Taskmanager. Because it refuses to quit while there is a session running.

Fortunately, there is a pretty simple solution.

When your test script looks like:

-- Created on 1/2/2009 by MAKKER
declare
  -- Local variables here
  i integer;
begin
  -- Test statements here
  :result := sos_log.log_run(p_schedule_id => :p_schedule_id,
  p_status => :p_status);
end;

Then just declare an extra variable l_time_out of data type number. Then set a time of for example 5 seconds. using the dbms_debug.set_timeout:

declare
  l_time_out number;
-- Local variables here
  i integer;
begin
  l_time_out := dbms_debug.set_timeout(5);
  -- Call the function
  :result := sos_log.log_run(p_schedule_id => :p_schedule_id,
  p_status => :p_status);
end;

When Pl/Sql Developer looses the connection to the debug session the debug session will be freed after the timeout. Pl/Sql Developer gets the control back and you can go on with compiling and debugging.

You can add it easily to the default test template. Unfortunately Pl/Sql Developer does not use the template when you do right-click->test on a program-unit.
For that you could create a macro that adds the code to your test script, and connect it to a key-stroke.

Last week I read a nice article of my con-colleague Lucas Jellema about “PL/SQL Table-to-Java Bean (and Data to Java Bean Manager) generator - useful for data driven demos without database ” at the AMIS-blog on:http://technology.amis.nl/blog/?p=3272.

In this blog Lucas writes how he generated java-code from Pl/Sql. It drew me back to a little project I did for myself and my former employer as a BusinessDevelopment-job. I wanted to make a simple method to enable a convential custom development application, like a Designer/Developer Forms application, like we build a million times in the past. Since it is primarly aimed for the hundreds of Forms applications at our customers that are primarly build with Designer and Headstart, I could do it in pl/sql also or even create a Headstart utility for it. But for the latter option I would learn myself Headstart-utility principals again, that did not look to useful for me. Pl/Sql would do. But I would have it flexible having the ability to generate several pieces of code from one source.

The problem with the code of Lucas, that is when you see it as a problem, is that all the code generation is in pl/sql. To change the outcome would mean that you should change your pl/sql code. I would like to have a more template-based solution.

A few years ago I wrote an article on “XML using Sql” for our monthly consulting paper at Oracle. See my post: http://darwin-it.blogspot.com/2008/07/xml-using-sql.html. With this knowledge it is easy to generate XML out of the datadictionary of the database. Then based on this xml you can generate all you want using ... XSLT.

Read further (pdf).

Another article that I wrote a few years ago is about Object Oriented Pl/Sql. It's also in Dutch and downloadable here.