Re: dynamic delete script based on columns

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 20 Nov 2007 06:05:15 -0800
Message-ID: <1195567508.388814_at_bubbleator.drizzle.com>


ralphbuetikofer_at_yahoo.de wrote:
> hi all,
>
> i got a big problem and hope, somebody can help. i have to create a
> dynamic delete script which has to run once a day. the script should
> find all tables containing a specific column (DEL_YN). then it should
> physically delete all records in this tables with value 'Y' in that
> column which indicates that the row has been 'deleted' by a user.
>
> i know i can get a cursor with all table-names by the sys.all_objects
> joining with the sys.all_tab_columns to determine if the specific
> column exists in the table.
> and i do know i can use this cursor to delete the records in all the
> tables.
>
> what i do not know is how can i assure that the delete script runs
> through the tables in the right order (i.e. dependencies,
> constraints......)??
>
> can anybody help?
>
> thanks in advance!
>
> kind regards,
> bueti

Tracking down dependencies is a lot of work made simple by building your constraints correctly. Replace your current referential constraints with deferrable constraints and the issue goes away.

For examples of deferrable referential constraints look here: http://www.psoug.org/reference/constraints.html#cpr

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Nov 20 2007 - 15:05:15 CET

Original text of this message