IF EXISTS Equivalent

DROP TABLE baniya.emp;

What is one problem you can think of when using a DROP statement inside a SQL script? The drop command drops table or defined database object if exist, if not it will throw an error and the script execution stops. If the DROP table statement is a part of script, it will not execute remaining script as soon as the error is thrown. Has anyone experience this before? I have…. How do you like to handle the error? exit or continue?

Do you want your script execution to stop or continue? In most case, you will want to continue execution of remaining script because the table you want to drop was already dropped by someone or some other scheduler job. Oracle does not provide IF EXIST functions like in MYSQL or SQL Server, therefore we will need to write a PL/SQL to check the existence before dropping it.

SET serveroutput ON 
BEGIN 
    EXECUTE IMMEDIATE 'DROP TABLE baniya.emp_test'; 
    dbms_output.Put_line('Table Dropped Successfully'); 
EXCEPTION 
    WHEN OTHERS THEN 
      dbms_output.Put_line('Table does NOT exist'); 
END; 

This will check if the table exist, if exist drops it, if not it will continue without error. Now, we will re-write the plsql block to drop and re-create a emp_test table.

SET serveroutput ON 
DECLARE 
BEGIN 
    BEGIN 
        EXECUTE IMMEDIATE 'DROP TABLE baniya.emp_test'; 

        dbms_output.Put_line('Table Dropped Successfully'); 
    EXCEPTION 
        WHEN OTHERS THEN 
          dbms_output.Put_line('Table does NOT exist'); 
    END; 
    --continue even if the table doesnot exist. 
    EXECUTE IMMEDIATE 
    'CREATE TABLE baniya.emp_test AS SELECT * from baniya.employee'; 
    dbms_output.Put_line('Table Created Successfully'); 
EXCEPTION 
    WHEN OTHERS THEN 
      dbms_output.Put_line('Unable to create table, Please check!'); 
END; 

/ 

Similarly, the same concepts can be applied before INDEX, VIEWS, MVIEWS, GTT, LTT, db_links and all other database objects. Now, let’s explore how we accomplish this on other popular Database Management Systems.

MySQL:
DROP TABLE IF EXISTS 'employee';
CREATE TABLE 'foo' ( ... );

SQL Server:
IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
DROP TABLE dbo.Scores;

From SQL Server 2016 and up, you can use
DROP TABLE IF EXISTS dbo.Scores

DROP IF EXISTS feature exists in major databases except Oracle. Even Oracle 12c doesn’t have this feature which many Oracle users wanted to see with release 12c. When do you think Oracle will implement this? Do we even need one when you have a work around?