wood burning stoves 2.0*
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 Android Security Essentials Live Lessons this week in the Android 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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: question regarding CONNECT BY
 
Similar Threads
problem in session
select with an "in" clause...
Hibernate 3.0 : Where am I going wrong?
CallableStatement Error on Oracle Stored Procedure.
How to link a function with jsp code