The very useful DROP TABLE IF EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a DROP TABLE statement without getting an error if the table doesn’t exist.

Earlier versions of Oracle don’t support the IF EXISTS clause. Therefore, if we want to avoid any nasty errors resulting from trying to drop a non-existent table, we need to do a bit of extra work.

Below are three options for dropping a table if it exists in Oracle.

Option 1: The DROP TABLE IF EXISTS Statement

Oracle Database 23c introduced the DROP TABLE IF EXISTS syntax. This is the easiest way to drop a table only if it exists:

DROP TABLE IF EXISTS t1;

That statement drops the table called t1 if it exists. If the table doesn’t exist, the statement runs without error.

The above code won’t work if you use Oracle Database 21c or earlier. In this case, you could try one of the alternatives below.

Option 2: Check if the Table Exists

We can check the DBA_TABLES data dictionary view to see if the table exists. This view describes all relational tables in the database. Its columns are the same as those in ALL_TABLES.

We can check this table to see if the table exists, then only run the DROP TABLE statement if it does.

Example:

DECLARE
tbl_count number;
sql_stmt long;

BEGIN
    SELECT COUNT(*) INTO tbl_count 
    FROM dba_tables
    WHERE owner = 'HR'
    AND table_name = 'T1';

    IF(tbl_count <> 0)
        THEN
        sql_stmt:='DROP TABLE T1';
        EXECUTE IMMEDIATE sql_stmt;
    END IF;
END;

Result:

PL/SQL procedure successfully completed.

In this case, the table called t1 already existed and was dropped.

Now, if we run the same code again, we get the same output:

PL/SQL procedure successfully completed.

No error occurred, even though the table no longer exists.

However, if we simply try to drop the table without first checking for its existence, we get an error:

DROP TABLE T1;

Result:

Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

Option 3: Test for the Error

Another way to do it is to simply go ahead and run the DROP TABLE statement, and then catch any ORA-00942 error that occurs. Specifically, we catch any SQLCODE -942 error that occurs.

Example:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE t1';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

Result:

PL/SQL procedure successfully completed.

I ran that even though the T1 table didn’t exist. The ORA-00942 error was caught and handled so we didn’t get an error message.

If the table had already existed, the table would have been dropped, and we’d see the same output.