Re: Using SQL to write SQL...

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Fri, 29 Mar 2002 08:59:17 -0000
Message-ID: <3ca445fd_1@mk-nntp-1.news.uk.worldonline.com>

"Ryan Gaffuri" <rkg100_at_erols.com> wrote in message news:a80hfj$d1b$1_at_bob.news.rcn.net...
> I know I can create a SQL query to generate more sql code for me. Then
spool
> that to a file and execute the file or just copy and paste it and run it
> from SQLPlus.
>
> Is there a way to do all of this in pl/sql and store it in the database?
Ive
> been trying to figure out how to do it. Can I create a cursor... to build
my
> sql statements.. store it in a record and execute it somehow?
>

Here's a simple (8i) example. I use it to turn on and off all Foreign Key constraints in the schema, for daily data loads. The exception handling is there so that if any fail, the error is logged, that one remains disabled and processing continues.

 CREATE OR REPLACE procedure sp_ri_enable (p_enable in boolean) as

cursor c1 is
select

    constraint_name,
    table_name
from

    user_constraints
where

    constraint_type = 'R';

v_word varchar2(8);
v_err_msg varchar2(500);

begin
if p_enable then

    v_word := 'ENABLE';
else

    v_word := 'DISABLE';
end if;

for r1 in c1 loop

    begin
    execute immediate
   'alter table '||r1.table_name||' '||v_word||' constraint '||r1.constraint_name;

    exception

        when others then
            rollback;
            v_err_msg:=SUBSTR(SQLERRM, 1,500);
            insert into progress_log
values(sq_progress_log.nextval,sysdate,v_err_msg);
            commit;

    end;
end loop;
end sp_ri_enable;
/

HTH,
Paul Received on Fri Mar 29 2002 - 02:59:17 CST