This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes question regarding CONNECT BY Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "question regarding CONNECT BY" Watch "question regarding CONNECT BY" New topic
Author

question regarding CONNECT BY

Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
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


I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury
 
Don't get me started about those stupid light bulbs.
 
subject: question regarding CONNECT BY