ORA-01436 : CONNECT BY loop in user data

Cause: The condition specified in a CONNECT BY clause caused a loop in the query, where the next record to be selected is a descendent of itself. When this happens, there can be no end to the query.

Option#1:
CREATE TABLE TEST_LOOP
(ID1 NUMBER,
ID2 NUMBER);

INSERT INTO TEST_LOOP VALUES (1,2);
INSERT INTO TEST_LOOP VALUES (2,1);

COMMIT;

SELECT * FROM TEST_LOOP
START WITH id1=1
CONNECT BY PRIOR id1=id2;

ORA-01436: CONNECT BY loop in user data

SELECT id1,id2,CONNECT_BY_ISCYCLE loop
FROM TEST_LOOP
START WITH id1=1
CONNECT BY NOCYCLE PRIOR id1=id2;

       ID1        ID2       LOOP
---------- ---------- ----------
         1          2          0
         2          1          1

So row number 2 is creating the loop.



Option #2:

You can debug in the following way:
ALTER SESSION SET "_dump_connect_by_loop_data"=TRUE;

SELECT * FROM TEST_LOOP
START WITH id1=1
CONNECT BY PRIOR id1=id2;


Check trace file from UDUMP destination for problematic IDs that causing the loop.
CONNECT BY loop in user data :  (1) --> (2) --> (1)


Action:
Solution#1: Check the CONNECT BY clause and remove the circular reference.
Solution#2: If you want to ignore it simply use "nocycle" keyword.

Example:

SELECT id1,id2
FROM TEST_LOOP
START WITH id1=1
CONNECT BY NOCYCLE PRIOR id1=id2;

       ID1        ID2
---------- ----------
         1          2
         2          1