Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Type person;
Create Type child AS Table Of Ref person;
Create Type person AS Object(
  id       NUMBER(12,0),
  name     varchar(10),
  father   ref person,
  children child
);
CREATE TABLE Employee OF Person NESTED TABLE children STORE AS employee_children;
DROP TABLE Employee;
CREATE TABLE Employee OF Person(
  father SCOPE IS Employee,
  CONSTRAINT Employee__ID__PK PRIMARY KEY (ID)
) NESTED TABLE children STORE AS employee_children;
ALTER TABLE employee_children ADD SCOPE FOR ( COLUMN_VALUE ) IS Employee;
INSERT INTO Employee ( id, name, father, children ) VALUES ( 1, 'Adam', NULL, child() );
1 rows affected

INSERT INTO Employee (
  id,
  name,
  father,
  children
) VALUES (
  2,
  'Bob',
  ( SELECT REF(e) FROM Employee e WHERE id = 1 ),
  child()
);
1 rows affected

INSERT INTO TABLE( SELECT children FROM Employee WHERE id = 1 )
VALUES ( ( SELECT REF(e) FROM Employee e WHERE id = 2 ) );
1 rows affected

INSERT INTO Employee (
  id,
  name,
  father,
  children
) VALUES (
  3,
  'Charles',
  ( SELECT REF(e) FROM Employee e WHERE id = 1 ),
  child()
);
1 rows affected

INSERT INTO TABLE( SELECT children FROM Employee WHERE id = 1 )
VALUES ( ( SELECT REF(e) FROM Employee e WHERE id = 3 ) );
1 rows affected

SELECT e.id,
       e.name,
       c.COLUMN_VALUE.id AS child_id,
       c.COLUMN_VALUE.name AS child_name
FROM   employee e
       OUTER APPLY TABLE( e.children ) c


ID
NAME
CHILD_ID
CHILD_NAME


1
Adam
2
Bob

1
Adam
3
Charles

2
Bob
null
null

3
Charles
null
null