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 
Frank Zhou gave me the DBMS_RANDOM idea a long time ago, now I find a case to use it 😀
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 😎
http://oraclesqlpuzzle.hp.infoseek.co.jp/8-43.html
Point is “and prior dbms_random.value != 1;” ,
Because dbms_random.value returns 0 or greater then 0 and lower then 1
🙂 good catch with != 1, it makes it 100% accurate instead of 99.999999999999999999999999999999999999%
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();
Yes, excellent
also possible with
and prior sys_guid() is not null
Why does this work?
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
Dores PRIOR remeber the earlier calculated value instead of doing it a second time?
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
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…