Xref: alice comp.databases.oracle.misc:40702 comp.databases.oracle.server:65237 comp.databases.oracle.tools:31682
Path: alice!news-feed.fnsi.net!enews.sgi.com!newsfeed.berkeley.edu!europa.netcrusader.net!205.252.116.205!howland.erols.net!ix.netcom.com!news
From: "Tom Ashbrook" <tashbrk@ix.netcom.com>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Dynamic SQL question in 8i or 7.3.4
Date: Thu, 9 Sep 1999 21:30:20 -0400
Organization: Netcom
Lines: 34
Message-ID: <7ra1or$dcf@dfw-ixnews21.ix.netcom.com>
X-NETCOM-Date: Thu Sep 09 11:34:35 PM CDT 1999
X-Newsreader: Microsoft Outlook Express 4.72.3110.1
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3

I have a situation where I need to read some dates and table names from one
table and delete data from several others based on this information.  For
example

ARCHIVE_TABLE
table_name    archive_date
----------------    ------------------
xx                    9/9/99
yy                    9/7/99
zz                    5/5/99

would need to execute 3 SQL stmts (in some fashion or the other):
delete from xx where somedate <= 9/9/99;
delete from yy where somedate <= 9/7/99;
delete from zz where somedate <= 5/5/99;

where xx, yy, and zz are variables.

I would have thought that this was simple but I am having a very hard time
using SQL to generate and execute dynamic SQL.  i.e. delete from
sometablenamevariable where x=y;

I can create one procedure to do all the tables serially but I would have to
edit it every time a table got created or dropped.

I can cron a perl job or run one through PLEX too but I should be able to do
this in PL\SQL.  No?

Any help would be appreciated.
TIA,
Tom.