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.