RE: constraints question
Date: Thu, 30 Apr 2009 08:31:59 -0700
Message-ID: <34DB87F47199374280ADFD2968CDBCFA87D621FC48_at_MAIL01KT.seattlepacificindustries.com>
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_at_gmail.com [mailto:jack.van.zanen_at_gmail.com] On Behalf Of Jack van Zanen
Sent: Wednesday, April 29, 2009 9:37 PM
To: Mark.Bobak_at_proquest.com
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_at_proquest.com<mailto:Mark.Bobak_at_proquest.com>>
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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] 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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 30 2009 - 10:31:59 CDT