Home » SQL & PL/SQL » SQL & PL/SQL » Connect by error (10.2.0.3, Windows 2003)
Connect by error [message #413457] Wed, 15 July 2009 18:32 Go to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Hi all,

This sql is giving us error ora-01436 CONNECT BY loop in user data.


Table mf
--------
mname
mseq
mfname

Table mf_perm
-------------
mfname
ugroup
afind
a_add
a_delete
a_update

SQL>select lpad(' ',2*(level-1)) || mfname from mf
 2    where mfname not in
 3  (select mfname from mf_perm where ugroup = 'devel ')
 4   start with mname = 'default'
 5   connect by prior mfname = mname;

ora-01436 CONNECT BY loop in user data


From my research I tried CONNECT BY nocycle, but the query ran for 2hrs without any results..


SQL>select lpad(' ',2*(level-1)) || mfname from mf
 2  where mfname not in
 3 (select mfname from mf_perm where ugroup = 'devel ')
 4  start with mname = 'default'
 5  connect by nocycle mfname = mname;



Any suggestions, please?

Thanks



Re: Connect by error [message #413459 is a reply to message #413457] Wed, 15 July 2009 19:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions, please?
Fix the data.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Connect by error [message #413461 is a reply to message #413457] Wed, 15 July 2009 19:59 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Okay, for fixing the data is there a way to find out where the error is from? Because its a huge table..

Thanks
Re: Connect by error [message #413463 is a reply to message #413461] Wed, 15 July 2009 20:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
pleasehelp wrote on Wed, 15 July 2009 17:59
Okay, for fixing the data is there a way to find out where the error is from? Because its a huge table..

Thanks


There is a way & somewhat straight forward called "binary search".
Load half the data (top half or bottom half) into test table.
Run query against test table.
If query works, problem data is in other half.
If query fails, problem data is in this half.
Start with the half which has problem data.
Load half (now 1/4) the data into new temp table & repeat process.

Repeat process until problem is isolated.

This will work.
If you have a better solution, have at it.
Re: Connect by error [message #413495 is a reply to message #413461] Thu, 16 July 2009 01:42 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
In Your first queue there You have:
 connect by PRIOR mfname = mname;

In the second I miss PRIOR
connect by mfname = mname;

To find the CYCLE You could use CONNECT_BY_ISCYCLE Pseudocolumn http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#sthref787 and decrease the search depth with a LEVEL condition:
connect by nocycle (prior mfname = mname AND LEVEL<4);

[Updated on: Thu, 16 July 2009 01:46]

Report message to a moderator

Re: Connect by error [message #413597 is a reply to message #413457] Thu, 16 July 2009 11:17 Go to previous message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Thanks Jum.

This works.

SQL>select lpad(' ',2*(level-1)) || mfname from mf
2 where mfname not in
3 (select mfname from mf_perm where ugroup = 'devel ')
4 start with mname = 'default'
5 connect by nocycle prior mfname = mname;
Previous Topic: ORA-06550: line 1, column 8: PLS-00103
Next Topic: Working one query but trouble with some extension please help me
Goto Forum:
  


Current Time: Sun Dec 04 14:37:11 CST 2016

Total time taken to generate the page: 0.12582 seconds