Re: Conditionally dropping tables in script

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 01 Aug 2002 20:05:14 +0200
Message-ID: <lrtikugldv7jod6d0jpfjmrij1hcobmd99@4ax.com>

On 1 Aug 2002 05:20:00 -0700, simonp_at_tesseract.co.uk (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 not, as you don't need it, because Oracle has exception handling. Trying to avoid an exception by extra queries must be considered a waste of resources.
You also can't drop a table directly as in the code above, you need to use EXECUTE IMMEDIATE (8i and higher) or dbms_sql (8.0 and before)

Piece of advice
Try to learn PL/SQL. Forget everything you learned about T-SQL. Don't try to find the PL/SQL equivalent, and *DO READ THOSE ORACLE MANUALS*

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Aug 01 2002 - 13:05:14 CDT