I think you should be using dynamic sql to do this,
PLSQL can not create or drop tables.
Tom
<weijendavid_at_my-deja.com> wrote in message
news: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 Tue Dec 28 1999 - 19:22:07 CST
