| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 'ORA-01436: CONNECT BY loop in user data' - how can I find the loop ?
Biwi,
For your light *grin* reading.. (if it interests you, i.e.)
The orignal problem is on the bottom of this email..
The person had a problem where the parent pointed to a child and the child itself was a parent of the same parent.. E.g. employee is a manager of another employee who is (according to bad data) manager of employee who is his manager..
-love, anu
"Michel Cadot" <micadot_at_netcourrier.com> on 11/09/99 06:05:26 AM
Please respond to "comp.databases.oracle.server_at_list.deja.com"
<comp.databases.oracle.server_at_list.deja.com>
To: "comp.databases.oracle.server_at_list.deja.com"
<comp.databases.oracle.server_at_list.deja.com>
cc:
Subject: Re: 'ORA-01436: CONNECT BY loop in user data' - how can I find the
loop ?
Message from the Deja.com forum:
comp.databases.oracle.server
Your subscription is set to individual email delivery
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);
14
15
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;
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 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) > > _____________________________________________________________Deja.com: Before you buy.
Sent via Deja.com http://www.deja.com/ Before you buy. Received on Tue Nov 16 1999 - 13:43:39 CST
 |
 |