Oracle: If Table Exists

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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;

Answer 4

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

Answer 5

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

Answer 6

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