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
|