CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-types
HelloJack
HelloJack

Reputation: 19

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

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

Upvotes: 0

Views: 785

Answers (1)

HelloJack
HelloJack

Reputation: 19

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

Upvotes: 1

Related Questions