Re: Conditionally dropping tables in script

To all:

Please read the question before the ranting. I know in this newsgroup there is much ranting about SQL Server. I would think it would be serve this group if questions were answered rather than speechs made.

What was asked was whether it was possible in a "script" not a procedure.

The answer about checking the count of tables is the best and was delivered without preaching.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ukj9brpcfgen2d_at_corp.supernews.com...
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D499E3A.28B1BA09_at_exesolutions.com...
> > Simon Picken wrote:
> >
> > > Hi,
> > >
> > > Is there a way of detecting the existance of a table in Oracle and
> conditionally
> > > dropping it? In Transact SQL I can do the following:
> > >
> > > IF EXISTS (
> > > SELECT * FROM sysobjects WHERE id = object_id('<table name>')
> > > AND sysstat & 0xf = 3
> > > )
> > > DROP TABLE <table name>
> > > GO
> > >
> > > Is there an Oracle equivalent?
> > >
> > > Regards,
> > > Simon.
> >
> > There is.
> >
> > SELECT COUNT(*) INTO x FROM all_tables WHERE table_name =
'<table_name'>;
> > IF x > 0 THEN
> > EXECUTE IMMEDIATE 'drop table ' || <table_name>;
> > END IF;
> >
> > Though it would be better to use bind variables which I am not
> demonstrating here as
> > it might just confuse you. Still it is something you should look up and
> use rather
> > than doing it as above.
> >
> > But I am really worried about two things (A) that you come from a TSQL
> background
> > and that (B) you are trying to do DDL in a procedure. If you are doing
> what I
> > suspect ... which is creating, altering, dropping objects in code STOP
IT!
> >
> > That is a TSQL perculiarity that can be done in Oracle but is rarely
done
> and hardly
> > ever justifiably.
> >
> > For example, I've worked in Oracle for more than 12 years and have yet
to
> need a
> > temp table for anything. And have never had a need to consider or write
> code such as
> > you are contemplating. So if what you are doing is trying to recreate
SQL
> Server in
> > Oracle please do yourself a huge favor and learn how to do things
Oracle's
> way in
> > Oracle so you don't just build a big slow, unscalable, mess.
> >
> > Daniel Morgan
> >
>
> Next time : you provide the solution and I do the ranting, agreed ;)
>
> Regards
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
Received on Thu Aug 01 2002 - 16:34:50 CDT