Drop Table If Exists in Oracle

In case of drop table statement you will face an exception in oracle, but in mysql there is a if exists in drop statement. so we can follow the following to avoid this in Oracle.

--CREATE TABLE MENNAN.TABLE_EXAMPLE AS SELECT * FROM DUAL;

---SIMPLE WAY, if execute immediate fails, because table not exists, transaction will be committed

BEGIN

  EXECUTE IMMEDIATE 'DROP TABLE MENNAN.TABLE_EXAMPLE';

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE <> -942 THEN

      RAISE;

    END IF;

END;

/

---- NAMED EXCEPTION, the same as SIMPLE WAY

DECLARE

  ve_TableNotExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);

  vs_DynamicDropTableSQL VARCHAR2(1024);

  vs_TableName           VARCHAR2(64);

BEGIN

  vs_TableName           := 'MENNAN.TABLE_EXAMPLE';

  vs_DynamicDropTableSQL := 'DROP TABLE ' || vs_TableName;

  EXECUTE IMMEDIATE vs_DynamicDropTableSQL;

EXCEPTION

  WHEN ve_TableNotExists THEN

    dbms_output.put_line(vs_TableName || ' not exist, skipping....');

  WHEN OTHERS THEN

    dbms_output.put_line(SQLERRM);

    RAISE;

END;

/

---- NAMED EXCEPTION, the same as SIMPLE WAY but more general one

DECLARE

  ve_TableNotExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);

  PROCEDURE DropTable(pis_TableName IN VARCHAR2) IS

    vs_DynamicDropTableSQL VARCHAR2(1024);

  BEGIN

 

    vs_DynamicDropTableSQL := 'DROP TABLE ' || pis_TableName; 

    EXECUTE IMMEDIATE vs_DynamicDropTableSQL;

   

  EXCEPTION

    WHEN ve_TableNotExists THEN

      dbms_output.put_line(pis_TableName || ' not exist, skipping....');

    WHEN OTHERS THEN

      dbms_output.put_line(SQLERRM);

      RAISE;

  END DropTable;

 

 

BEGIN

  DropTable('MENNAN.TABLE_EXAMPLE');

END;

/

---Yet another way, first check from dictionary, if exists drop the table. Checking from dictionary may take time but avoids unnecessary transactions

DECLARE

  vs_TableName VARCHAR2(64);

  vn_Count     PLS_INTEGER;

BEGIN

  vs_TableName := 'MENNAN.TABLE_EXAMPLE';

 

  SELECT COUNT(*)

    INTO vn_Count

    FROM ALL_TABLES

   WHERE OWNER || '.' || TABLE_NAME = vs_TableName;

  IF vn_Count > 0 THEN

    EXECUTE IMMEDIATE 'DROP TABLE ' || vs_TableName;

  END IF;

END;

/