Hi!

I've been searching the web for some days now and can't find a way to denormalize a composite oracle object (an oracle object whose one of its attributes is another oracle object) in such a way that I can execute one single insert statement.

The idea would be to have an oracle object Parent with a simple set of attributes (oracle "native" types) together with an oracle object attribute (or a list of them) such as:

Create or Replace Type T_Child_Obj As Object
(
  attribute_c VARCHAR2(5)
);

Create or Replace Type T_Child_Obj_Tbl IS TABLE OF T_Child_Obj;

Create or Replace Type T_Parent_Obj As Object
(
  attribute_p VARCHAR2(10),
  child_objects T_Child_Obj_Tbl
);

Create or Replace Type T_Parent_Obj_Tbl IS TABLE OF T_Parent_Obj;

I also have a parent table and a child table, which are alternative and compatible representations of the given objects:

CREATE TABLE parent_table (attribute_p VARCHAR2(10));
CREATE TABLE child_table (attribute_p VARCHAR2(10), attribute_c VARCHAR2(5));

I wish to traverse a parent object collection, dump parent record into parent_table and then use the child collection to dump into the child table in a the most bulkish way possible.

To do so, consider that I have a set of T_Parent_Obj stored in parent_obj_tbl, as seen below. For writing into the parent table, I would do it like this:

declare
  parent_obj_tbl T_Parent_Obj_Tbl;
  child_obj_tbl  T_Child_Obj_Tbl;
  parent_obj     T_Parent_Obj;
  child_obj      T_Child_Obj;
--
begin
  child_obj  := new T_Child_Obj('son');
  child_obj_tbl := new T_Child_Obj_Tbl(child_obj);
  parent_obj := new T_Parent_Obj('father', child_obj_tbl);
  parent_obj_tbl := new T_Parent_Obj_Tbl(parent_obj);
  
  insert into parent_table (SELECT attribute_p FROM TABLE(parent_obj_tbl) );
end;

And it actually works... but when I try the same approach for the child table, I can't find a way to tell Oracle how to flatten that child object collection:

declare
  parent_obj_tbl T_Parent_Obj_Tbl;
  child_obj_tbl  T_Child_Obj_Tbl;
  parent_obj     T_Parent_Obj;
  child_obj      T_Child_Obj;
--
begin
  child_obj_tbl := new T_Child_Obj_Tbl(
                         new T_Child_Obj('son1'),
    new T_Child_Obj('son2'));
  parent_obj := new T_Parent_Obj('father', child_obj_tbl);
  parent_obj_tbl := new T_Parent_Obj_Tbl(parent_obj);

  insert into parent_table (SELECT attribute_p FROM TABLE(parent_obj_tbl) );
  insert into child_table (SELECT attribute_p, (SELECT attribute_c FROM TABLE(child_objects)) FROM TABLE(parent_obj_tbl));

end;

I get an error because I'm returning a collection along with a single column value to the same SELECT expression, of course.
I believe I would need to join the attribute_p with the attribute_c collection,
by treating both as collections and then select from there, but I can't find a way to express in SQL... both queries need to be interdependent but there's the TABLE transformations in the way, blocking me from doing it... :c (

Is there really no way of accomplishing this? Should I not be trying to do it in the first place? My idea is to be able to bulk insert records whilst taking advantage of the oracle objects functionality. I also intend not to need to rewrite the insert code each time a new attribute is added to both tables and objects (which will always be synchronized in structure).

Thank you very much!

Mário Barbosa

ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> declare
  2    parent_obj_tbl T_Parent_Obj_Tbl;
  3  begin
  4      parent_obj_tbl :=
  5        t_parent_obj_tbl(
  6           t_parent_obj( 'Father', t_child_obj_Tbl( t_child_obj( 'a' ), t_child_obj( 'b' ) ) ),
  7           t_parent_obj( 'Mother', t_child_obj_Tbl( t_child_obj( 'c' ), t_child_obj( 'd' ) ) )
  8        );
  9  
 10    open :x for select attribute_p, attribute_c
 11                  from (select x.attribute_p, x.child_objects
 12                          from table(parent_obj_tbl) x), table(child_objects);
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

ATTRIBUTE_ ATTRI
---------- -----
Father     a
Father     b
Mother     c
Mother     d