Dynamic SQL question in 8i or 7.3.4

From: Tom Ashbrook <tashbrk_at_ix.netcom.com>
Date: Thu, 9 Sep 1999 21:30:20 -0400
Message-ID: <7ra1or$dcf_at_dfw-ixnews21.ix.netcom.com>

[Quoted] 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. Received on Fri Sep 10 1999 - 03:30:20 CEST