Re: need help in SQL script

"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