ORA-01436: CONNECT BY loop in user data

Lets assume our data is:

select * from emp;

when we execute a hierarchical query, it throws an error as

select empno, ename, sys_connect_by_path(ename,' -> ') tree from emp connect by prior empno=mgrno;

To trace this error of Connet by loop using connect_by_iscycle, we may execute following query:

select emp.*, connect_by_iscycle from emp where connect_by_iscycle = 1 connect by nocycle prior empno=mgrno;

and now we can correct this cyclic loop as:

update emp set mgrno=2497 where empno=3061;

and execute our previous hierarchical query

select empno, ename, sys_connect_by_path(ename,' -> ') tree from emp connect by prior empno=mgrno;

Related Links

Connect By Prior (Hierarchical Query)
http://nimishgarg.blogspot.in/2010/02/oracle-connect-by-prior-hierarchical.html

Get All Month or Week Days Names
http://nimishgarg.blogspot.in/2010/01/oracle-sql-get-all-month-names-jan-to.html

Alpha Numeric Counter Or Sequence
http://nimishgarg.blogspot.in/2011/04/alpha-numeric-counter-or-sequence.html

Oracle: Fibonacci Series by SQL
http://nimishgarg.blogspot.in/2010/08/oracle-fibonacci-series-by-sql.html

Oracle SQL: Triangular Series (1 3 6 10 15 21)
http://nimishgarg.blogspot.in/2010/07/oracle-sql-triangular-series-1-3-6-10.html