Oracle : Nested table inside a type

Question

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 ?

Answer 1

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}