DROP TABLE IF EXISTS - MySQL to Oracle Migration

In MySQL you can use IF EXISTS clause in the DROP TABLE statement. This is mostly uses to suppress error messages in the database schema creation scripts when they are executed for the first time.

MySQL:

  -- The statement always returns success
  DROP TABLE IF EXISTS sales;

Oracle does not provide IF EXISTS clause in the DROP TABLE statement, but you can use a PL/SQL block to implement this functionality and prevent from errors then the table does not exist.

Query Catalog Views

You can query catalogs views (ALL_TABLES or USER_TABLE i.e) to check if the required table exists:

Oracle:

  DECLARE cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'SALES';
    IF cnt <> 0 THEN
      EXECUTE IMMEDIATE 'DROP TABLE sales';
    END IF;
  END;
  /

Using Exceptions

Another way is to run the DROP TABLE statement and suppress the errors in the EXCEPTION block:

  BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE sales';
  EXCEPTION
      WHEN OTHERS THEN NULL;
  END;
  /

This approach works faster and requires less resources from the database.

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.