ORA-01436 : CONNECT BY loop in user data

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