Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: need help in SQL script

Re: need help in SQL script

From: andrija <ar35644_at_fer.hr>
Date: Fri, 11 Jan 2002 14:19:10 +0100
Message-ID: <a1mopd$27kr$1@as201.hinet.hr>

"Siddharth Aggarwal" <siddharth_aggarwal_at_persistent.co.in> wrote in message news:a1njvu$837$1_at_news.vsnl.net.in...
> Hi all,
> I want to write a SQL script for Oracle8i/9i which checks if a table is
> present, and if so, drops it.
> i.e. something like "if exists table1 then drop table1".
> Could somebody suggest how that could be done?
> Thanks,
> Sid.

you could do that in two ways:

first way is to check if table exists and then drop it:

select count(*) into v_temp from dba_all_tables where owner='OWNER' and table_name='TABLE';
if v_temp=1 then

    execute immediate 'drop table OWNER.TABLE'; end if;

second is to try to drop it without checking if it exists. of course, you need to catch the exception if drop fails:

begin

    execute immediate 'drop table OWNER.TABLE'; exception
when others then
null;
end;

you can insert this block in any other block. Received on Fri Jan 11 2002 - 07:19:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US