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;

---------- ----------
         5          6
         6          4
         4          5

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

10 Replies to “Cycling”

  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 Reply