Re: Drop Table in PLSQL Script

You need to specify version. There are a couple of ways as noted in other posts. In general you should not do this if it is a frequent thing. If it is an occasional thing then fine, but otherwise you should probably do it via other means.
Jim
"August" <august.pasimio_at_quest.com> wrote in message news:890bf4f6.0206250937.268d301_at_posting.google.com...
> Hi, All -
>
> I wrote this plsql script to drop some of the tables I no longer need:
> 1 declare
> 2 my_table_name varchar(30);
> 3 cursor my_cursor is
> 4 select table_name from user_tables
> 5 where table_name like 'TEST_TABLE%';
> 6
> 7 begin
> 8 open my_cursor;
> 9 loop
> 10 fetch my_cursor into my_table_name;
> 11 exit when my_cursor%NOTFOUND;
> 12 drop table my_table_name;
> 13 end loop;
> 14 end;
> 15 /
> ~
>
> When I run it, I get this error:
> SQL> _at_drop_test_tables
> drop table my_table_name;
> *
> ERROR at line 12:
> ORA-06550: line 12, column 3:
> 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 execute commit forall
> <a single-quoted SQL string>
>
> Is drop table not allowed in a plsql script?
>
> Thanks in advance,
> August
> SQL>
Received on Wed Jun 26 2002 - 03:47:05 CEST