Oracle: If Table Exists

J

Jeffrey Kemp

The best and most efficient way is to catch the "table not found" exception: this avoids the overhead of checking if the table exists twice; and doesn't suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

ADDENDUM For reference, here are the equivalent blocks for other object types:

Sequence

BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
END;

View

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Trigger

BEGIN
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4080 THEN
      RAISE;
    END IF;
END;

Index

BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1418 THEN
      RAISE;
    END IF;
END;

Column

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                || ' DROP COLUMN ' || column_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -904 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Database Link

BEGIN
  EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2024 THEN
      RAISE;
    END IF;
END;

Materialized View

BEGIN
  EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -12003 THEN
      RAISE;
    END IF;
END;

Type

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Constraint

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
            || ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2443 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Scheduler Job

BEGIN
  DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -27475 THEN
      RAISE;
    END IF;
END;

User / Schema

BEGIN
  EXECUTE IMMEDIATE 'DROP USER ' || user_name;
  /* you may or may not want to add CASCADE */
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1918 THEN
      RAISE;
    END IF;
END;

Package

BEGIN
  EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Procedure

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Function

BEGIN
  EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Tablespace

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE ' || tablespace_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -959 THEN
      RAISE;
    END IF;
END;

Synonym

BEGIN
  EXECUTE IMMEDIATE 'DROP SYNONYM ' || synonym_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1434 THEN
      RAISE;
    END IF;
END;

And for dropping a USER, the SQLCODE to ignore is -1918.

One needs to write a procedure do do that? Isn't there a better way to do that?

If I add many EXECUTE IMMEDIATE 'DROP TABLE mytable'; sentences (one for each table in the script), do I have to put one exception handler for each one, or is it enough to wrap all the senteces in one BEGIN ... EXCEPTION ... END; block?

@jpmc26: The equivalent for MS SQL is IF OBJECT_ID('TblName') IS NOT NULL DROP TABLE TblName. It seems the verbosity of a SQL language is proportional to the price.

@JeffreyKemp You wouldn't think so, but I've found time and time again that Oracle makes everything hard. When you spend an average of an hour per obscure syntax error or trying to figure out how to do something that's obvious and easy in another database (like conditionally drop an element) and those kinds of problems pop up daily, it adds up. Fast.

M

Marius Burz

declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('table_name');
   if c = 1 then
      execute immediate 'drop table table_name';
   end if;
end;

That's for checking whether a table in the current schema exists. For checking whether a given table already exists in a different schema, you'd have to use all_tables instead of user_tables and add the condition all_tables.owner = upper('schema_name')

+1 This is better because do not relay on exception decoding to understand what to do. Code will be easier to mantain and understand

Agree with @daitangio - performance generally doesn't trump maintainability with run-once deployment scripts.

I would be interested to understand if implicit-commit plays a part here. You would want the SELECT and DROP to be inside the same transaction. [ Obviously ignoring any subsequent DDL that may be executed. ]

