Oracle SQL Left join same table unknown amount of times with cycles

Question

This is follow up question to my previous question: Oracle SQL Left join same table unknown amount of times

Let's say I have this dataset

| old  | new   | 
|------|-------| 
| a    | b     | 
| b    | c     | 
| d    | e     | 
| e    | a     | <- Here is a cycle 
| ...  | ...   | 
| aa   | bb    | 
| bb   | ff    | 
| ...  | ...   | 
| 11   | 33    | 
| 33   | 523   | 
| 523  | 4444  | 
| 4444 | 33    | <- another cycle 

because of the cycles oracle returns this error: "ORA-32044: cycle detected while executing recursive WITH query"

I want to break the recursive cycle and detect the rows that are causing the cycle

In the following it's possible to break the cycle with "<"

with numbers(val) as (
select 1 as val from dual
union all
select val + 1 from numbers
where val < 5 
)
select val from numbers

I tried the following in: http://rextester.com/ITB3407

Same code here:

with cte (old, new, lev) as
(
  select old, new, 1 as lev from mytable
  union all
  select m.old, cte.new, cte.lev + 1
  from mytable m
  join cte on cte.old = m.new
  where cte.lev < 6
)
select old, max(new) keep (dense_rank last order by lev) as new
from cte
group by old
order by old;

Answer 1

Add the CYCLE clause. By specifying this clause, the cycle is detected and the recursion stops

WITH cte (OLD, NEW, lev)
     AS (SELECT OLD, NEW, 1 AS lev FROM mytable
         UNION ALL
         SELECT m.old, cte.new, cte.lev + 1
           FROM mytable m JOIN cte ON cte.old = m.new
                                        )
  CYCLE OLD SET cycle TO 1 DEFAULT 0
  SELECT OLD, MAX (NEW) KEEP (DENSE_RANK LAST ORDER BY lev)
    FROM cte
GROUP BY OLD
ORDER BY OLD;

REXTESTER