RE: constraints question

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


brgds

Jack



2009/4/30 Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>>

I think you should be able to do it by walking DBA_CONSTRAINTS w/ a start 
with/connect by.....but it's late and I'm almost out the door... :)



If no one else replies, I'll take a crack at it tomorrow....



-Mark



From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On 
Behalf Of Eugene Pipko
Sent: Wednesday, April 29, 2009 5:36 PM
To: 'oracle-l-freelists'
Subject: constraints question



Hi all,

I started to think about writing an archiving procedure for one of our apps.

The idea is to move/delete records from SALES table and all other tables that 
related to it.

I am using the following query to find all children of the starting table:



select

              parents.owner || '.' || parents.table_name  parent_table,

              child.owner || '.' || child.table_name child_table,

              child.r_constraint_name

from

             dba_constraints child,

             dba_constraints parents

where

              child.r_constraint_name = parents.constraint_name

     and  child.r_owner = parents.owner

     and  parents.table_name = 'SALES";



This query returns 7 tables that are children to SALES.

So now I have to walk all 7 tables looking for children for them as well and so 
forth...

I can loop through them using PLSQL, but is there a way to find all of them all 
the way down specifying starting table using SQL?



Thanks,



Eugene Pipko

Seattle Pacific Industries

office: 253.872.5243

cell: 206.304.7726

P  Please consider the environment before printing this e-mail.





























--
Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the sole 
use of the intended recipient. If you are not the intended recipient, please be 
aware that any disclosure, copying, distribution or use of this e-mail or any 
attachment is prohibited. If you have received this e-mail in error, please 
contact the sender and delete all copies.
Thank you for your cooperation

Other related posts: