Oracle: If Table Exists

ghz 8months ago ⋅ 81 views

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.

Answers

Yes, Oracle does not have a direct equivalent to MySQL's DROP TABLE IF EXISTS statement. However, you can achieve similar functionality in Oracle using PL/SQL blocks.

Here's an example of how you can drop a table only if it exists in Oracle:

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

Explanation:

  • The EXECUTE IMMEDIATE statement allows you to execute a dynamically constructed SQL statement.
  • If the table doesn't exist, Oracle will raise an error with code -942 (ORA-00942: table or view does not exist).
  • The EXCEPTION block catches the error, and the IF condition checks if the error code is -942.
  • If the error code is not -942, it re-raises the exception.
  • If the error code is -942, it means the table doesn't exist, and the script continues execution without raising an error.

This PL/SQL block ensures that the script continues execution even if the table doesn't exist, similar to the behavior of DROP TABLE IF EXISTS in MySQL.

#oracle