Re: dropping a table, if it exists?

By script, I'll assume that you mean a sql script instead of PL/sql. If that's true a refinement of this will work:

spool drops.sql
select 'drop table '||table_name||' ;'
from user_tables
where <table name selected by you criteria>; spool off
@drops

the refinemts are in eliminating headers, feedback counts, and prompts from the spool file so that it runs with out error. (those can all be removed by various SET options.

(If the script language is not locked into sql or PL/SQL, why not use PERL??)

In article <3a78a5ce.7648364_at_news.newcastle.edu.au>,   hn67_at_mailcity.com (Hope) wrote:
> hi, it's part of a script that has to test many tables, drop them
> recreate them etc. It has to run without error.
>
> sorry, I should have been clearer!
>
> Hope
>
> On Wed, 31 Jan 2001 15:50:16 GMT, Brian Peasland
> <peasland_at_edcmail.cr.usgs.gov> wrote:
>
> >Why not just issue the DROP TABLE command? If the table exists, it
 will
> >be dropped. If it doesn't exist, then you'll just get a harmless
 error
> >message which you can ignore.
> >
> >HTH,
> >Brian
> >
> >
> >Hope wrote:
> >>
> >> hi-
> >>
> >> I am trying to test to see if a table exists, and then drop it. I
> >> would have thought that
> >>
> >> drop table advsearch
> >> where exists (select * from user_tables where
> >> table_name='ADVSEARCH';);
> >>
> >> would work, but it doesn't.
> >>
> >> I have also tried
> >>
> >> BEGIN
> >> drop table ADVSEARCH;
> >> create table ADVSEARCH ( testfield number );
> >> EXCEPTION
> >> create table ADVSEARCH (testfield number);
> >> END;
> >>
> >> to no avail. Please please help?
> >>
> >> gratefully,
> >>
> >> Hope
> >
> >--
> >========================================
> >Brian Peasland
> >Raytheons Systems at
> > USGS EROS Data Center
> >These opinions are my own and do not
> >necessarily reflect the opinions of my
> >company!
> >========================================
>
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support
440-498-3700 magic_at_magicinterface.com <<<WE MOVED!


Sent via Deja.com
http://www.deja.com/