Re: Conditionally dropping tables in script

"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