ORA-01436:CONNECT BY loop in user data

So, what is the problem?

I got an error ORA 01436: CONNECT BY LOOP in user data.

Hmm..What did you do to get this error?

Nothing. I was just running a hierarchical query in Oracle and somehow ended up with this error. First, I don’t really know what this error means to me and secondly, I don’t know how it came in my query.

Do you have the Query and the scripts to create and insert data into the table?

Sorry, I don’t have that with me right now. 

Ok. What do you want me do to for you now?

Three things:

1. What does the error code mean?
2. How to simulate the error?
3. How to debug when we get this error?

Ok. By the way, what version of Oracle you are using?

Oracle Database 10g Express Edition Release 10.2.0.1.0 – Product

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

Right, here we go. ‘What does the error code mean?’

Oracle throws this error when you try to use a ‘CONNECT BY’ constructs in a query and it results in a loop. By ‘loop’, what is conveyed is, the query results at some point in time during execution starts to return back to the point from where it started ad infinitum. During such instances, Oracle detects the error and to avoid the looping, gives a meaningful message on the screen like the one shown below

Aaha...Now I know. But, Can you simulate this error with a simple example for me?

Yes. Here we go. Let us create a simple table with two fields:

CREATE TABLE CONNECT_BY_LOOP
(ID1 NUMBER,
ID2 NUMBER)

Now, I try to insert exactly two rows into this table. The ‘insert’ statement is provided below

INSERT INTO connect_by_loop VALUES ('1','2')INSERT INTO connect_by_loop VALUES ('2','1')

COMMIT the transaction.

Now, if you give, “select * from connect_by_loop” this is what you see

And here is the problematic SQL that will simulate this error

SELECT * FROM connect_by_loopSTART WITH id1='1'
CONNECT BY PRIOR id1=id2

What happens here is Oracle retrieves the first row and then uses‘1’ as ID2 and retrieves the second row. Now, it tries to use ‘2’ as ID2 because of the PRIOR condition and detects a loop there. It throws this error right on the screen that you saw earlier.

Can I force Oracle to return a row in such cases?

Yes, you can. Use the ‘NOCYCLE’ parameter in the SQL and you are done! This is how the SQL looks like

SELECT id1,id2 FROM connect_by_loopSTART WITH id1='1'
CONNECT BY NOCYCLE PRIOR id1=id2

That’s cool. But if I have a big table, how can I find out which row is creating the problem? It would have been so nice if Oracle has provided a shortcut route for that. 

Fortunately, for your database version yes. You don’t have to toil that hard to find the row that is causing the loop. Here is a neat SQL that will help in identifying it.

SELECT id1,id2,CONNECT_BY_ISCYCLE "loop" FROM connect_by_loopSTART WITH id1='1'
CONNECT BY NOCYCLE PRIOR id1=id2

Notice the keyword “CONNECT_BY_ISCYCLE”. The output looks likes this

In row # 2, you see a value of ‘1’ for loop. So, this is the row, which is causing the ‘cyclic’ effect. If this row is fixed, then the problem is resolved.

That is really great! This saves a lot of my time in identifying the problematic row. Oracle is great..

Yes, in fact it is. Now, go home and find the row and fix it. See your hierarchical queries in action. Have a good time!