RE: constraints question

From: Eugene Pipko <eugene.pipko_at_unionbay.com>
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