Re: Cursor to drop all tables

If you only need to do this once or occaisonally, try from within SQL*Plus:

set pages 0
set lines 999
select 'drop table '||owner||'.'||table_name||';' from all_tables
where owner not in ('SYS','SYSTEM')

spool /tmp/dropall.sql
/
spool off
@/tmp/dropall

• Not dropping tables owned by sys or system will make it much easier for you...
• The spool file contains a set of DDL commands for dropping all the tables.

Try and enjoy!
weijendavid_at_my-deja.com wrote in message <84bkma$pfh$1_at_nnrp1.deja.com>...
>Hi, all
>
>I'm trying to use Cursor to drop all tables in the database so that I
>can 'import' the backup copy to the 2nd database(stand by).
>This is what I have written:
>******************************************
>create or replace procedure droptable
>is
> cursor c_droptable is
> select table_name from all_tables;
> v_droptable c_droptable.table_name%type;
> begin
> open c_droptable;
> loop
> fetch c_droptable into v_droptable;
> exit when c_droptable%NOTFOUND;
> drop table v_droptable;
> end loop;
> close c_droptable;
> end;
>/
>*************************************
>
>Then the error message araise:
>
>LINE/COL ERROR
>------------------------------------------------------------------------
>11/5 PLS-00103: Encountered the symbol "DROP" when expecting one of
> the following:
> begin declare end exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql commit <a single-quoted SQL string>
>
>What's wrong with the 'drop' statement here? Can't I use 'drop' in a
>cursor. Please help me with this, and thanx.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed Dec 29 1999 - 08:17:14 CST