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