Re: how to debug "ORA-01436: CONNECT BY loop in user data"

I found the error and all I can say is that it was very strange behavior bordering on a bug in Oracle.

Don

bbcrock_at_hotmail.com (D) wrote in message news:<22e171df.0411160718.5bd38b2e_at_posting.google.com>...
> I am doing a simple hierarchy tree with parent and child records.
> Someone entered "bad" data, but out of 400 records I can't find out
> where/how.
>
> my report query is:
> SELECT id, parentid
> FROM table
> START WITH id = 1
> CONNECT BY PRIOR id = parentid
>
> the error I get is:
> ORA-01436: CONNECT BY loop in user data
>
> I see about 300 records before that throws an error.
>
> I cleaned up a bunch of the data and mysteriously I see about a dozen
> more items in my result set, but it does NOT appear that any of those
> new dozen were actually affected by the data clean up.
>
> There is nothing as easy as running a report for all the records where
> the id and the parentid are equal. I have investigated the data by
> hand.
>
> Is there a report I can run to identify which records may be causing
> the problem?
>
> post here.
>
> thanks,
>
> Don
Received on Wed Nov 17 2004 - 12:43:16 CST