Re: Odp: How to drop an nonexist table without the error message?

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com>
Date: 2000/05/12
Message-ID: <391c0898.563640@news.alt.net>#1/1

DROP TABLE A;
CREATE OR REPLACE TABLE A (A SMALLINT);
CREATE TABLE A (A SMALLINT); produces:
===
Table dropped.

CREATE OR REPLACE TABLE A (A SMALLINT)

                  *

ERROR at line 1:
ORA-00922: missing or invalid option

Table created.
===
With the asterisk under "TABLE".

Brian

On Fri, 12 May 2000 12:04:03 GMT, "AW" <wysza_at_polbox.com> wrote:

>Try to use:
>"create or replace table ....."
>this way you :
>* you don't have to recreate all grants etc. - Oracle remembers it and
>doesn't destroy it
>* if the table doesn't exists Oracle creates it without an error,
>* if the table exists Oracle replaces it without an error
>good luck
>AW
>
>U¿ytkownik Kenneth C Stahl <BlueSax_at_Unforgettable.com> w wiadomooci do grup
>dyskusyjnych napisa³:39180D97.275224E8_at_Unforgettable.com...
>> Maggie wrote:
>> >
>> > When I write create table script, I always put "drop" first, such as,
>> > drop table orders;
>> > create table orders(id number);
>> >
>> > I have some tables and indexes need to drop and recreate everyday.
>> > However, if this is the first time to run the drop and create table, it
>> > always shows the error that "table or view does not exist" first, and
 then
>> > create the table.
>> > How to drop an nonexist table without the error message?
>> >
>> > It really bother me because some scheduling tool will take as the error
 and
>> > stop the whole process. Hence, I would like to add some sort of "IF
 THEN"
>> > statement that bypass the "DROP" if the table has not created yet. I
 know
>> > the MS SQL Server, we run the statement as the following to avoid this
>> > error:
>> > if exists (select * from sysobjects where id =
 object_id(N'[dbo].[Orders]')
>> > and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> > drop table [dbo].[Orders]
>> > GO
>> >
>> > Would you please tell me how it works in the ORACEL. Hope Oracle got
 this
>> > piece of beauty as well as MS SQL Server. Please help.
>>
>> Like this:
>>
>> set feedback off;
>> set termout off;
>> whenever sqlerror continue;
>>
>> drop table mytable;
>>
>> whenever sqlerror exit failure;
>> create table mytable(dummy varchar2(1));
>> exit success;
>
>
Received on Fri May 12 2000 - 00:00:00 CDT