• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

question regarding CONNECT BY

 
Andres Gonzalez
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. I'm having problems using the CONNECT BY feature in oracle 9.

I need to check which code is causing a loop between parent and child. In 10G I could use CONNECT_BY_ISCYCLE, but it is not available in 9i; so what I did was the following (if there's a better solution please let me know).

parent_id | child_id
111 | 777
777 | 111

this should cause a loop and I should be able to tell the user that code 111 or 777 (either) is causing the problem.




If there's a loop (the exception code is -1436), then I get the code and return it to the client.

However, in some situations the exception thrown is the following:

java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows

the problem is in this sql:

SELECT parent_id
INTO class_parent_id2
FROM MY_TABLE
WHERE parent_id = c1.parent_id
CONNECT BY PRIOR parent_id = child_id
start with parent_id = c1.parent_id;

am I using connect by correctly? Is there any other better way to retrieve the offending code that is causing the circular loop? is the PRIOR keyword needed?

thanks
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic