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

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com>
Date: 2000/05/10
Message-ID: <391969da.2579734@news.alt.net>#1/1

On Wed, 10 May 2000 13:33:55 GMT, "Patrick Joyal" <please.reply_at_to.the.newsgroup> wrote:

>
>>
>>1) Why do I not need to declare?
>
>
>I know, all the examples in books use declare but it won't work?
>I'm new to Oracle, so I don't know WHY it don't work, but I know it don't.

OK, I dropped it anyway. I guess I'll just have to play with it. Though having the abel is nice.

>
>>2) Why not use "as" when declaring a variable?
>
>because it's Oracle's syntax.

Yeah, stupid question. I think I left it in because I grabbed the stored procedure from SQL Server, and I didn't realize that was why I had it until after I posted the message.

>
>>3) I need an "End If" even when only executing one statement?
>
>
>Hey, it's not VB! It's not meant to be simple.
But many languages do it. I haven't read a command reference yet, so there's a lot that I don't know. Unfortunately I have to do a few things before I'll have the time to read it.

>
>
>>Here is what I tried. I needed to add "FROM Dual".
>>
>
>or you could have tried this, assuming you change
>The_table_Exists to a numeric variable :
>
>CREATE OR REPLACE FUNCTION Table_Exists (The_Table_Name IN VARCHAR2)
>RETURN BOOLEAN IS
> The_Table_Exists number ;
>BEGIN
> -- Check if the table exists.
> SELECT Count(Table_Name) into the_table_exists
> From User_tables
> Where Table_Name = the_Table_Name ;
>
> -- Return what we found.
> RETURN The_Table_Exists;
>END Table_Exists;
>/

I would make an uneducated guess that EXISTS is faster then COUNT. Technically, COUNT would have to check every occurence, whilst EXISTS need only check the first occurence.

I ended up using:

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

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

And that seems to work. I woul;d still like to know why I can't select "True" into a boolean.

I am still having problems with the delete, however.

It:

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

Only drop a table if it doesn't exist. IF Table_Exists(The_Table_Name) THEN DROP TABLE Table_Name; END IF;
The table should not exist anymore. RETURN NOT Table_Exists(The_Table_Name); END Drop_Table; /

produces:

Warning: Function created with compilation errors.

Errors for FUNCTION DROP_TABLE:

LINE/COL ERROR

6/5 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>

Why won't it except a DROP?

Brian Received on Wed May 10 2000 - 00:00:00 CDT