Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

Need help in resolving CONNECT BY loop in user data in Oracle 9.2.0.5 Ver

432862
432862 Member Posts: 209
edited November 2010 in SQL & PL/SQL
Hi Everyone,

Below are the scripts to reproduce the error

CREATE TABLE TESTING
(
C1 NUMBER,
C2 NUMBER,
GRP NUMBER
);


Insert into TESTING
(C1, C2, GRP)
Values
(1, 2, 100);
Insert into TESTING
(C1, C2, GRP)
Values
(1, 3, 100);
Insert into TESTING
(C1, C2, GRP)
Values
(2, 5, 200);
Insert into TESTING
(C1, C2, GRP)
Values
(3, 11, 200);
Insert into TESTING
(C1, C2, GRP)
Values
(11, 1, 400);
COMMIT;

SELECT sys_connect_by_path(C1,'/') AS C1,C2
FROM TESTING
CONNECT BY C1 = PRIOR C2

I am getting "ORA-01436: CONNECT BY loop in user data" because of Recurssion.

My Oracle Version is 9.2.0.5, can you guide me how to resolve in 9.2.0.5
In Oracle 10g I have read that we can achieve through NOCYCLE option

Can you please guide me how to solve this in Oracle 9.2.0.5 Version

Thanks in Advance,
Sankar
«1

