Re: Drop ALL Tables!!How??

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 28 Sep 1998 17:27:38 GMT
Message-ID: <3611c43c.18448918@dcsun4.us.oracle.com>

On Mon, 28 Sep 1998 22:54:40 +0800, Cincout <checkin_at_hongkong.com> wrote:

>I am new to PL/SQL, I have tried to write a script to drop all tables as
>follow but enter a runtime error.
>What wrong is It?
>Thanks a lot!!
>
>Begin
>for curTable in (select table_name from user_tables) loop
> DROP TABLE CURTABLE.TABLE_NAME;
>end loop;
>END;
You can't do DDL directly in PL/SQL. You will need to use dynamic sql.

eg.

declare
  c number;
  s number;
begin
  c := dbms_sql.open_cursor;
  for x in (select table_name from user_tables) loop     dbms_sql.parse( c, 'drop table '||x.table_name, dbms_sql.native );     s := dbms_sql.execute( c );
  end loop;
  dbms_sql.close_cursor( c );
end;
/

Just be careful which database user you run this as.

chris. Received on Mon Sep 28 1998 - 12:27:38 CDT