Unable to create a table with a column of object type (and the same object type contains an attribute of nested table type)

Question

I am trying to create a table with a column of object type and tricky nested table hierarchy. Getting ORA-22913 error. In CREATE table statement the column "theCol" is of type object (i.e. MainObj). MainObj contains an attribute of type nested table i.e. ChildTab. I think am supposed to use NESTED TABLE clause in CREATE TABLE statement. But not sure how to use it here because "theCol" is NOT of nested table type.

DROP TYPE MainObj;
DROP TYPE ChildTab;
DROP TYPE ChildObj;

CREATE TYPE ChildObj AS OBJECT (
naame varchar2(20)
, kaam varchar2(20)
);
/

CREATE TYPE ChildTab AS TABLE OF ChildObj;
/

Create TYPE MainObj as OBJECT (
    KEEY VARCHAR2(5),
    ChildList  ChildTab
);
/

CREATE TABLE TestTableDesi (
 theCol MainObj
);
/

Type dropped. Type dropped. Type dropped. Type created. Type created. Type created. ORA-22913: must specify table name for nested table column or attribute

Possible duplicate of How do I create an Oracle table with nested tables of object types? — Markus Deibel
This problem is slightly different as the attribute in CREATE TABLE statement is of type object.. not a type of nested table — Saurabh Sharma

Saurabh Sharma Saurabh Sharma · Accepted Answer · 2019-03-29T16:51:52

Just found the solution here. How do I create an Oracle table of objects containing nested tables? The create table statement should be as follows.

CREATE TABLE TestTableDesi (
theCol MainObj
) nested table theCol.ChildList store as ChildList_tab ;
/

Unable to create a table with a column of object type (and the same object type contains an attribute of nested table type)

1 Answers