Oracle: If Table Exists

SqlOracleSql Drop

Sql Problem Overview

I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.

Specifically, whenever I want to drop a table in MySQL, I do something like

DROP TABLE IF EXISTS `table_name`;

This way, if the table doesn't exist, the DROP doesn't produce an error, and the script can continue.

Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not

SELECT * FROM dba_tables where table_name = 'table_name';

but the syntax for tying that together with a DROP is escaping me.

Sql Solutions

Solution 1 - Sql

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;

Solution 2 - Sql

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')

Solution 3 - Sql

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

Solution 4 - Sql

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;

Solution 5 - Sql

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][1]

[1]: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm "WHENEVER SQLERROR Docs"

Solution 6 - Sql

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;

Solution 7 - Sql

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;

Solution 8 - Sql

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

Solution 9 - Sql

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

Solution 10 - Sql

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.

Solution 11 - Sql

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;

Solution 12 - Sql

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;

Solution 13 - Sql

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

Solution 14 - Sql

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

Solution 15 - Sql

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

Categories
Recommended Sql Solutions
Recommended Oracle Solutions
Recommended Sql Drop Solutions

Previous Solution:

How do you create a yes/no boolean field in SQL server?

Sql ServerBooleanSqldatatypes

Next Solution:

Determine if Python is running inside virtualenv

PythonVirtualenv

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAlan StormView Question on Stackoverflow
Solution 1 - SqlJeffrey KempView Answer on Stackoverflow
Solution 2 - SqlMarius BurzView Answer on Stackoverflow
Solution 3 - SqlRobert VaboView Answer on Stackoverflow
Solution 4 - SqlmishkinView Answer on Stackoverflow
Solution 5 - SqltrunkcView Answer on Stackoverflow
Solution 6 - SqlPavel SView Answer on Stackoverflow
Solution 7 - SqlLeigh RiffelView Answer on Stackoverflow
Solution 8 - SqlLukasz SzozdaView Answer on Stackoverflow
Solution 9 - SqlErichView Answer on Stackoverflow
Solution 10 - SqlAndrei NossovView Answer on Stackoverflow
Solution 11 - Sqluser9213118View Answer on Stackoverflow
Solution 12 - SqlTomView Answer on Stackoverflow
Solution 13 - SqlKhbView Answer on Stackoverflow
Solution 14 - SqlgranadaCoderView Answer on Stackoverflow
Solution 15 - SqlMahesh PandeyaView Answer on Stackoverflow