Re: sql script delete all tables

Tim X <timx_at_spamto.devnul.com> wrote in message news:<87of43lptd.fsf_at_tiger.rapttech.com.au>...
> Untested and off the top of my head - but it might give you the
> idea. ASsuming oracle 8i or better
>
> set serveroutput on size 1000000
> declare
> cursor tab_list is
> select table_name
> from user_tables;
> tname tab_list_at_ROWTYPE;
> sql_str varchar2(256);
> begin
> for tname in tab_list loop
> sql_str := 'drop table '||tname;
> execute immediate sql_str;
> end loop;
> exception
> when others then
> dbms_output.put_line(SQLERRM);
> end;
> /

That will almost work, but has 1 bug and some redundant variable declarations, redundant exception handler etc. This is enough:

begin
  for trec in (select table_name from user_tables) loop     execute immediate 'drop table '||trec.table_name;   end loop;
end;
/ Received on Sat Mar 22 2003 - 09:26:48 CST