Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table mytable (id number(5), name varchar2(10), parent_id number(5));
begin
  insert into mytable values (1, 'John', 4);
  insert into mytable values (2, 'Paul', 1);
  insert into mytable values (3, 'George', 2);
  insert into mytable values (4, 'Ringo', 3);

  insert into mytable values (5, 'Elvis', 5);

  insert into mytable values (6, 'Roy', 4);
  insert into mytable values (7, 'Carl', 3);

  insert into mytable values (10, 'Les', 100);
  insert into mytable values (100, 'Mary', 10);

  commit;
end;
/
1 rows affected

with cte (id, parent_id, ids, min_id) as
(
  select id, parent_id, to_char(id), id from mytable
  union all
  select
    t.id, t.parent_id, ids || ' -> ' || t.id, least(t.id, cte.min_id)
  from cte
  join mytable t on t.id = cte.parent_id
)
cycle id set cycle to 1 default 0
select min(ids) as cycling_ids
from cte
where cycle = 1
group by min_id
order by min_id;


CYCLING_IDS


1 -> 4 -> 3 -> 2 -> 1

5 -> 5

10 -> 100 -> 10