Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (ID, TERM, PARENT, GROUP_LEVEL) AS
SELECT  1, 'a', NULL, 1 FROM DUAL UNION ALL
SELECT  2, 'b',    1, 1 FROM DUAL UNION ALL
SELECT  3, 'c',    2, 1 FROM DUAL UNION ALL
SELECT  4, 'a', NULL, 1 FROM DUAL UNION ALL
SELECT  5, 'b',    4, 2 FROM DUAL UNION ALL
SELECT  6, 'c',    5, 2 FROM DUAL UNION ALL
SELECT  7, 'a', NULL, 1 FROM DUAL UNION ALL
SELECT  8, 'b',    7, 3 FROM DUAL UNION ALL
SELECT  9, 'c',    8, 3 FROM DUAL UNION ALL
SELECT 10, 'd',    8, 2 FROM DUAL UNION ALL
SELECT 11, 'e',   10, 3 FROM DUAL UNION ALL
SELECT 12, 'f',   11, 3 FROM DUAL UNION ALL
SELECT 13, 'a', NULL, 1 FROM DUAL UNION ALL
SELECT 14, 'b',   13, 2 FROM DUAL UNION ALL
SELECT 15, 'c',   13, 2 FROM DUAL UNION ALL
SELECT 16, 'a', NULL, 1 FROM DUAL UNION ALL
SELECT 17, 'b',   16, 3 FROM DUAL UNION ALL
SELECT 18, 'c',   16, 3 FROM DUAL;
18 rows affected

CREATE TYPE node IS OBJECT(
  group_level NUMBER(5,0),

  NOT FINAL NOT INSTANTIABLE MEMBER PROCEDURE add_child(
    self        IN OUT NOCOPY node,
    id          NUMBER,
    term        VARCHAR2,
    parent      NUMBER,
    group_level NUMBER
  ),

  NOT FINAL NOT INSTANTIABLE MEMBER FUNCTION to_char(self node) RETURN VARCHAR2,
  NOT FINAL NOT INSTANTIABLE MEMBER FUNCTION first_id(self node) RETURN NUMBER,
  NOT FINAL NOT INSTANTIABLE MEMBER FUNCTION last_id(self node) RETURN NUMBER
) NOT FINAL NOT INSTANTIABLE;
CREATE TYPE node_list IS TABLE OF NODE;
CREATE TYPE branch_node UNDER node (
  nodes node_list,
  CONSTRUCTOR FUNCTION branch_node(
    self        IN OUT NOCOPY branch_node,
    group_level NUMBER
  ) RETURN SELF AS RESULT,
  
  OVERRIDING MEMBER PROCEDURE add_child(
    self        IN OUT NOCOPY branch_node,
    id          NUMBER,
    term        VARCHAR2,
    parent      NUMBER,
    group_level NUMBER
  ),

  OVERRIDING MEMBER FUNCTION to_char(self branch_node) RETURN VARCHAR2,
  OVERRIDING MEMBER FUNCTION first_id(self branch_node) RETURN NUMBER,
  OVERRIDING MEMBER FUNCTION last_id(self branch_node) RETURN NUMBER
);
CREATE TYPE leaf_node UNDER node (
  id     NUMBER(5,0),
  term   VARCHAR2(5),
  parent NUMBER(5,0),

  OVERRIDING MEMBER PROCEDURE add_child(
    self        IN OUT NOCOPY leaf_node,
    id          NUMBER,
    term        VARCHAR2,
    parent      NUMBER,
    group_level NUMBER
  ),

  OVERRIDING MEMBER FUNCTION to_char(self leaf_node) RETURN VARCHAR2,
  OVERRIDING MEMBER FUNCTION first_id(self leaf_node) RETURN NUMBER,
  OVERRIDING MEMBER FUNCTION last_id(self leaf_node) RETURN NUMBER
);
CREATE TYPE BODY branch_node IS
  CONSTRUCTOR FUNCTION branch_node(
    self IN OUT NOCOPY branch_node,
    group_level NUMBER
  ) RETURN SELF AS RESULT
  IS
  BEGIN
    self.group_level := group_level;
    self.nodes := node_list();
    RETURN;
  END branch_node;

  OVERRIDING MEMBER FUNCTION to_char(self branch_node) RETURN VARCHAR2
  IS
    terms VARCHAR2(4000);
  BEGIN
    IF self.nodes IS EMPTY THEN
      RETURN '()';
    END IF;
    terms := '(' || self.nodes(1).to_char();
    FOR i IN 2 .. self.nodes.COUNT LOOP
      terms := terms || '->' || self.nodes(i).to_char();
    END LOOP;
    terms := terms || ')';
    RETURN terms;
  END to_char;

  OVERRIDING MEMBER FUNCTION first_id(self branch_node) RETURN NUMBER
  IS
  BEGIN
    IF self.nodes IS EMPTY THEN
      RETURN NULL;
    ELSE
      RETURN self.nodes(1).first_id();
    END IF;
  END first_id;

  OVERRIDING MEMBER FUNCTION last_id(self branch_node) RETURN NUMBER
  IS
  BEGIN
    IF self.nodes IS EMPTY THEN
      RETURN NULL;
    ELSE
      RETURN self.nodes(self.nodes.COUNT).last_id();
    END IF;
  END last_id;
  
  OVERRIDING MEMBER PROCEDURE add_child(
    self        IN OUT NOCOPY branch_node,
    id          NUMBER,
    term        VARCHAR2,
    parent      NUMBER,
    group_level NUMBER
  )
  IS
    debug CONSTANT BOOLEAN := FALSE;
  BEGIN
    IF     self.nodes IS NOT EMPTY
       AND group_level > self.group_level
       AND self.nodes(self.nodes.COUNT) IS OF (branch_node)
       AND parent BETWEEN self.nodes(self.nodes.COUNT).first_id()
                      AND self.nodes(self.nodes.COUNT).last_id()
    THEN
      IF debug THEN
      DBMS_OUTPUT.PUT_LINE(
        'Add to last branch: ' || self.group_level || ' -> ' || self.nodes.COUNT
        || ' (' || id || ', ' || term || ', ' || parent || ', ' || group_level || ')'
      );
      END IF;
      self.nodes(self.nodes.COUNT).add_child(id, term, parent, group_level);
      RETURN;
    END IF;
    self.nodes.EXTEND;
    IF group_level = self.group_level THEN
      IF debug THEN
      DBMS_OUTPUT.PUT_LINE(
        'Add new leaf: ' || self.group_level || ' -> ' || self.nodes.COUNT
        || ' (' || id || ', ' || term || ', ' || parent || ', ' || group_level || ')'
      );
      END IF;
      self.nodes(self.nodes.COUNT) := leaf_node(group_level, id, term, parent);
    ELSE
      IF debug THEN
      DBMS_OUTPUT.PUT_LINE(
        'Add new branch: ' || self.group_level || ' -> ' || self.nodes.COUNT
        || ' (' || id || ', ' || term || ', ' || parent || ', ' || group_level || ')'
      );
      END IF;
      self.nodes(self.nodes.COUNT) := branch_node(self.group_level + 1);
      self.nodes(self.nodes.COUNT).add_child(id, term, parent, group_level);
    END IF;
  END add_child;
