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: <391895b9.37346406@news.alt.net>#1/1

Hey, thanx for the help. You got me quite a bit further.

Why do I not need to declare?
Why not use "as" when declaring a variable?
I need an "End If" even when only executing one statement?

Here is what I tried. I needed to add "FROM Dual".

>>>

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

Check if the table exists. SELECT TRUE 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; END Table_Exists; /

SHOW ERRORS FUNCTION Table_Exists;
<<<

It spat out:
>>>

Warning: Function created with compilation errors.

Errors for FUNCTION TABLE_EXISTS:

LINE/COL ERROR

5/3      PL/SQL: SQL Statement ignored
5/10     PLS-00382: expression is of wrong type

<<<

It doesn't like SELECT TRUE INTO... I could use a smallint instead:

>>>

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; <<<

For Drop_Table:

>>>

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; END IF;
If the table still exists, then something is wrong. RETURN NOT Table_Exists(The_Table_Name); END Drop_Table; /

SHOW ERRORS FUNCTION Drop_Table;
<<<

Notice I changed the RETURN. I would assume that would work.

It spat out:
>>>

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>

<<<

Brian

On Tue, 09 May 2000 21:28:21 GMT, "Patrick Joyal" <please.reply_at_to.the.newsgroup> wrote:

>Here's your code corrected :
>
>
>CREATE OR REPLACE FUNCTION Table_Exists (The_Table_Name IN VARCHAR2)
>RETURN BOOLEAN IS
>  The_Table_Exists  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;
>/
>
>
>(no need for declare, and don't use "as" to declare a variable)
>
>
>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;
>  end if ;
>
>  -- If the table still exists, then something is wrong.
>  IF Table_Exists(The_Table_Name) THEN
>    RETURN FALSE;
>  end if ;
>
>  -- If we've come this far, then the table doesn't exists.
>  RETURN TRUE;
>END Drop_Table;
>
>
>("if" requires "end if")
>
>
>Try this...
>
>

Received on Tue May 09 2000 - 00:00:00 CDT