Solved

Troubleshooting ORA-01436: CONNECT BY loop in user data

Posted on 2008-12-10
5
10,889 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

688 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