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,
pa**@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,
pa**@paulrowe. com
