5

I have the following PL/SQL:

declare
    i_cnt number;
begin
    select count(1) into i_cnt 
      from dba_tables 
     where table_name = upper('foo') 
       and owner = upper('bar'); 

if i_cnt > 0 then 
    drop table foo; -- <--- error this line
end if;
end;

From which I get this error.

ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge

How can I drop a table in a procedure?

Ben
  • 51,770
  • 36
  • 127
  • 149
Ray Cheng
  • 12,230
  • 14
  • 74
  • 137

1 Answers1

11

You can't directly execute DDL statements from a PL/SQL block - you'll have to use EXECUTE IMMEDIATE instead:

declare
  i_cnt number;
begin
  select count(1) into i_cnt 
  from dba_tables where table_name=upper('foo') and owner=upper('bar'); 
  if i_cnt > 0 then 
    execute immediate 'drop table foo'; 
  end if;
end;
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • is it possible to combine the `select` and `if` statement into one line? i need to do this for many tables so i want to save some typing. – Ray Cheng May 31 '12 at 17:59
  • 1
    You say you need to do this for many tables. Can you characterize the list of tables based on criteria against DBA_TABLES? In other words, can you write a query against DBA_TABLES and specify an appropriate WHERE clause that would list all the tables you want to drop? If so, you can write a cursor loop and do the execute immediate drop in the loop, specifying bind variable, and loop throug to drop all the tables you want to drop. – Mark J. Bobak May 31 '12 at 19:50
  • @Mark J. Bobak, good idea! i'm already doing it that way for the grants. couldn't know why didn't do that for the tables and sequences. :) – Ray Cheng Jun 01 '12 at 02:33