Solved

Troubleshooting ORA-01436: CONNECT BY loop in user data

Posted on 2008-12-10
5
10,394 Views
Last Modified: 2013-12-19
I've written a hierarchical query in 9i (the purpose of which is to identify every employee and their management hierarchy) and am getting the "ORA-01436: CONNECT BY loop in user data" error message.  It's my understanding that this error message  occurs when child value = parent value.  Knowing that, I ran a query against the base recordset to identify any records where child = parent and there weren't any!  So, why am I getting this error message?  It's got me stumped.  At a high level, my query (which works for every pay period in 3 years worth of data except for 2) is:


SELECT
  CASE
    WHEN INSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), '.' , 2 ) = 0 THEN TO_NUMBER ( SUBSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), 2 , LENGTH ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ))))
    ELSE TO_NUMBER ( SUBSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), 2 , INSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), '.' , 2 )- 2 ))
  END AS ROOT_EMPLOYEE ,
  LEVEL AS HIERARCHY_LEVEL,
  MGR1.*
FROM
  (
  SELECT
    FIELDS
  FROM
    SAMPLE_TABLE
  WHERE
    PER_END_DATE = '19-JAN-2008'
  ) MGR1
CONNECT BY
  PRIOR MGR1.EMP_NUM = MGR1.MGR_NUM

Open in new window

0
Comment
Question by:Kurt Reinhardt
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 23145477
Well, the reason is - parent value of one of the nodes is a child of its own.
In the example below employee 7566 has the parent value 7876, but in fact it is the grand child of 7566 itself. So a tree traversal will loop back to the same node.

You can use NOCYCLE to get over this issue. Pseudo column CONNECT_BY_ISCYCLE will help you to identify the problematic rows.
Try this query.

SELECT
  CASE
    WHEN INSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), '.' , 2 ) = 0 THEN TO_NUMBER ( SUBSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), 2 , LENGTH ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ))))
    ELSE TO_NUMBER ( SUBSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), 2 , INSTR ( SYS_CONNECT_BY_PATH ( MGR1.EMP_NUM , '.' ), '.' , 2 )- 2 ))
  END AS ROOT_EMPLOYEE ,
  LEVEL AS HIERARCHY_LEVEL,
  MGR1.*,
  CONNECT_BY_ISCYCLE
FROM
  (
  SELECT
    FIELDS
  FROM
    SAMPLE_TABLE
  WHERE
    PER_END_DATE = '19-JAN-2008'
  ) MGR1
CONNECT BY NOCYCLE
  PRIOR MGR1.EMP_NUM = MGR1.MGR_NUM

SQL> select * from emp2;
 
     EMPNO ENAME                JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN                SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD                 SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES                MANAGER         7876 02-APR-81       2975                    20
      7654 MARTIN               SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE                MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK                MANAGER         7839 09-JUN-81       2450                    10
      7839 KING                 PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER               SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES                CLERK           7698 03-DEC-81        950                    30
      7902 FORD                 ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER               CLERK           7782 23-JAN-82       1300                    10
      7788 SCOTT                ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS                CLERK           7788 23-MAY-87       1100                    20
 
14 rows selected.
 
SQL> select lpad('*',level,'*')||ename ename, empno, mgr, level
  2  from emp2
  3  start with empno = 7566
  4  connect by prior empno = mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data
 
 
 
no rows selected
 
SQL> select lpad('*',level,'*')||ename ename, empno, mgr, level, CONNECT_BY_ISCYCLE
  2  from emp2
  3  start with empno = 7566
  4  connect by nocycle prior empno = mgr;
 
ENAME                     EMPNO        MGR      LEVEL CONNECT_BY_ISCYCLE
-------------------- ---------- ---------- ---------- ------------------
*JONES                     7566       7876          1                  0
**SCOTT                    7788       7566          2                  0
***ADAMS                   7876       7788          3                  1
**FORD                     7902       7566          2                  0
***SMITH                   7369       7902          3                  0
 
SQL> 

Open in new window

0
 
LVL 26

Author Comment

by:Kurt Reinhardt
ID: 23145569
Those are 10g functions.  Since I'm using 9i, they won't work.
0
 
LVL 26

Author Comment

by:Kurt Reinhardt
ID: 23145812
FYI - I understand what you're saying in regards to the parent-grandchild relationship causing the loop as opposed to a direct parent-child relationship causing the loop.  What I don't know how to do is find and filter out the offending root record, since I can't use the 10g keywords.  I've asked the DBA if we have a 10g dev environment.  If so, we might be able access the data in 10g through a db_link and process the query in 10g, thereby taking advantage of the 10g keywords.  I don't know if we have the capability, but it's worth asking.  In lieu of that, I'd love to find a pl/sql solution in 9i.
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 23146141
Writing the complete tree traversing logic, could be complicated.
You may try to iterate through your hierarchical query using a technique like the attached code to see which of the sub-trees has loop in the data.

SQL>     select lpad('*',level,'*')||ename ename, empno, mgr, level
  2      from emp2
  3      connect by prior empno = mgr
  4  /
ERROR:
ORA-01436: CONNECT BY loop in user data
 
 
 
no rows selected
 
SQL> 
SQL> set serveroutput on size 20000
SQL> declare
  2   l_n number;
  3  begin
  4   for rec in (select empno from emp2) loop
  5    begin
  6     select count(*) into l_n
  7     from (
  8      select lpad('*',level,'*')||ename ename, empno, mgr, level
  9      from emp2
 10      start with empno = rec.empno
 11      connect by prior empno = mgr
 12     );
 13    exception
 14     when others then
 15      if sqlcode = -1436 then
 16       dbms_output.put_line(rec.empno);
 17      end if;
 18    end;
 19   end loop;
 20  end;
 21  /
7566
7788
7876
 
PL/SQL procedure successfully completed.

Open in new window

0
 
LVL 26

Author Closing Comment

by:Kurt Reinhardt
ID: 31524826
Thank you for your assistance.  We were able to identify the 1 (!) employee record out of thousands that indirectly managed itself.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question