Type with nested table in oracle

aggregateoracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a table called corrections. All my basic objects are compiling fine, just trying to build the assignment_tbl is a problem as im not sure how to use the nested table statement.

CREATE TYPE correction_info_objtyp AS OBJECT
(
   correction_date   DATE,
   raised_by         VARCHAR(50), 
   problem           VARCHAR(255)
);

CREATE TYPE correction_info_ntabtyp AS TABLE OF correction_info_objtyp;

CREATE TYPE assignment_status_info_objtyp UNDER status_info_obj
(
   corrections     correction_info_ntabtyp
);

CREATE table assignement_tbl
(
  assignment_id       NUMBER(6),
  assignment_title    VARCHAR(40),
  elements            elements_v,
  assignment_status   assignment_status_info_objtyp
)NESTED TYPE assignment_status STORE AS assign;  

-- I know this nested type isn't going to work because it's not a table,
-- But inside the assignment_status_info_objtyp is correction_info_ntabtyp which is
-- a table and needs the nested type statement

I've tried to follow the example here with the planets and satelites, where they have a nested table in a nested table, but for my situation i have a nested table in a type because I only ever want one instance of assignment_status in the assignment.

Any help, hints or even a link to an example or a page in a book would be of fantastic use.

Exhausted first 20 results of Google already.

Best Answer

I managed to create an array of size one instead of using a object type. Through thorough reading I found no mention of whether a nested table within a type was possible, although varrays of nested tables are; Nested tables of varrays are also allowed.

CREATE TYPE correction_info_objtyp AS OBJECT
(
   correction_date   DATE,
   raised_by         VARCHAR(50), 
   problem           VARCHAR(255)
);

CREATE TYPE correction_info_ntabtyp AS TABLE OF correction_info_objtyp;

CREATE TYPE assignment_status_info_objtyp UNDER status_info_obj
(
   corrections     correction_info_ntabtyp
);

CREATE TYPE assignement_status_info_v AS VARRAY(1) OF assignment_status_info_objtyp

CREATE table assignement_tbl
(
    assignment_id       NUMBER(6),
    assignment_title    VARCHAR(40),
    elements            elements_v,
    assignment_status   assignement_status_info_v
)VARRAY assignment_status STORE AS LOB assignement_status_store; -- contains the nested table
Related Question