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?
I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury