Oracle: If Table Exists

Alan_Storm_4668 0
Viewed 604740

I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.

Specifically, whenever I want to drop a table in MySQL, I do something like

DROP TABLE IF EXISTS `table_name`;

This way, if the table doesn't exist, the DROP doesn't produce an error, and the script can continue.

Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not

SELECT * FROM dba_tables where table_name = 'table_name';

but the syntax for tying that together with a DROP is escaping me.

sql-drop oracle sql
16 Answers

I prefer following economic solution

BEGIN
    FOR i IN (SELECT NULL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TABLE_NAME') LOOP
            EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
    END LOOP;
END;
user_30717690

The following snippet worked for me

BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE schema_name.table_name';
    EXCEPTION
       WHEN OTHERS THEN
          IF SQLCODE != -942 THEN
             RAISE;
          END IF;
    END;
user_141635320

A block like this could be useful to you.

DECLARE
    table_exist INT;

BEGIN
    SELECT Count(*)
    INTO   table_exist
    FROM   dba_tables
    WHERE  owner = 'SCHEMA_NAME' 
    AND table_name = 'EMPLOYEE_TABLE';

    IF table_exist = 1 THEN
      EXECUTE IMMEDIATE 'drop table EMPLOYEE_TABLE';
    END IF;
END;
Admin1
Related