This content has been marked as final. Show 2 replies
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.
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:
you could create an object view:
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;
which you would query to get instances of GT_MODULE:
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;
The idea is the same for a collection of sub-module REFs.
... select value(m) into myModule from V_GT_MODULE m where m.ModuleID = :moduleID; ...
Hope this helps...