2 Replies Latest reply: Jun 18, 2011 5:02 PM by gaverill RSS

    Inserting into a doubly nested table through an object view

    448589
      Can anyone give me an example of an INSTEAD OF trigger that will mediate an INSERT into a doubly nested table of an Object View? Is there syntax that will allow it?
        • 1. Re: Inserting into a doubly nested table through an object view
          867194
          Can anyone show the example of doubly nested table?
          I've got like:

          create type ot_1 as object(i number);
          create type list_1 as table of ref ot_1;

          create type ot_2 as object(liist11 list_1);
          create type list_2 as table of ref ot_2;

          create type ot_3 as object(list22 list_2);

          How can I now create nested table in another nested table, like
          create table t_3 of ot_3
          nested table list22 store as t_32 .......?
          • 2. Re: Inserting into a doubly nested table through an object view
            gaverill
            Here's some code to demonstrate. Note that relational tables, not an object table, are used to store object instances:
            create or replace type TInnerNestedTable
            is table of varchar2(20)
            /
            
            create or replace type TOuterNestedTable
            is table of TInnerNestedTable
            /
            
            create or replace type TMyObject
            is object
            (
                 id     varchar2(20)
            ,     tab     TOuterNestedTable
            )
            /
            
            create
            table     T_MY_OBJECT
            (
                 id          varchar2(20)     not null
            --
            ,     primary key (id)
            )
            /
            
            create
            table     T_MY_OBJECT_TAB_OUTER
            (
                 id          varchar2(20)     not null
            ,     outerIndex     integer          not null
            --
            ,     primary key (id, outerIndex)
            ,     foreign key (id) references T_MY_OBJECT on delete cascade
            )
            /
            
            create
            table     T_MY_OBJECT_TAB_INNER
            (
                 id          varchar2(20)     not null
            ,     outerIndex     integer          not null
            ,     innerIndex     integer          not null
            ,     innerValue     varchar2(20)
            --
            ,     primary key (id, outerIndex, innerIndex)
            ,     foreign key (id, outerIndex) references T_MY_OBJECT_TAB_OUTER on delete cascade
            )
            /
            
            create or replace view V_MY_OBJECT
            of TMyObject
            with object identifier (id)
            as
            select     t.id
            ,     cast(multiset(
                      select     cast(multiset(
                                select     i.innerValue
                                from     T_MY_OBJECT_TAB_INNER i
                                where     i.id = o.id
                                and     i.outerIndex = o.outerIndex
                           ) as TInnerNestedTable)
                      from     T_MY_OBJECT_TAB_OUTER o
                      where     o.id = t.id
                 ) as TOuterNestedTable)
            from     T_MY_OBJECT t
            /
            
            create or replace trigger TR_II_V_MY_OBJECT
            instead of insert on V_MY_OBJECT
            for each row
            begin
                 insert
                 into     T_MY_OBJECT
                 (
                      id
                 )
                 values     (
                      :new.id
                 );
            
                 insert
                 into     T_MY_OBJECT_TAB_OUTER
                 (
                      id
                 ,     outerIndex
                 )
                 select     :new.id
                 ,     rownum
                 from     table(:new.tab) o;
            
                 insert
                 into     T_MY_OBJECT_TAB_INNER
                 (
                      id
                 ,     outerIndex
                 ,     innerIndex
                 ,     innerValue
                 )
                 select     :new.id
                 ,     o.outerIndex
                 ,     rownum
                 ,     value(i)
                 from     (
                      select     :new.id
                      ,     rownum outerIndex
                      ,     value(o) innerTab
                      from     table(:new.tab) o
                      ) o
                 ,     table(o.innerTab) i;
            end;
            /
            
            insert
            into     V_MY_OBJECT
            values     (
                 new TMyObject(
                      'A'
                 ,     TOuterNestedTable(
                           TInnerNestedTable('A','B','C')
                      ,     TInnerNestedTable('AA')
                      ,     TInnerNestedTable('AB')
                      )
                 )
            )
            /
            
            insert
            into     V_MY_OBJECT
            values     (
                 new TMyObject(
                      'B'
                 ,     TOuterNestedTable(
                           TInnerNestedTable('X','Y','Z')
                      ,     TInnerNestedTable('Hello', 'World!')
                      )
                 )
            )
            /
            Selecting from the view shows the results:
            select     value(o)
            from     V_MY_OBJECT o
            /
            
            VALUE(O)(ID, TAB)
            ---------------------------------------------------------------------------------------------------------------------
            TMYOBJECT('A', TOUTERNESTEDTABLE(TINNERNESTEDTABLE('A', 'B', 'C'), TINNERNESTEDTABLE('AA'), TINNERNESTEDTABLE('AB')))
            TMYOBJECT('B', TOUTERNESTEDTABLE(TINNERNESTEDTABLE('X', 'Y', 'Z'), TINNERNESTEDTABLE('Hello', 'World!')))
            
            2 rows selected.
            Hope that helps...

            Gerard