Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor to drop all tables

Re: Cursor to drop all tables

From: Marcin Wlodarski <mwlodars_at_ns1.wonlok.com.pl>
Date: Thu, 30 Dec 1999 14:31:52 GMT
Message-ID: <Pine.BSF.4.05.9912301547040.19858-100000@ns1.wonlok.com.pl>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US