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:

SELECT empno,ename "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM scott.emp
START WITH ename = 'KING'
CONNECT BY NOCYCLE PRIOR empno = mgr
;