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:

Categorized as Blogroll, sql

By Laurent Schneider

Oracle Certified Master


  1. Wow this is a great solution.
    I have seen this DBMS_RANDOM usage in US-OTN-SQL-forum.
    However I did not realize that “Connect by nocycle” can be emulated.

    I have researched and tested that on Oracle9i.
    Then I derived version like below 😎
    Point is “and prior dbms_random.value != 1;” ,
    Because dbms_random.value returns 0 or greater then 0 and lower then 1

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

    FROM Lsc_t
    WHERE LEVEL = 50
    AND LEVEL <= 50 AND PRIOR sys_guid() != sys_guid();

  3. 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

  4. 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

  5. In Oracle 10g :

    select parent,child
    from lsc_t
    where connect_by_iscycle=1
    connect by nocycle parent=prior child

    In 9i, increase the 50 to whatever you feel relevant…

Leave a comment

Your email address will not be published.