How to define a type in oracle11g that references a collection of this type?

Question

I want to do something like this

create type Item as object ( id number, subitems table of ref Item ) 

but oracle throws an exception when I try to do this. Is this possible, and if so, how?

Answer 1

Oracle will not compile your definition because the Item type has not yet been compiled. Why don't you try:

Compile this:

 CREATE OR REPLACE TYPE Item; CREATE OR REPLACE TYPE items_table IS TABLE OF REF item; 

and then try:

 CREATE OR REPLACE TYPE item AS OBJECT ( id number, subitems items_table ) 

Answer 2

It would be nice! You can try the following:

 create type item_ids_t is table of number; create type Item as object ( id number, subitems item_ids_t); 

This means that subelements are just a list of identifiers that will then be used to find the table indexed by the identifier.