preventing CONNECT BY from generating an error if cycles exist

**Paul Scott** · Jun 27 '08, 05:01 PM

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 <:StartOfCycleI D

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@paulrowe.c om (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@paulrowe. com

preventing CONNECT BY from generating an error if cycles exist

preventing CONNECT BY from generating an error if cycles exist

Comment