DROP tables in PL/SQL

Giganews Newsgroups
Subject: DROP tables in PL/SQL
Posted by:  mlachajcz…@reporter.com.pl
Date: 29 Nov 2005

Hi,

what's the most efficient way to remove tables in procedure,
i.e.

example 1

CREATE OR REPLACE PROCEDURE usun_xxx as
Begin
if EXISTS (select * from user_objects where object_name = 'xxx') then
  begin
    drop table xxx;
  end
End;

or

example 2

CREATE OR REPLACE PROCEDURE usun_xxx as
begin
drop table  xxx;
EXCEPTION
  WHEN OTHERS THEN
  null;
end;

Both examples restore an error:

"DROP" symbol encountered while waiting for 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> <<
  close current delete fetch lock insert open rollback
  savepoint set sql execute commit forall merge
  <a single-quoted SQL string> pipe

and here I probably find a gap in this piece of cake, where's an error,

where did I make a mistake, in syntax or in consistence?

thanks for help

Replies