2 Replies Latest reply: May 17, 2011 5:13 PM by gaverill RSS

    Insert into Recursive Object Collection

    724405
      Hi,

      I have a similar problem with object collections as described in the thread "Thread: Recursive Object Collection".

      I intend to model a hierarchic tree as a collection of (recursive) objects. Data from a table should be inserted into this collection of objects. I have defined two types as follows:

      CREATE OR REPLACE TYPE ESI_NSA.GT_MODULE as object
      (
      ModuleID number,
      AnzeigeName varchar2(128),
      ModuleURL varchar2(128),
      Typ varchar2(128),
      ParentID number,
      SubModule GT_MODULE_L
      )
      NOT FINAL;

      CREATE OR REPLACE TYPE ESI_NSA.GT_MODULE_L as table of REF ESI_NSA.GT_MODULE;

      My code for inserting into the type is here:

      DECLARE
      i NUMBER := 1;
      lvModuleList GT_MODULE_L := GT_MODULE_L();

      CURSOR rec_cur IS
      SELECT mdl.module_id,
      mdl.name,
      mdl.module_url,
      mdl.typ,
      mdl.parent_module_id
      FROM esi_user.tb_module mdl
      BEGIN
      ...
      FOR lvRec IN rec_cur LOOP
      lvModuleList.Extend;
      lvModuleList(i) := GT_MODULE(lvRec.module_id,

      lvRec.name,
      lvRec.module_url,
      lvRec.typ,
      lvRec.parent_module_id,
      ???);
      i := i + 1;
      END LOOP;
      ...


      Does anybody know what I have to place at ??? for the code to compile without errors? I have tried to use additionally
      lvSubModuleList GT_MODULE_L := GT_MODULE_L();

      and use it instead of ??? but then I get the error# PLS-00382: Expression is of wrong type.

      Whats wrong with my code?

      Thanks for Your help.
        • 1. Re: Insert into Recursive Object Collection
          Zlatko Sirotic
          It seems that your table tb_module (CURSOR...SELECT...FROM tb_module) is relational table, not object table.
          But, in PL/SQL you can't have REFs to transient objects.
          You can have REFs to persistent objects (rows in object tables) only.

          Regards
          • 2. Re: Insert into Recursive Object Collection
            gaverill
            Your nested collection is a collection of object REFs, so you'll need to construct object references in one of two ways:

            1) Include an object table or view in your query, and use the REF(<alias>) function; or

            2) Use the MAKE_REF(<object-table>, <primary-key1>[, <primary-key2>, ...]) function.

            Also, since it appears you are using relational tables to store object data (a good choice), check out object views. For example, if your GT_MODULE was instead defined as:
              CREATE OR REPLACE TYPE ESI_NSA.GT_MODULE as object
              (
              ModuleID number,
              AnzeigeName varchar2(128),
              ModuleURL varchar2(128),
              Typ varchar2(128),
              Parent REF GT_MODULE,
              ...
              )
              NOT FINAL;
            you could create an object view:
              create or replace view V_GT_MODULE
              of GT_MODULE
              using object identifier (module_id)
              as
              SELECT mdl.module_id,
              mdl.name,
              mdl.module_url,
              mdl.typ,
              MAKE_REF(V_GT_MODULE, mdl.parent_module_id))
              FROM esi_user.tb_module mdl;
            which you would query to get instances of GT_MODULE:
              ...
              select  value(m)
              into     myModule
              from    V_GT_MODULE m
              where  m.ModuleID = :moduleID;
              ...
            The idea is the same for a collection of sub-module REFs.

            Hope this helps...

            Gerard