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!




9 comments:

  1. Wonderful explanation!
    Highly appreciated!

    ReplyDelete
  2. Great post. Thank you for the time you spend on explaining this topic.

    ReplyDelete
  3. Explained so well. Thanks a lottt !

    ReplyDelete
  4. Thanks for explaining it so clearly. It really helped.

    ReplyDelete
  5. Thank you very very much!!!!

    ReplyDelete
  6. Cant explain this in a simpler way! Good work!!

    ReplyDelete