Cursor to drop all tables

From: <weijendavid_at_my-deja.com>
Date: Wed, 29 Dec 1999 00:38:21 GMT
Message-ID: <84bkma$pfh$1@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 - 18:38:21 CST