Oracle: Connect By Loop in user data

Add the expression nocycleconnect by nocycle

To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query.

If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.

if there are no rows which satisfy START WITH and have Job = 'PRESIDENT' the loop will never occur (Oracle retrieves only START WITH rows)

if there is a row in the table which satisfies START WITH and has Job = 'PRESIDENT' the loop occurs in any case because:
1. Oracle finds all the rows which satisfy the START WITH (root rows).
2. For each row from p.1 Oracle scans the whole table for descendants. All rows (including the row from p. 1) satisfy the condition in CONNECT BY (because prior Job = 'PRESIDENT' is always true)
3. Obvious ...

SELECT Empno, Ename, JobFROM EmpSTART WITH Empno = 7839CONNECT BY nocycle PRIORJob='PRESIDENT'