Re: 'ORA-01436: CONNECT BY loop in user data'

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 9 Nov 1999 12:05:26 +0100
Message-ID: <808v97$oll$1@oceanite.cybercable.fr>

That's not easy because you have to travel through the tree. The example bellow gives you an idea of what you can do:

set serveroutput on
drop table t;
create table t (id number, pid number);

insert into t values (0, null);
insert into t values (1, 0);
insert into t values (2, 0);
insert into t values (3, 1);
insert into t values (4, 1);
insert into t values (10, null);
insert into t values (11, 10);
insert into t values (12, 10);
insert into t values (13, 11);
insert into t values (14, 13);
insert into t values (15, 14);
insert into t values (13, 15);
insert into t values (16, 13);
insert into t values (17, 16);
insert into t values (14, 17);
insert into t values (21, 23);
insert into t values (22, 21);
insert into t values (23, 22);
insert into t values (30, null);
insert into t values (31, 31);
insert into t values (32, 31);

commit;
select substr(lpad(' ', 2*(level-1))||id,1,10) id from t connect by prior id = pid
start with pid is null
/
ID

0
  1
    3
    4
  2
10
  11
    13

      14
        15

ERROR:
ORA-01436: CONNECT BY loop in user data

Create or replace package FL_PKG as

Type elem is record (id t.id%TYPE, pid t.pid%TYPE); Type pile is table of elem index by binary_integer; Empty pile;
Procedure FindLoop ;
End FL_PKG;
/

Create or replace package body FL_PKG as

NbRow integer;
Rows pile;

Procedure FindLoopB (

      the_id t.id%TYPE,
      the_pile pile default Empty,
      the_level integer default 0,
      non_self integer default 0
      ) is
      i        integer;
      iMem     integer;
      my_level integer;
      new_pile pile;
      line     varchar2(1000);
      found    number(1);
      Cursor curs is
         Select id, pid from t
         where pid = the_id and ( non_self = 0 or pid != id )
         order by 1;
   Begin
      If ( the_level != 0 ) then
         For i in 1..the_level loop
            new_pile(i) := the_pile(i);
         End loop;
      End if;
      my_level := the_level + 1;
      For rec in curs loop
          found := 0;
          If ( rec.id != rec.pid ) then /* If not self-parent */
             /* Search for sub-trees already known */
             For i in 1..NbRow loop
                If ( Rows(i).id = rec.id and Rows(i).pid = rec.pid ) then
                   found := found + 1;
                   Exit;
                End if;
             End loop;
             /* Add the new sub-tree in the list */
             If ( found = 0 ) then
                NbRow := NbRow + 1;
                Rows(NbRow).id := rec.id;
                Rows(NbRow).pid := rec.pid;
             End if;
             /* Search for a loop */
             /* Loop is in the know sub-tree => found = 2 */
             For i in 1..the_level loop
                If ( the_pile(i).id = rec.id and the_pile(i).pid = rec.pid )
then
                   found := found + 1;
                   iMem := i;
                   Exit;
                End if;
             End loop;
             /* Display the loop */
             If ( found = 2 ) then
                line := '';
                For i in iMem..the_level loop
                    If ( i <> iMem ) then
                       line := line || ' - ';
                    End if;
                    line := line || the_pile(i).id;
                End loop;
                line := line || ' - ' || rec.id;
                dbms_output.put_line(line);
             End if;
             /* Search in the children */
             If ( found = 0 ) then
                new_pile(my_level).id := rec.id;
                new_pile(my_level).pid := rec.pid;
                FindLoopB (rec.id, new_pile, my_level);
             End if;
          Else /* Case self-parent */
             dbms_output.put_line(rec.id || ' - ' || rec.id);
             new_pile(my_level).id := rec.id;
             new_pile(my_level).pid := rec.pid;
             FindLoopB (rec.id, new_pile, my_level, 1);
          End if;
      End loop;

End;

Procedure FindLoop is

      i        integer;
      found    number(1);
      Cursor curs is Select id from t order by id;
   Begin
      NbRow  := 0;
      dbms_output.enable (100000);
      For rec in curs loop
         found := 0;
         For i in 1..NbRow loop
            If ( Rows(i).id = rec.id ) then
               found := 1;
               Exit;
            End if;
         End loop;
         If ( found = 0 ) then
            FindLoopB (rec.id);
         End if;
      End loop;

End;

End FL_PKG;
/

prompt Search for loops...
exec fl_pkg.FindLoop;

Search for loops...

14 - 15 - 13 - 14
15 - 13 - 16 - 17 - 14 - 15
22 - 23 - 21 - 22
31 - 31

PL/SQL procedure successfully completed.

--
Have a nice day
Michel

Igor V. Podolsky <igoryok_at_soft-review.kiev.ua> a écrit dans le message : ABr5l9uSo5_at_soft-review.kiev.ua...
> Hi all !
>
> I've got error about loop in user data. Fine, that's possible.
> But have anybody ever tried to write SQL that can find the loop ?
> Everytime I try I got the same error message. :( Any ideas ?
>
> P.S. Please duplicate answers ( if any :( ) to my e-mail.
>
> --
> Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)
>
> Igor V. Podolsky (igoryok_at_soft-review.kiev.ua)
>
>
Received on Tue Nov 09 1999 - 05:05:26 CST

Re: 'ORA-01436: CONNECT BY loop in user data' - how can I find the loop ?