Oracle: Connect By Loop in user data

Question

I understand when a loop can occur in Oracle. Theoritically it says if a record is both parent to another node and at the same time it is child to it, then it can enter into a loop.

But I can't catch why my this particular query is running into a loop.

SELECT Empno, Ename, Job
FROM Emp
START WITH Empno = 7839
CONNECT BY PRIOR
Job='PRESIDENT'

Can someone please explain me how this data can result into a loop. I made a CONNECT_BY_ISCYCLE check and found that the record looping is KING(President). But I still don't understand how can it be possible, as KING is President and I don't see any way in which it is becoming both child and parent to any record in the table.

Please explain me, why this is an error and where is the loop?

Thanks in advance.

FYI, The table is the default EMP table in SCOTT user in Oracle.

Answer 1

Add the expression nocycle connect by nocycle

Answer 2

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 ...