Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i


SQL> create table lsc_t as
2 select 1 parent, 2 child from dual
3 union all select 2,3 from dual
4 union all select 4,5 from dual
5 union all select 5,6 from dual
6 union all select 6,4 from dual;

Table created.

SQL> select parent,child
2 from lsc_t
3 where level=50
4 connect by parent=prior child
5 and level<=50 6 and prior dbms_random.value != dbms_random.value; PARENT CHILD ---------- ---------- 5 6 6 4 4 5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose :mrgreen:

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

10 Comments

  1. with SYS_GUID() you even have no PL/SQL context switch in your SQL – Karl

    SELECT PARENT,
    Child
    FROM Lsc_t
    WHERE LEVEL = 50
    CONNECT BY PARENT = PRIOR Child
    AND LEVEL <= 50 AND PRIOR sys_guid() != sys_guid();

  2. well, in my humble opinion it is not supposed to work… It probably does not in oracle7 or 8 (anyone can check?)

    it is very similar to the connect by without prior row generator
    select * from dual connect by level<50

    we just trick Oracle to generate a loop and we limit the loop to 50 levels, in case the level reach 50, we consider it is a loop.

    it is the way new connnect by algorythm (introduced as a hidden parameter in 8174) detects loop

  3. Thank you very much for this solution. Can you please let me know how to detect cycling records without using 50 level restrictions? I have a table where I can have cycling records of more than 50 level

Leave a comment

Your email address will not be published.