Re: any way drop several tables in one go?

From: a <a_at_x.y>
Date: Tue, 20 Jul 1999 13:27:31 +1000
Message-ID: <o2Sk3.4438$r61.32535@ozemail.com.au>

since it appears you have the list of tables to drop conveniently in another table, you can create a sql script using a sql statement:

spool dropem.sql
select 'drop table ' || table_name || ';' from table_delete; spool off
@dropem

-ak

    Ruiping Gao wrote in message <378043E2.66C9D367_at_dpiwe.tas.gov.au>...

Hi,
Is there any way to delete several tables in one go? I had 200 tables created by user and need delete it in one go. What I did is first create a table to hold all table name which I what to delete. Then I use PL/SQL did following:
DECLARE
ta table_delete.table_name%type;
cursor a is select table_name from table_delete; BEGIN

        open a;
        fetch a into ta;
        while a%found loop
            drop table ta;
        fetch a into ta;
        end loop;
        close a;

END;
I got following error message
RROR at line 8:

ORA-06550: line 8, column 3:
PLS-00103: Encountered the symbol "DROP" when expecting one of the
following:

begin declare exit for goto if loop mod null pragma raise return select update while <an identifier> <a double-quoted delimited-identifier> etc. Thanks in advance,
ruiping
e_mail:ruiping_at_dpiwe.tas.gov.au Received on Mon Jul 19 1999 - 22:27:31 CDT