Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   

connect_by_iscycle tips

Oracle Tips by Burleson


Oracle 10g connect_by_iscycle

The purpose of the connect_by_iscycle pseudo-column is to return 1 if the current row has a child which is also its ancestor. If there is no relation, it returns a 0 value. This information can then be used to further expand the hierarchy.

connect_by_iscycle can only be used if you have specified the nocycle parameter of the CONNECT BY clause. The NOCYCLE clause enables Oracle to return the results of a query that would fail because of a CONNECT BY loop.

connect_by_iscycle Example

In the standard Oracle example table, scott.emp, provided for this purpose in all Oracle databases, the employee King is the ultimate boss and has no manager (no entry for the mgr column). One of his employees is Clark, who is the manager of department 10. If we update the employees table to set Clark as King’s manager, we can create a loop in the data to show the use of the CONNECT_BY_ISCYCLE pseudo-column:

Without the update the SELECT:

SQL> SELECT ename "Employee",

   2 LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"

   3 FROM scott.emp

   4 WHERE level <= 3 AND deptno = 10

   5 START WITH ename = ’KING’

   6 CONNECT BY PRIOR empno = mgr AND LEVEL <= 4;

Generates:

Employee           LEVEL Path

-------------- --------- ------------------

KING                   1 /KING

CLARK                  2 /KING/CLARK

MILLER                 3 /KING/CLARK/MILLER

Now, let's give Clark a really big promotion (don't we all dream about becoming our boss’s boss?).

SQL> UPDATE scott.emp SET mgr = 7782

   2 WHERE empno = 7939;

 

1 row updated.

Now let's check out the hierarchy again:

SQL> SELECT ename "Employee",

   2 LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"

   3 FROM scott.emp

   4 WHERE level <= 3 AND deptno = 10

   5 START WITH ename = ’KING’

   6 CONNECT BY PRIOR empno = mgr AND LEVEL <= 4;

 

ERROR:

ORA-01436: CONNECT BY loop in user data

 

no rows selected

By adding the nocycle parameter in the CONNECT BY condition, we can cause Oracle to return the rows despite the loop. The connect_by_iscycle pseudo-column will show you which rows contain the cycle:

SQL> SELECT ename "Employee", CONNECT_BY_ISCYCLE "Cycle",

   2 LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"

   3 FROM scott.emp

   4 WHERE level <= 3 AND deptno = 10

   5 START WITH ename = ’KING’

   6 CONNECT BY NOCYCLE PRIOR empno = mgr AND LEVEL <= 4;

 

Employee       CYCLE      LEVEL Path

-------------- -----  --------- ------------------

KING               0          1 /KING

CLARK              1          2 /KING/CLARK

MILLER             0          3 /KING/CLARK/MILLER

 

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks