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

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

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

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