By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

preventing CONNECT BY from generating an error if cycles exist

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.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,

pa**@paulrowe.com


Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.