Detect hierarchy loops with Oracle 10g's new hierarchy pseudocolumns

by Bob Watkins | Jan 23, 2007 8:00:00 AM

Tags: Oracle Application Server 10g, NOCYCLE parameter, Bob Watkins, Oracle Corp., loop, Oracle Tips Newsletter

Takeaway: Hierarchical queries sometimes fail because the data contain rows that reference each other: a loop. Oracle 10g has syntax and two new pseudocolumns to aid in finding such loops.

In a previous tip, I described how to sort the entries in a hierarchical query. For a hierarchical query to work, however, the data must not loop back upon itself. That is, a parent row may not list itself or one of its child rows as its own parent. If this were the case, Oracle could follow the links around and around, never coming to a definitive end.

Fortunately, Oracle tests for this and will abort the query with this error:

ORA-01436: CONNECT BY loop in user data

In the past, it could be difficult finding all the rows that are referencing each other in a large data set because the query is terminated after the first loop is found. In Oracle 10g, two new syntax elements in the SELECT statement give you tools for troubleshooting such data errors: NOCYCLE and CONNECT_BY_ISCYCLE.

The NOCYCLE parameter is added to the CONNECT BY clause to indicate that Oracle should return a row anyway, even if a loop is detected in it. This provides valuable clues as to which rows are in error (don't worry—Oracle will stop evaluating the loop and move on to the next row). The CONNECT_BY_ISCYCLE pseudocolumn returns a one (1) if the current row contains a loop or returns a zero (0) if not. Used together, these two elements can pinpoint what needs to be fixed.

Listing A is the same hierarchical query from the previous article, which was executed against the HR sample schema.

This time let's introduce a loop in the data by changing employee Kochhar (101) to report to Urman (112) who reports to Greenberg (108) who reports to Kochhar, creating a loop.

UPDATE employees

SET manager_id = 112

WHERE employee_id = 101;

COMMIT;

If we rerun the query, we get the ORA-01436 error. Listing B detects the error we've introduced. Instead of CONNECT BY PRIOR, we use CONNECT BY NOCYCLE PRIOR. This will continue the query even if there is a loop detected. We also added the manager_id column and CONNECT_BY_ISCYCLE pseudocolumn to the report to aid in troubleshooting.

The output shows a one (1) in the row for Urman. By tracing back up the chain, we can see where the error occurred: in Kochhar's row.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.