1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-01436: CONNECT BY loop in user data

Discussion in 'SQL PL/SQL' started by Vicky, Jun 25, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    While executing the query below:


    SELECT emp_id, salary, manager_id, level
    FROM employees
    CONNECT BY PRIOR emp_id = manager_id;

    I'm facing the error below:

    ORA-01436: CONNECT BY loop in user data
    01436. 00000 - "CONNECT BY loop in user data"
    *Cause:
    *Action:

    Could you tell me the problem with the query.,?!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    You get this error when you use a ‘CONNECT BY’ constructs in a query and it results in a loop.
    By ‘loop’, it means the query results at some point in time during execution starts to return back to the point from where it started. During such instances, Oracle detects the error and to avoid the looping, gives a meaningful message like the one you obtained.

    Please check your data - somewhere you must be having a situation like for example:

    Code (SQL):
    CREATE TABLE tloop
      (emp_id NUMBER,
       mgr_id NUMBER
      );

    INSERT INTO tloop VALUES (10, 100);  -- data which will cause looping
    INSERT INTO tloop VALUES (20, 200);
    INSERT INTO tloop VALUES (30, 100);
    INSERT INTO tloop VALUES (100, 10); -- data which will cause looping
    INSERT INTO tloop VALUES (200, NULL);


    SELECT emp_id, mgr_id, level FROM tloop CONNECT BY PRIOR emp_id = mgr_id;

    ORA-01436: CONNECT BY loop IN USER DATA
    01436. 00000 -  "CONNECT BY loop in user data"
    *Cause:    
    *Action:
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Please also note that you can avoid getting this error if you use the NOCYCLE clause:

    Code (SQL):
    SELECT emp_id, mgr_id, level FROM tloop CONNECT BY NOCYCLE PRIOR emp_id = mgr_id;
     
    P.S: As from Oracle 10g.
     
    Vicky likes this.