Re: Cursor to drop all tables

weijendavid 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.

The problem here is that the DROP TABLE command is considered to be a DDL (data definition language) statement. Only DML (Data manipulation language) statements can be used in PL/SQL unless you use DYNAMIC SQL (DBMS_SQL package). In addition if you just want to import data you might consider just truncating the tables via the TRUNCATE TABLE command (which is also DDL). This can be done via a SQL script such as

select 'TRUNCATE TABLE '||table_name||';' from all_tables;

spool the output to a file and you have a turncate table script.

HTH RC

--
Posted via CNET Help.com
http://www.help.com/ Received on Wed Dec 29 1999 - 10:30:15 CST