Tuesday, February 21, 2012

ORA-01436: CONNECT BY loop

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Let's consider this relation graph:

Jon -> Mike -> Paul -> Steve 
 ^                         |
 |                         |
 --------------Tom--------

We want to represent this into a table and retrieve the dependency chain:
SQL> create table hierarchy (col1 varchar2(15), col2 varchar2(15));
Table created.
SQL> insert into hierarchy values ('Jon','Mike');
1 row created.
SQL> insert into hierarchy values ('Mike','Paul');
1 row created.
SQL> insert into hierarchy values ('Paul','Steve');
1 row created.
SQL> insert into hierarchy values ('Steve','Tom');
1 row created.
SQL> insert into hierarchy values ('Tom','Jon');
1 row created.
SQL> commit;
Commit complete.
Now let's try to show this. But because 'Tom' loops back to 'Jon' an error will be raised:
SQL> select lpad(' ',level-1)|| col1 || '>>' || col2
  2  from hierarchy
  3  start with col1 = 'Jon'
  4  connect by prior col1=col2;
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected
The NOCYCLE keyword resolves this:
SQL> select lpad(' ',level-1)|| col1 || '>>' || col2
  2  from hierarchy
  3  start with col1 = 'Jon'
  4  connect by NOCYCLE prior col1=col2;
LPAD('',LEVEL-1)||COL1||'>>'||COL2
-------------------------------------------------------
-------------------------------------------------------
-------------------------------------------------------
Jon>>Mike
 Tom>>Jon
  Steve>>Tom
   Paul>>Steve
    Mike>>Paul

Now we are trying to find who introduces the loop effect.
It seems that 'Mike' -> 'Paul' is causing it, yet I have no clue why is this reported like this.
SQL> select col1,col2,connect_by_iscycle, level
  2  from hierarchy
  3  start with col1 = 'Jon'
  4  connect by NOCYCLE prior col1=col2;
COL1            COL2            CONNECT_BY_ISCYCLE      LEVEL
--------------- --------------- ------------------ ----------
Jon             Mike                             0          1
Tom             Jon                              0          2
Steve           Tom                              0          3
Paul            Steve                            0          4
Mike            Paul                             1          5

No comments:

Post a Comment