在Oracle11gR2中，如何利用嵌套表创建“带嵌套表类型”的表？

Question

在你提出任何问题之前，是的，我正在做一些家庭作业(类似于大学)。我正在处理Oracle 11gR2中的嵌套表，我的代码显然不起作用(但为什么不起作用呢？)我认为有些东西遗漏了：

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;

在这段代码中，我在Oracle中遇到了这个错误：[99999][2320] ORA-02320: failure in creating storage table for nested table column ADDENDUMS。但我认为我创建该表的方式与在tStaff表(CREATE OR REPLACE TYPE tStaff AS TABLE OF Staff;)中相同。

还有。我在这个问题(How do I create an Oracle table with nested tables of object types?)中看到，我可能必须在最后一个表中添加内部嵌套表：

创建AgreementInter的表AgreementsInter嵌套表地址存储为addendums_nt (嵌套表STORE成员存储为addendums_staff_members_nt)；

但是甲骨文又说出了同样的错误：[99999][2320] ORA-02320: failure in creating storage table for nested table column ADDENDUMS。

Answer 1

对于最后两个步骤，我建议使用tippo (staff_members而不是staffmembers)和名称更改(输入tAgreementInter而不是AgreementInter)：

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)
;

这里有一个db-fiddle来展示它的工作原理。