Re: DROP tables in PL/SQL

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 29 Nov 2005 07:11:46 -0500
Message-ID: <9OadndtqSfUY2BHenZ2dnUVZ_t-dnZ2d@comcast.com>

"William Robertson" <william.robertson_at_bigfoot.com> wrote in message news:1133264667.708384.114060_at_o13g2000cwo.googlegroups.com...
> mlachajczyk_at_reporter.com.pl wrote:

>>
>> 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;
>>
>> [snip]
>>
>> where did I make a mistake, in syntax or in consistence?
>

> There is no "IF EXISTS" syntax in PL/SQL.

>

> Also it is generally not a good idea to create/alter/drop tables within
> a stored procedure. What is the business requirement? Could it not be
> implemented using partitions of a permanent table?
>

you can get a copy of the PL/SQL manaual (and all Oracle docs) at http://tathiti.oracle.com, which will help you get familiar with PL/SQL syntax and limitations.

as the other response suggested, execute immediate is required since DDL is not directly supported in PL/SQL

if you do this (again, you may be headed down the wrong path, since tables are no usually created and droppped as part of runtime operations in an Oracle application) you will need to trap the exception -- 'generally' it is more effiicient to try the operation and catch the exception (letting oracle figure out if the operation is possible, in this case, if the table exists) rather than write your own code to do what Oracle will have to do anyway.

++ mcs Received on Tue Nov 29 2005 - 06:11:46 CST