There is a request with connect by . While writing, I encountered an error:

ORA-01436: CONNECT BY loop in user data

By advice from the Internet, I added the line prior dbms_random.value is not null .

 select n, n + level n_sum, nvl(prior n, 0) + level - 1 as prior_sum from ( select 1 as n from dual ) connect by 1=1 and level < 10 and nvl(prior n, 0) + level - 1 in (1, 2, 3, 7) and prior dbms_random.value is not null;

I do not like magic in the code, which I do not understand :)

Explain, please, why without this line my request loops, and how does adding a random house fix the situation?

    1 answer 1

    The request is not looping. That Oracle thinks it is fixated. The definition of looping in Oracle is very simple: if the next line, the values ​​of all fields that are used with prior are equal to the value of these fields from the previous recursion level, then the query is looped. At each level, at least one field used prior should be different. This provides DBMS_RANDOM , which gives consecutive non-duplicate values.