Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor to drop all tables
In PL/SQL you can't use SQL data definition language (drop,alter,create).
You must use dynamic SQL(package DBMS_SQL is useful for do it).
create or replace procedure droptable is
cid integer; num integer; cursor c_droptable is select table_name from all_tables; begin cid:=DBMS_SQL.OPEN_CURSOR;--open cursor for i in c_droptable loop DBMS_SQL.PARSE(cid,'DROP TABLE '||i.table_name,dbms_sql.v7);--parsing num:=DBMS_SQL.EXECUTE(cid);--execute end loop; DBMS_SQL.CLOSE_CURSOR(cid);--close cursor commit; EXCEPTION when others then DBMS_SQL.CLOSE_CURSOR(cid);--close cursor RAISE;--raise error
end droptable;
Greetings Marcin
On Wed, 29 Dec 1999 weijendavid_at_my-deja.com wrote:
> 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 Thu Dec 30 1999 - 08:31:52 CST
![]() |
![]() |