Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (parent_id, id) AS
SELECT NULL, 'A' FROM DUAL UNION ALL
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'B', 'C' FROM DUAL UNION ALL
SELECT 'C', 'A' FROM DUAL UNION ALL
SELECT 'C', 'D' FROM DUAL UNION ALL
SELECT 'D', 'B' FROM DUAL UNION ALL
SELECT 'C', 'E' FROM DUAL UNION ALL
SELECT 'E', 'F' FROM DUAL;
8 rows affected

SELECT parent_id,
       id,
       SYS_CONNECT_BY_PATH(id, '-') AS path,
       CONNECT_BY_ISCYCLE
FROM   table_name
START WITH
       parent_id IS NULL
CONNECT BY NOCYCLE
       PRIOR id = parent_id
ORDER SIBLINGS BY id


PARENT_ID
ID
PATH
CONNECT_BY_ISCYCLE


null
A
-A
0

A
B
-A-B
0

B
C
-A-B-C
1

C
D
-A-B-C-D
1

C
E
-A-B-C-E
0

E
F
-A-B-C-E-F
0


SELECT p.parent_id,
       p.id AS current_id,
       n.id AS next_id,
       SYS_CONNECT_BY_PATH(p.id, '-') || '-' || n.id AS path,
       CONNECT_BY_ISCYCLE
FROM   table_name p
       INNER JOIN table_name n
       ON (p.id = n.parent_id)
START WITH
       p.parent_id IS NULL
CONNECT BY NOCYCLE
       PRIOR n.ROWID = p.ROWID
ORDER SIBLINGS BY p.id, n.id


PARENT_ID
CURRENT_ID
NEXT_ID
PATH
CONNECT_BY_ISCYCLE


null
A
B
-A-B
0

A
B
C
-A-B-C
0

B
C
A
-A-B-C-A
1

B
C
D
-A-B-C-D
0

C
D
B
-A-B-C-D-B
1

B
C
E
-A-B-C-E
0

C
E
F
-A-B-C-E-F
0

PARENT_ID	ID	PATH	CONNECT_BY_ISCYCLE
null	A	-A	0
A	B	-A-B	0
B	C	-A-B-C	1
C	D	-A-B-C-D	1
C	E	-A-B-C-E	0
E	F	-A-B-C-E-F	0