This discussion is archived
2 Replies Latest reply: May 17, 2011 3:13 PM by gaverill RSS

Insert into Recursive Object Collection

724405 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points