@Matthew, the DROP is a DDL command, so it will first issue a COMMIT, drop the table, then issue a 2nd COMMIT. Of course, in this example there is no transaction (since it's only issued a query) so it makes no difference; but if the user had previously issued some DML, it will be implicitly committed before any DDL is executed.

W

Willem Renzema

I have been looking for the same but I ended up writing a procedure to help me out:

CREATE OR REPLACE PROCEDURE DelObject(ObjName varchar2,ObjType varchar2)
IS
 v_counter number := 0;   
begin    
  if ObjType = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(ObjName);
    if v_counter > 0 then          
      execute immediate 'drop table ' || ObjName || ' cascade constraints';        
    end if;   
  end if;
  if ObjType = 'PROCEDURE' then
    select count(*) into v_counter from User_Objects where object_type = 'PROCEDURE' and OBJECT_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP PROCEDURE ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'FUNCTION' then
    select count(*) into v_counter from User_Objects where object_type = 'FUNCTION' and OBJECT_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP FUNCTION ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'TRIGGER' then
    select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP TRIGGER ' || ObjName;
      end if; 
  end if;
  if ObjType = 'VIEW' then
    select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP VIEW ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'SEQUENCE' then
    select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP SEQUENCE ' || ObjName;        
      end if; 
  end if;
end;

Hope this helps

After I created above proc. delobject, I tried to call it issuing the following SQL. But it did not work. delobject('MyTable', 'TABLE'); I am getting the following error -------------------------------- Error starting at line 1 in command: delobject('MyTable', 'TABLE') Error report: Unknown Command

use the EXECUTE Command - EXECUTE DelObject ('MyTable','TABLE');

I like this more than the other solutions, and the fact you do a check on the object first to see it exists defends against SQL Injection. Also I want to check if an object exists after creating as part of unit testing.

m

mishkin

just wanted to post a full code that will create a table and drop it if it already exists using Jeffrey's code (kudos to him, not me!).

BEGIN
    BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE tablename';
    EXCEPTION
         WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                     RAISE;
                END IF;
    END;

    EXECUTE IMMEDIATE 'CREATE TABLE tablename AS SELECT * FROM sourcetable WHERE 1=0';

END;

Personally, I'd put the CREATE TABLE in a separate step, since it doesn't need to be done dynamically and doesn't need an exception handler.

t

trunkc

With SQL*PLUS you can also use the WHENEVER SQLERROR command:

WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;

WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;

With CONTINUE NONE an error is reported, but the script will continue. With EXIT SQL.SQLCODE the script will be terminated in the case of an error.

see also: WHENEVER SQLERROR Docs

P

Pavel S

I prefer following economic solution

BEGIN
    FOR i IN (SELECT NULL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TABLE_NAME') LOOP
            EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
    END LOOP;
END;

L

Leigh Riffel

Another method is to define an exception and then only catch that exception letting all others propagate.

Declare
   eTableDoesNotExist Exception;
   PRAGMA EXCEPTION_INIT(eTableDoesNotExist, -942);
Begin
   EXECUTE IMMEDIATE ('DROP TABLE myschema.mytable');
Exception
   When eTableDoesNotExist Then
      DBMS_Output.Put_Line('Table already does not exist.');
End;

@Sk8erPeter "already does not exist" vs. "did exist, but no longer does" :)

L

Lukasz Szozda

One way is to use DBMS_ASSERT.SQL_OBJECT_NAME :

This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.

DECLARE
    V_OBJECT_NAME VARCHAR2(30);
BEGIN
   BEGIN
        V_OBJECT_NAME  := DBMS_ASSERT.SQL_OBJECT_NAME('tab1');
        EXECUTE IMMEDIATE 'DROP TABLE tab1';

        EXCEPTION WHEN OTHERS THEN NULL;
   END;
END;
/

DBFiddle Demo

But it might not be the name of a table.

There could also be various tables using that name in different schemas.

E

Erich

There is no 'DROP TABLE IF EXISTS' in oracle, you would have to do the select statement.

try this (i'm not up on oracle syntax, so if my variables are ify, please forgive me):

declare @count int
select @count=count(*) from all_tables where table_name='Table_name';
if @count>0
BEGIN
    DROP TABLE tableName;
END

I've made an attempt to translate the script to oracle syntax.

declare count number; begin select count(*) into count from all_tables where table_name = 'x'; if count > 0 then execute immediate 'drop table x'; end if; end; You cannot run DDL directly from a transaction block, you need to use execute.

Thanks very much! I hadn't realized the syntax was that different. I DID know you need to wrap the whole thing in a begin/end, but i figured it was being run in the middle of another script. Tom: I decided to leave my version and not copy yours, so I don't take any votes from you, who obviously has the right ansswer.

I do not think this will compile. It may also be important to include the schema owner here or you may get 'true' for a table you did not mean to get with the same name.

Your answer was superseded by the correct Oracle syntax 10 minutes after this was posted.

A

Andrei Nossov

And if you want to make it re-enterable and minimize drop/create cycles, you could cache the DDL using dbms_metadata.get_ddl and re-create everything using a construct like this: declare v_ddl varchar2(4000); begin select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') into v_ddl from dual; [COMPARE CACHED DDL AND EXECUTE IF NO MATCH] exception when others then if sqlcode = -31603 then [GET AND EXECUTE CACHED DDL] else raise; end if; end; This is just a sample, there should be a loop inside with DDL type, name and owner being variables.

佚

佚名

A block like this could be useful to you.

DECLARE
    table_exist INT;

BEGIN
    SELECT Count(*)
    INTO   table_exist
    FROM   dba_tables
    WHERE  owner = 'SCHEMA_NAME' 
    AND table_name = 'EMPLOYEE_TABLE';

    IF table_exist = 1 THEN
      EXECUTE IMMEDIATE 'drop table EMPLOYEE_TABLE';
    END IF;
END;

T

Tom

Sadly no, there is no such thing as drop if exists, or CREATE IF NOT EXIST

You could write a plsql script to include the logic there.

http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9003.htm

I'm not much into Oracle Syntax, but i think @Erich's script would be something like this.

declare 
cant integer
begin
select into cant count(*) from dba_tables where table_name='Table_name';
if count>0 then
BEGIN
    DROP TABLE tableName;
END IF;
END;

declare cant integer; tablename varchar2(100) := 'BLABLABL'; begin select count(*) into cant from dba_tables where lower(table_name) = tablename; if cant > 0 then execute immediate 'DROP TABLE tablename'; END IF; end;

K

Khb

You could always catch the error yourself.

begin
execute immediate 'drop table mytable';
exception when others then null;
end;

It is considered bad practice to overuse this, similar to empty catch()'es in other languages.

Regards K

No, never "exception when others then null"

g

granadaCoder

I prefer to specify the table and the schema owner.

Watch out for case sensitivity as well. (see "upper" clause below).

I threw a couple of different objects in to show that is can be used in places besides TABLEs.

.............

declare
   v_counter int;
begin
 select count(*) into v_counter from dba_users where upper(username)=upper('UserSchema01');
   if v_counter > 0 then
      execute immediate 'DROP USER UserSchema01 CASCADE';
   end if; 
end;
/



CREATE USER UserSchema01 IDENTIFIED BY pa$$word
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

grant create session to UserSchema01;

And a TABLE example:

declare
   v_counter int;
begin
 select count(*) into v_counter from all_tables where upper(TABLE_NAME)=upper('ORDERS') and upper(OWNER)=upper('UserSchema01');
   if v_counter > 0 then
      execute immediate 'DROP TABLE UserSchema01.ORDERS';
   end if; 
end;
/

M

Moinuddin Quadri

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE "IMS"."MAX" ';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
          END IF;
         EXECUTE IMMEDIATE ' 
  CREATE TABLE "IMS"."MAX" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "NAME" VARCHAR2(20 BYTE), 
     CONSTRAINT "MAX_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"  ';


END;

// Doing this code, checks if the table exists and later it creates the table max. this simply works in single compilation

I believe this only creates the table when the error is thrown.

Oracle: If Table Exists

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US