Re: preventing CONNECT BY from generating an error if cycles exist

The problem is CONNECT BY is designed for tree walking tree data structures, whereas the scenerio you describe is not a tree as it has no finite path when traversed. Without Oracle's treewalk cycle checks it wouldn't know when to stop, it could get caught in an infinite loop.

I haven't tried this, but if you knew the id of the item which causes the cycle on a given treewalk, you could do something like this :

select id, lpad(' ', 6*(level-1))||name
from mytable
start with parent_id is null
connect by parent_id = prior id and
id <> :StartOfCycleID

but you could have many IDs which cycle for a given treewalk especially if you walk from root to leaf, and remember you can't use sub queries or joins in treewalks to join to many StartOfCycleIDs.

Perhaps a StartOfCycleID could be determined from a PL/SQL function which keeps track of the last id when doing a for rec loop, until the cycle exception occurs?

It's a tough one!

Paul Scott

On 1 Aug 2003 08:44:11 -0700, paul_at_paulrowe.com (Paul Rowe) wrote:

>Hello;
>
> I am wondering if anyone knows of a way to issue the CONNECT BY
>clause on data that contains cycles? I have a business requirement
>that specifies that cycles could be present in the data and cannot be
>prevented. I want to use the CONNECT BY clause to prevent sending
>massive IN clauses, but I cannot because CONNECT BY will generate an
>error. Is there a similar clause that I can use besides CONNECT BY?
>
>Any help would be appreciated.
>
>Sincerely,
>
>paul_at_paulrowe.com
Received on Fri Aug 01 2003 - 18:42:16 CEST