Laurent Schneider

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

Put your code in <code> and </code> tags

8 Responses to “Cycling”

1. Laurent Schneider Says:
   September 24th, 2008 at 12:37

   Frank Zhou gave me the DBMS_RANDOM idea a long time ago, now I find a case to use it

2. Aketi Jyuuzou Says:
   September 25th, 2008 at 13:05

   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

3. Laurent Schneider Says:
   September 25th, 2008 at 14:35

   :) good catch with != 1, it makes it 100% accurate instead of 99.999999999999999999999999999999999999%

4. Karl Reitschuster Says:
   September 25th, 2008 at 15:29

   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();
   
   

5. Laurent Schneider Says:
   September 25th, 2008 at 16:24

   Yes, excellent

   also possible with

   and prior sys_guid() is not null

6. Brian Tkatch Says:
   September 25th, 2008 at 21:28

   Why does this work?

7. Laurent Schneider Says:
   September 26th, 2008 at 06:47

   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

8. Brian Tkatch Says:
   September 26th, 2008 at 16:37

   Dores PRIOR remeber the earlier calculated value instead of doing it a second time?

Leave a Reply