CODEWITHSUNDEEP
sqloracle-databaseplsqlnested-table
Orion Aurore
Orion Aurore

Reputation: 29

Oracle : Nested table inside a type

I need to create table with an O_BIEN attribute. O_BIEN type includes a nested table of T_HABITANT type.

Here is my code :

CREATE OR REPLACE TYPE O_HABITANT AS OBJECT (
    estmajeur CHAR(1),
    lien      VARCHAR(10)
);
\

CREATE OR REPLACE TYPE T_HABITANT AS TABLE OF O_HABITANT;
\

CREATE OR REPLACE TYPE O_BIEN AS OBJECT (
    surfacetotale NUMBER(3, 2),
    nombrepices   VARCHAR(2),
    habitant      T_HABITANT
);
\

CREATE TABLE FISC (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    bien O_BIEN
)NESTED TABLE bien STORE AS NST_BIEN;

I've the following error:

ORA-22912: specified column or attribute is not a nested table type" *Cause: The storage clause is specified for a column or attribute that is not a nested table column or attribute. *Action: Specify a valid nested table column or attribute.

So, how can I specify the habitant attribute of O_BIEN as a nested table ?

Upvotes: 0

Views: 781

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

This worked for me when I specified full column name with alias:

create table fisc (id number primary key, bien o_bien) 
  nested table bien.habitant store as bien_habitant_store;

dbfiddle with test

Upvotes: 0

Related Questions