2

Possible Duplicate:
How can I define a type in oracle11g that references a collection of that type?

I have a scenario in which I have to create a user defined type A which has a collection of type A. I tried doing the following but did not help:

create or replace type sku_t;

create or replace type skulink_t as table of sku_t;

create or replace type sku_t as object(skuId varchar(12), display_name varchar(100), bundlnks ref skulink_t );

This leaves objects sku_t and skulink_t in an incomplete state and the compiler complains to complete them. I am not sure how to go about this. Any help will be much appreciated.

Community
  • 1
  • 1
user1711845
  • 143
  • 3
  • Why do you believe you need to create a type that contains a collection of that type? That doesn't appear to be a sensible requirement and I'm hard-pressed to imagine any language where such a thing would be possible. If you can eliminate that requirement, then you can sensibly define an object A and define another object B that has a collection of A's which would seem to be the appropriate way to approach this sort of problem. Can you explain why you can't have multiple objects? – Justin Cave Oct 23 '12 at 18:12
  • Basically I want to create a tree structure from the tables I have. As per the requirements this is the hierarchy defined and described as: catalog-->category-->category-->category-->product-->sku-->bundl – user1711845 Oct 23 '12 at 18:21
  • sorry about the above incomplete reply following is what I wanted to write. – user1711845 Oct 23 '12 at 18:31
  • Basically I am trying to create a tree structure from the tables I have and convert it into XML format simultaneously using the DBMS_XMLGEN package or any other tool. As per the requirements this is how the hierarchy is defined: catalog-->category(s)-->category(s)-->category(s)-->product(s)-->sku(s)-->sku(s) . A catalog can have one or more categories which can further have one or more sub categories etc., similarly the tree branches down to products and skus. This requirement cant be changed. – user1711845 Oct 23 '12 at 18:31
  • 2
    If you already have the data in tables and the goal is to generate XML, why are you trying to create an object type hierarchy in the first place? Why not generate the XML from the tables directly? – Justin Cave Oct 23 '12 at 18:39

1 Answers1

1

You need to use a nested table of REFs, instead of a REF of nested tables.

create or replace type sku_t;
create or replace type skulink_t as table of ref sku_t;
create or replace type sku_t as object(skuId varchar(12), display_name varchar(100), bundlnks skulink_t );

Example of how to use this:

create table sku_table of sku_t nested table bundlnks store as outer_nt;

insert into sku_table values(sku_t('sku1', 'sku1', null));
insert into sku_table values(sku_t('sku2', 'sku2', null));
insert into sku_table
values(sku_t('sku3', 'sku3',
    skulink_t
    (
        (select ref(s) from sku_table s where s.skuId = 'sku1'),
        (select ref(s) from sku_table s where s.skuId = 'sku2')
    )));

commit;

select deref(b.column_value).skuid skuid
from sku_table, table(bundlnks) b where skuid = 'sku3';

skuid
-----
sku1
sku2

But a regular hierarchical table would probably work much better 99.99% of the time.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132