Answers

  • 12826
    12826 Member Posts: 1,849
    edited March 2009
    Add NOCYCLE
    SELECT sys_connect_by_path(C1,'/') AS C1,C2
    FROM TESTING
    CONNECT BY nocycle C1 = PRIOR C2
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    edited March 2009
    Sankar,

    How many records are you expecting to be returned by your query? 134?

    Edited by: hoek on Mar 24, 2009 3:21 PM
  • 666352
    666352 Member Posts: 1,442
    May be this code
    DECLARE
       l_n    VARCHAR2 (2000);
       l_c2   VARCHAR2 (20);
    BEGIN
       FOR rec IN (SELECT c1
                     FROM testing)
       LOOP
          BEGIN
             SELECT     SYS_CONNECT_BY_PATH (c1, '/') AS c1, c2
                   INTO l_n, l_c2
                   FROM testing
             START WITH c1 = rec.c1
             CONNECT BY c1 = PRIOR c2;
    
             DBMS_OUTPUT.put_line (l_n || ' - ' || l_c2);
          EXCEPTION
             WHEN OTHERS
             THEN
                IF SQLCODE = -1436
                THEN
                   NULL;
                END IF;
          END;
       END LOOP;
    END;
    /
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited March 2009
    Hi,

    Sorry, there is no good, general way to do NOCYCLE in Oracle 9 SQL.

    You could write PL/SQL functions to do particular things.
    For example, you could write a pipelined PL/SQL function that, given a c1 value (let's call it starting_c1), produces the same reuslts as:
    SELECT  SYS_CONNECT_BY_PATH (c1, '/')
    FROM    testing
    START WITH         c1 = starting_c1
    CONNECT BY NOCYCLE c1 = PRIOR c2;
    Edited by: Frank Kulash on Mar 24, 2009 10:43 AM
  • 432862
    432862 Member Posts: 209
    hoek,

    This is my desired output

    C1, C2
    /1 2
    /1/2 5
    /1 3
    /1/3 11
    /2 5
    /3 11
    /11 1 -- recurssion start here and it shoudl stop here

    Thanks,
    Sankar
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,386 Black Diamond
    VG2 wrote:
    Add NOCYCLE
    Did you read the topic name? There is no NOCYCLE in 9i.

    SY.
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    edited March 2009
    Plus: adding just nocycle doesn't return the desired output.

    Edited by: hoek on Mar 24, 2009 3:44 PM
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    edited March 2009
    perhaps STRAGG (string aggregation) is a useful alternative here?

    You can search for it on www.asktom.oracle.com

    Other than that: We need to find the right code into the connect by some condition that prevents the loop - but adding AND PRIOR C1 != C2 returns 10 records instead of 7

    (changed 6 to 7 and decided to buy some glasses)
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,386 Black Diamond
    Based on sample data:
    SELECT sys_connect_by_path(C1,'/') path,c2
    FROM TESTING
    CONNECT BY C1 = PRIOR C2
           AND GRP > PRIOR GRP
           AND C1 < C2
    /
    
    PATH                         C2
    -------------------- ----------
    /1                            2
    /1/2                          5
    /1                            3
    /1/3                         11
    /2                            5
    /3                           11
    /11                           1
    
    7 rows selected.
    
    SQL> 
    SY.
  • 12826
    12826 Member Posts: 1,849
    edited March 2009
    You guys should be nicer - my feeling are hurt ;)
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    edited March 2009
    Nice! I totally forgot about the GRP column ;-)
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    Don't take it personally,VG2, it was meant as good help ;-)
  • 666352
    666352 Member Posts: 1,442
    SQL> SELECT     SYS_CONNECT_BY_PATH (c1, '/') AS c1, c2
      2        FROM testing
      3  CONNECT BY  c1 = PRIOR c2 AND (c2 >= PRIOR c1);
    
    C1                                                         C2
    -------------------------------------------------- ----------
    /1                                                          2
    /1/2                                                        5
    /1                                                          3
    /1/3                                                       11
    /2                                                          5
    /3                                                         11
    /11                                                         1
    
    7 ligne(s) sélectionnée(s).
    
    SQL> 
  • 12826
    12826 Member Posts: 1,849
    Just making fun - I never take anything personnel (well, on forums anyway) :)
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    That's what ;-) ' s are for, indeed.
  • 432862
    432862 Member Posts: 209
    Hi my data is like this then it will fail with the Query

    SELECT SYS_CONNECT_BY_PATH (c1, '/') AS c1, c2
    FROM testing
    CONNECT BY c1 = PRIOR c2 AND (c2 >= PRIOR c1);

    SET DEFINE OFF;

    Insert into TESTING
    (C1, C2, GRP)
    Values
    (1, 2, 600);
    Insert into TESTING
    (C1, C2, GRP)
    Values
    (1, 3, 600);
    Insert into TESTING
    (C1, C2, GRP)
    Values
    (2, 5, 200);
    Insert into TESTING
    (C1, C2, GRP)
    Values
    (3, 11, 200);
    Insert into TESTING
    (C1, C2, GRP)
    Values
    (5, 2, 400);

    COMMIT;

    Regards,
    Sankar
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    And what happens when you use Solomon's provided solution?
  • 432862
    432862 Member Posts: 209
    That's not returning the required result set.

    Regards,
    Sankar
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    OK, so given your new dataset, the first one you you provided was not complete.
    We have to decide again when to stop cycling and adjust the connect based on this dataset.
  • Sreekanth Munagala
    Sreekanth Munagala Member Posts: 642
    Hi,
    Is this what you are looking for
    SELECT SYS_CONNECT_BY_PATH (c1, '/') AS c1, c2
    FROM testing
    CONNECT BY c1 = PRIOR c2 AND (c2 > PRIOR c1);
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    We can emulate connect by nocycle in Oracle 9i B-)

    http://laurentschneider.com/wordpress/2008/09/cycling.html
    http://www.geocities.jp/oraclesqlpuzzle/8-43.html
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    Sankar, since we have to decide again where to stop cycling, it would come in quite handy if you post your desired output ;-)
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    Hi Aketi,

    The dbms_random 'trick' doesn't seem to work for SYS_CONNECT_BY_PATH.
    I still get ORA-01436.

    (10Gr2)
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited March 2009
    my solution works B-)
    col c1 for a20
    
    SELECT sys_connect_by_path(C1,'/') AS C1,C2
      FROM TESTING
    CONNECT BY nocycle C1 = PRIOR C2
    order by C1,C2;
    
    C1           C2
    -----------  --
    /1            2
    /1            3
    /1/2          5
    /1/3         11
    /1/3/11       1
    /1/3/11/1     2
    /1/3/11/1/2   5
    /11           1
    /11/1         2
    /11/1         3
    /11/1/2       5
    /11/1/3      11
    /2            5
    /3           11
    /3/11         1
    /3/11/1       2
    /3/11/1       3
    /3/11/1/2     5
    In oracle 9i,it is easy to emulate connect_by_root ;-)
    select min(C1) as c1,C2
    from (SELECT sys_connect_by_path(C1,'/') AS C1,C2,
          connect_by_root(RowID) as treeID,RowID as nodeID
            FROM TESTING
          CONNECT BY C1 = PRIOR C2
            and level<=50
            and prior dbms_random.value != 1)
    group by treeID,nodeID,C2
    order by C1,C2;
    SQL> select * from v$version;
    BANNER
    ---------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    OP has 9i, he can't use nocycle.

    Funny, I get different results for 10.2.0.4:
    SQL> select min(C1) as c1,C2
      2  from (SELECT sys_connect_by_path(C1,'/') AS C1,C2,
      3        connect_by_root(RowID) as rootID,RowID as nodeID
      4          FROM TESTING
      5        CONNECT BY C1 = PRIOR C2
      6          and level<=50
      7          and prior dbms_random.value != 1)
      8  group by rootID,nodeID,C2
      9  order by C1,C2;
            FROM TESTING
                 *
    ERROR at line 4:
    ORA-01436: CONNECT BY loop in user data
    
    
    Elapsed: 00:00:00.01
    
    SQL> select banner from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
    NLSRTL Version 10.2.0.4.0 - Production
    
    Elapsed: 00:00:00.01
This discussion has been closed.