sql – Oracle: If Table Exists

sql – Oracle: If Table Exists

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

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;

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

sql – Oracle: If Table Exists

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