Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE TYPE typeAuthor AS OBJECT(
    aId INTEGER,
    aName VARCHAR(60),
    aSurname VARCHAR(200)
);
CREATE TABLE tableAuthors OF typeAuthor (aId PRIMARY KEY)
--NESTED TABLE aArticles STORE AS aArticles_nt;
CREATE OR REPLACE TYPE typeListAuthors AS TABLE OF REF typeAuthor;
CREATE OR REPLACE TYPE typeUniversity AS OBJECT(
    uniId INTEGER,
    uAlias VARCHAR(16),
    uName VARCHAR(20),
    uLocation VARCHAR(150),
    uAuthors typeListAuthors
);
CREATE TABLE tableUniversity OF typeUniversity (uniId PRIMARY KEY)
NESTED TABLE uAuthors STORE AS uAuthors_nt;
insert into tableAuthors (aID, aName, aSurname)
select 1, 'Alice', 'Jones' from dual
union all
select 2, 'Bob', 'Williams' from dual
2 rows affected

insert into tableUniversity (uniId, uAlias, uName, uLocation, uAuthors)
select 1, 'UCL', 'University College', 'London', cast(collect(ref(a)) as typeListAuthors)
from tableAuthors a;
1 rows affected

SELECT u.uAuthors from tableUniversity u WHERE u.uniId = 1;
SELECT u.uAuthors.aName from tableUniversity u WHERE u.uniId = 1;
ORA-00904: "U"."UAUTHORS"."ANAME": invalid identifier

SELECT u.uAuthors.column_value.aName from tableUniversity u WHERE u.uniId = 1;
ORA-00904: "U"."UAUTHORS"."COLUMN_VALUE"."ANAME": invalid identifier

SELECT u.uniId, deref(a.column_value).aname
from tableUniversity u
cross join table (u.uAuthors) a
WHERE u.uniId = 1;


UNIID
DEREF(A.COLUMN_VALUE).ANAME


1
Alice

1
Bob