How to fill a table with data which contains a nested table of references in Oracle?

Question

I am trying to create a database of a conference. There are tables like Authors, Papers and a relation table. The Papers table contains the title of research and a nested table of reference to authors (because a study can be written by more than one person). The Authors table should be created of reference type? I can't figure out how to insert references in that nested table. Any ideas?

CREATE OR REPLACE TYPE AuthorType AS OBJECT (
name VARCHAR(30),
email VARCHAR(25),
institute VARCHAR(50)
)

CREATE OR REPLACE TYPE AuthorTable AS TABLE OF AuthorType;

CREATE TABLE Authors OF AuthorType;

CREATE OR REPLACE TYPE AuthorRefType AS TABLE OF REF AuthorType;

CREATE OR REPLACE TYPE PaperType AS OBJECT (
    title VARCHAR(150),
    language VARCHAR(2),
    section REF SectionType,
    authors AuthorRefType,
    MEMBER FUNCTION has_authors(authorName IN CHAR) RETURN INTEGER
)

CREATE TABLE PaperTable OF PaperType (
    FOREIGN KEY (section) REFERENCES SectionTable
)
NESTED TABLE authors STORE AS AuthorRefTable;

INSERT INTO PaperTable VALUES
    SELECT 'Services in the digital era: How the role of customers changes with the adoption of new technologies', 'EN', REF(section),
        AuthorRefType(
            (SELECT REF(a) FROM Authors a WHERE email = 'zs.kenesei@gmail.com'),
            (SELECT REF(a) FROM Authors a WHERE email = 's.seuring@gmail.com')),
FROM SectionTable section WHERE section = 'Marketing';

Answer 1

If you have the authors:

INSERT INTO Authors VALUES ( 'Alice',    'a@example.com', 'aaa' );
INSERT INTO Authors VALUES ( 'Beatrice', 'b@example.com', 'bbb' );
INSERT INTO Authors VALUES ( 'Carol',    'c@example.com', 'ccc' );

Then you can populate the PaperTable (why the table suffix?) like this:

INSERT INTO PaperTable (
  title,
  language,
  section,
  authors
) VALUES (
  'Paper 1',
  'XY',
  ( SELECT REF(s) FROM SectionTable s WHERE section = 'Fiction' ),
  AuthorRefType(
    ( SELECT REF(a) FROM Authors a WHERE name = 'Alice' ),
    ( SELECT REF(a) FROM Authors a WHERE name = 'Carol' )
  )
);

INSERT INTO PaperTable (
  title,
  language,
  section,
  authors
)
SELECT 'Paper 2',
       'YZ',
       REF(s),
       ( SELECT CAST(
                  COLLECT(
                    REF( a )
                  ) AS AuthorRefType
                )
         FROM   Authors a
         WHERE  name IN ( 'Beatrice', 'Carol' )
       )
FROM   SectionTable s
WHERE  section = 'Reference';

INSERT INTO PaperTable (
  title,
  language,
  section,
  authors
) VALUES (
  'Paper 3',
  'XZ',
  NULL,
  NULL
);

and then:

SELECT p.title,
       p.language,
       DEREF( p.section ).section AS section,
       DEREF( a.COLUMN_VALUE ).name AS name,
       DEREF( a.COLUMN_VALUE ).email AS email,
       DEREF( a.COLUMN_VALUE ).institute AS institute
FROM   PaperTable p
       LEFT OUTER JOIN TABLE( p.Authors ) a
       ON ( 1 = 1)

outputs:

TITLE   | LANGUAGE | SECTION   | NAME     | EMAIL         | INSTITUTE
:------ | :------- | :-------- | :------- | :------------ | :--------
Paper 1 | XY       | Fiction   | Alice    | a@example.com | aaa      
Paper 1 | XY       | Fiction   | Carol    | c@example.com | ccc      
Paper 2 | YZ       | Reference | Beatrice | b@example.com | bbb      
Paper 2 | YZ       | Reference | Carol    | c@example.com | ccc      
Paper 3 | XZ       | null      | null     | null          | null     

db<>fiddle here