Re: Dynamic SQL question in 8i or 7.3.4

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 10 Sep 1999 10:32:47 +0100
Message-ID: <936956864.12804.0.nnrp-11.9e984b29@news.demon.co.uk>

This requires a little massage to do want you want most effectively, but for 8.1 try:

create table t1 (n1 number, v1 varchar2(20)); insert into t1 values (1,'junk');
insert into t1 values (2,'more junk');

create or replace procedure del_row (

    i_table in varchar2(32),
    i_id in number
) as

begin

    execute immediate

        'delete from ' || i_table || ' where n1 = :v1'
        using i_id

    ;
end;
/

execute del_row ('t1',1);

I have taken advantage of the 'using' clause so that only one SQL statement per table gets into the shared pool, rather than one sql statement per reference row.

The same sort of thing can be done in 7.3 (there is an article on binding with pl/sql on my website) but it is a lot messier.

You will want to open a cursor loop on
your driving table (best ordered by
table_name probably) and perform
the execute immediate for each row
rather than calling a procedure to do
the execute immediate,

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Tom Ashbrook wrote in message <7ra1or$dcf_at_dfw-ixnews21.ix.netcom.com>...
>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;
Received on Fri Sep 10 1999 - 04:32:47 CDT