Can I make a table of objects that have 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 attempt to run this, I get the 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 that this is because of the table type in the track_type object?

Answer 1

It just means you have to provide the 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>