Re: Conditionally dropping tables in script

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 Received on Thu Aug 01 2002 - 15:46:53 CDT