In Oracle 11gR2, how to CREATE TABLE of "Type with NESTED TABLE " with NESTED TABLE?

Question

Before any of your questions, yes, I'm doing some homework (university kind). I'm dealing with Nested Tables in Oracle 11gR2, and I have this code, which is not working, obviously (but why not?). I think something's missing:

CREATE OR REPLACE TYPE subjects AS VARRAY(20) OF VARCHAR2(100);

CREATE OR REPLACE TYPE Person AS OBJECT(
    name            VARCHAR2(30),
    last_name       VARCHAR2(128),
    id_card         VARCHAR2(9)
) NOT FINAL;

CREATE OR REPLACE TYPE Student UNDER Person (
    id_college              VARCHAR2(10),
    enrolled_subjects       subjects
);

CREATE TABLE Students OF Student;

CREATE OR REPLACE TYPE Student_List AS VARRAY(5) OF REF Student;

CREATE OR REPLACE TYPE PDI UNDER Person (
    id_university       VARCHAR2(10),
    depto               VARCHAR2(50),
    date_incorporation  TIMESTAMP
);

CREATE TABLE PDIs OF PDI;

CREATE OR REPLACE TYPE Staff UNDER Person () FINAL;

CREATE TABLE Staff_Members OF Staff;

CREATE OR REPLACE TYPE tStaff AS TABLE OF Staff;

CREATE OR REPLACE TYPE Addendum AS OBJECT (
    idCode          VARCHAR2(10),
    sign_date       TIMESTAMP,
    students        student_list,
    staff_members   tStaff,
    professor       REF PDI
) FINAL;

CREATE TABLE Addendums OF Addendum
    NESTED TABLE staff_members STORE AS staffmembers_nt;

CREATE OR REPLACE TYPE tAddendum AS TABLE OF Addendum;

-- UNTIL HERE EVERYTHING'S OK

CREATE OR REPLACE TYPE AgreementInter AS OBJECT (
    idCode              VARCHAR2(10),
    manager_name        VARCHAR2(30),
    manager_last_name   VARCHAR2(128),
    addendums           tAddendum
) FINAL;

CREATE TABLE AgreementsInter OF AgreementInter
    NESTED TABLE addendums STORE AS addendums_nt;

With this code I have this error in Oracle: [99999][2320] ORA-02320: failure in creating storage table for nested table column ADDENDUMS. But I think I created the table the same way as in the tStaff table (CREATE OR REPLACE TYPE tStaff AS TABLE OF Staff;).

Also. I saw in this question (How do I create an Oracle table with nested tables of object types?) that may I have to add inner Nested Table in my last table:

CREATE TABLE AgreementsInter OF AgreementInter NESTED TABLE addendums STORE AS addendums_nt (NESTED TABLE staffmembers STORE AS addendums_staff_members_nt);

But then Oracle says the same error: [99999][2320] ORA-02320: failure in creating storage table for nested table column ADDENDUMS.

Answer 1

There's a tippo (staff_members instead of staffmembers) and a name change (type tAgreementInter instead of AgreementInter) I suggest for the last two steps:

CREATE OR REPLACE TYPE tAgreementInter AS OBJECT (
    idCode              VARCHAR2(10),
    manager_name        VARCHAR2(30),
    manager_last_name   VARCHAR2(128),
    addendums           tAddendum
) FINAL;

CREATE TABLE AgreementsInter OF tAgreementInter 
NESTED TABLE addendums STORE AS addendums_nt 
(NESTED TABLE staff_members STORE AS addendums_staff_members_nt)
;

Here's a db-fiddle to show it works.