Re: constraints question
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: eugene.pipko@xxxxxxxxxxxx, 'oracle-l-freelists' <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Apr 2009 15:05:21 -0600
If you are on 10g, add the NOCYCLE option to avoid this situation. You
should also add CONNECT_BY_ISCYCLE to check the constraint that is
self-referencing. More can be found in the 10g SQL Reference manual
(quoted below)
"The |NOCYCLE| parameter instructs Oracle Database to return rows from a
query even if a |CONNECT| |BY| |LOOP| exists in the data. Use this
parameter along with the |CONNECT_BY_ISCYCLE| pseudocolumn to see which
rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn
<mailbox:///C%7C/Home/Netscape%20Mail/daniel.fink_optimaldba.com/Trash?number=1033604&part=1.1.2&filename=pseudocolumns001.htm>
for more information."
Regards,
Daniel Fink
--
Daniel Fink
OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be/
Eugene Pipko wrote:
Thanks Jack,
Query fails with ORA-01436: CONNECT BY loop in user data.
I believe it is because there is a self-referencing constraint on SALES
Eugene Pipko
Seattle Pacific Industries
office: 253.872.5243
cell: 206.304.7726
P Please consider the environment before printing this e-mail.
*From:* jack.van.zanen@xxxxxxxxx [mailto:jack.van.zanen@xxxxxxxxx] *On
Behalf Of *Jack van Zanen
*Sent:* Wednesday, April 29, 2009 9:37 PM
*To:* Mark.Bobak@xxxxxxxxxxxx
*Cc:* Eugene Pipko; oracle-l-freelists
*Subject:* Re: constraints question
try this
select
lpad(' ',2*(level-1)) ||table_name from
(
select a.table_name , b.table_name parent
from user_constraints a left outer join user_constraints b on
(a.r_constraint_name=b.constraint_name)
)
start with table_name='SALES'
connect by prior table_name=parent
Other related posts: