Is it possible to create a table of objects with nested tables as attributes?

Question

Here is a snippet of my OR schema:

CREATE TYPE artist_table_type AS TABLE OF REF artist_type; / CREATE TYPE track_type AS OBJECT ( title VARCHAR(1000), duration INT, release_date DATE, producers artist_table_type, MEMBER FUNCTION getProducers RETURN artist_table_type, MEMBER FUNCTION getRemixers RETURN artist_table_type ); / CREATE TABLE track_obj_table OF track_type; 

When I try to run this, I get an error:

 CREATE TABLE track_obj_table OF track_type * ERROR at line 1: ORA-22913: must specify table name for nested table column or attribute 

I suspect this is due to the type of table in the track_type object?

Answer 1

It just means that you have to provide a storage clause for the nested table:

 SQL> CREATE TABLE track_obj_table OF track_type; CREATE TABLE track_obj_table OF track_type * ERROR at line 1: ORA-22913: must specify table name for nested table column or attribute SQL> CREATE TABLE track_obj_table OF track_type 2 NESTED TABLE producers STORE AS producers_nt 3 / Table created. SQL> desc track_obj_table Name Null? Type ----------------------------------------- -------- ------------------------- TITLE VARCHAR2(1000) DURATION NUMBER(38) RELEASE_DATE DATE PRODUCERS ARTIST_TABLE_TYPE SQL>