END;
/
CREATE TYPE BODY leaf_node IS
  OVERRIDING MEMBER PROCEDURE add_child(
    self        IN OUT NOCOPY leaf_node,
    id          NUMBER,
    term        VARCHAR2,
    parent      NUMBER,
    group_level NUMBER
  )
  IS
  BEGIN
    RAISE_APPLICATION_ERROR(-20000, 'Not valid for a leaf node.');
  END add_child;

  OVERRIDING MEMBER FUNCTION to_char(
    self IN leaf_node
  ) RETURN VARCHAR2
  IS
    terms VARCHAR2(4000);
  BEGIN
    RETURN self.term;
  END to_char;

  OVERRIDING MEMBER FUNCTION first_id(
    self IN leaf_node
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN self.id;
  END first_id;

  OVERRIDING MEMBER FUNCTION last_id(
    self IN leaf_node
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN self.id;
  END last_id;
END;
/
CREATE TYPE term_details IS OBJECT (
  id   NUMBER(5,0),
  term VARCHAR2(4000)
);
CREATE TYPE term_details_list IS TABLE OF term_details;
CREATE FUNCTION get_hierarchy
  RETURN term_details_list PIPELINED
IS
  v_node branch_node;
  debug CONSTANT BOOLEAN := FALSE;
BEGIN
  FOR r IN (
    SELECT *
    FROM   table_name t
    START WITH parent IS NULL
    CONNECT BY PRIOR id = parent
    ORDER SIBLINGS BY id
  )
  LOOP
    IF r.parent IS NULL THEN
      IF v_node IS NOT NULL THEN
        PIPE ROW (term_details(v_node.first_id, v_node.to_char()));
      END IF;
      IF debug THEN
        DBMS_OUTPUT.PUT_LINE('------');
      END IF;
      v_node := branch_node(1);
    END IF;
    v_node.add_child(r.id, r.term, r.parent, r.group_level);
  END LOOP;

  IF v_node IS NOT NULL THEN
    PIPE ROW (term_details(v_node.first_id, v_node.to_char()));
  END IF;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    NULL;
END;
/
SELECT * FROM USER_ERRORS;
SELECT *
FROM   TABLE(get_hierarchy());


ID
TERM


1
(a->b->c)

4
(a->(b->c))

7
(a->((b->c)->d->(e->f)))

13
(a->(b)->(c))

16
(a->((b))->((c)))


DECLARE
  v_details term_details_list;
BEGIN
  SELECT VALUE(h)
  BULK COLLECT INTO v_details
  FROM   TABLE(get_hierarchy()) h;
  
  FOR i IN 1 .. v_details.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( v_details(i).id || ', ' || v_details(i).term );
  END LOOP;
END;
/
1 rows affected

dbms_output:
1, (a->b->c)
4, (a->(b->c))
7, (a->((b->c)->d->(e->f)))
13, (a->(b)->(c))
16, (a->((b))->((c)))
ID	TERM
1	(a->b->c)
4	(a->(b->c))
7	(a->((b->c)->d->(e->f)))
13	(a->(b)->(c))
16	(a->((b))->((c)))