Re: Drop ALL Tables!!How??

In article <360FA330.8F7CFB6A_at_hongkong.com>,   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 are not allowed to execute static DDL in PL/SQL. Instead, you must use dynamic SQL.

Unless you have to do this repeatedly, or have a reason for doing with PL/SQL, it's easier to generate a SQL script using a script such as

set pages 0
set feedb off
select 'drop table '||table_name||' cascade constraints;' from user_tables

spool dropt.sql
/

run from SQL*Plus.

Paul

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Sep 29 1998 - 09:56:48 CDT