CONNECT BY loop in user data in Oracle

Introduction

In this tip,i will explain  how to use CONNECT BY loop in user data and fetch 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;

That's all.hopes help.