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

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com>
Date: 2000/05/09
Message-ID: <39187f5c.31621656@news.alt.net>#1/1

Here's what I've been trying. Though neither works, due to my inexperience. Anyone want to help?

CREATE OR REPLACE FUNCTION Table_Exists (The_Table_Name IN VARCHAR2) RETURN BOOLEAN IS
DECLARE
  The_Table_Exists as Boolean;
BEGIN

• Check if the table exists. SELECT True INTO The_Table_Exists WHERE EXISTS (SELECT Table_Name FROM User_Tables WHERE Table_Name = The_Table_Name);
• Return what we found. RETURN The_Table_Exists; END Table_Exists; /

CREATE OR REPLACE FUNCTION Drop_Table (The_Table_Name IN VARCHAR2) RETURN BOOLEAN IS
BEGIN

• Only drop a table if it doesn't exists. IF Table_Exists(The_Table_Name)THEN DROP TABLE Table_Name;
• If the table still exists, then something is wrong. IF Table_Exists(The_Table_Name)THEN RETURN FALSE;
• If we've come this far, then the table doesn't exists. RETURN TRUE; END Drop_Table;

  ---

SHOW ERRORS FUNCTION Table_Exists;
SHOW ERRORS FUNCTION Drop_Table;

Errors for FUNCTION TABLE_EXISTS:

LINE/COL ERROR

---

---

2/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of

         the following:
         begin function package pragma procedure subtype type use
         <an identifier> <a double-quoted delimited-identifier> cursor
         form current external language

Errors for FUNCTION DROP_TABLE:

LINE/COL ERROR

---

---

4/39 PLS-00103: Encountered the symbol "DROP" when expecting one of

         the following:
         begin declare exit for goto if loop mod null pragma raise
         return select update while <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall
         <a single-quoted SQL string>

Brian

On Tue, 09 May 2000 15:28:01 GMT, emaus081269_at_my-deja.com wrote:

>if you are really looking for an if then else way to handle it, look at
>all_tables where owner = x and table_name = y. If in your own schema
>try user_tables where table_name = x
>
>In article <8f74e1$qnt$1_at_web1.cup.hp.com>,
> "Maggie" <maggiezhou_at_hotmail.com> 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.
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Tue May 09 2000 - 00:00:00 CDT