Re: dropping a table, if it exists?

From: Happy <allan_at_livvy80.freeserve.co.uk>
Date: Tue, 30 Jan 2001 00:25:35 -0000
Message-ID: <9551q0$pk3$1@newsg1.svr.pol.co.uk>

You'd need to do it something like this:

vcounter NUMBER;

BEGIN SELECT count(0)
INTO vcounter
FROM user_tables
WHERE table_name = 'ADVSEARCH';

IF vcounter = 1 THEN

DROP TABLE advsearch;
END IF; END; Depending on version of ORACLE, you may need to use dynamic sql.

Cheers
Allan

"Hope" <hn67_at_mailcity.com> wrote in message news:3a75ee85.7037184_at_news.newcastle.edu.au...
> On Mon, 29 Jan 2001 22:07:49 GMT, hn67_at_mailcity.com (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
> >
> >
> >
> >
> >
>
Received on Mon Jan 29 2001 - 18:25:35 CST