Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE person IS OBJECT(
  id NUMBER,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  mother REF PERSON,
  MEMBER FUNCTION full_name( self IN PERSON )
    RETURN VARCHAR2,
  MEMBER FUNCTION children (self IN PERSON )
    RETURN SYS_REFCURSOR
);
CREATE TABLE people OF person (
  ID PRIMARY KEY
);
CREATE TYPE BODY person IS
  MEMBER FUNCTION full_name( self IN PERSON )
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN self.first_name || ' ' || self.last_name;
  END;

  MEMBER FUNCTION children( self IN PERSON )
    RETURN SYS_REFCURSOR
  IS
    p_cursor SYS_REFCURSOR;
  BEGIN
    OPEN p_cursor FOR
    SELECT VALUE(p) AS child
    FROM   people p
    WHERE  p.mother.id = self.id;
    
    RETURN p_cursor;
  END;
END;
/
CREATE SEQUENCE people__id__seq;
CREATE TRIGGER people__new__trg
BEFORE INSERT ON people
FOR EACH ROW
  WHEN (new.ID IS NULL)
BEGIN
  :new.ID := PEOPLE__ID__SEQ.NEXTVAL;
END;
/
INSERT INTO people ( first_name, last_name )
SELECT 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 'Belle', 'Burns' FROM DUAL UNION ALL
SELECT 'Carol', 'Charles' FROM DUAL;
3 rows affected

UPDATE people
SET mother = ( SELECT REF(p) FROM people p WHERE id=2 )
WHERE id = 3;
1 rows affected

SELECT id,
       first_name,
       last_name, 
       p.mother.id
FROM   people p;


ID
FIRST_NAME
LAST_NAME
MOTHER.ID


1
Alice
Abbot
null

2
Belle
Burns
null

3
Carol
Charles
2


DECLARE
  p_person PERSON;
  p_child  PERSON;
  p_cursor SYS_REFCURSOR;
BEGIN
  SELECT VALUE( p )
  INTO   p_person
  FROM   people p
  WHERE  id = 2;
  
  p_cursor := p_person.children();
  DBMS_OUTPUT.PUT_LINE( 'Children of ' || p_person.full_name );
  
  LOOP
    FETCH p_cursor INTO p_child;
    EXIT WHEN p_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( p_child.full_name );
  END LOOP;
  
  CLOSE p_cursor;
END;
/
1 rows affected

dbms_output:
Children of Belle Burns
Carol Charles