1

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.

  • I'm not sure if this is doable. It's time for me to sleep now, but i'll revisit in the morning. – Philᵀᴹ May 7 '14 at 0:52
  • I've found an example where they have created a nested table within a VARRAY, maybe I could have a VARRAY of size (1), which would be close to what I want, I just want to follow the best practice though. – Max Carroll May 7 '14 at 18:32
  • From looking at the oracle docs which specifies the combination of possible collection types, the one I proposed doesn't exist. I think I will go with a varray(1) I cant think of less painfull alternative. – Max Carroll May 7 '14 at 18:41
1

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
| improve this answer | |
  • I have implemented this, also managed to insert a row, it was monstrous. Ouch my head. – Max Carroll May 7 '14 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.