How can I determine if a table exists in Oracle?

1150550 pts.
Tags:
Oracle
Oracle Database
I've been writing some different migration scripts for an Oracle database and I'm hoping Oracle has something similar to MySQL's "IF EXISTS" construct. Whenever I need to drop a table in MySQL, here's what I use:
DROP TABLE IF EXISTS `table_name`;
So if the table doesn't exist, the DROP doesn't give an error and the script keeps going. Does Oracle have anything like this? Thank you for your help.
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Brijesh
    Hi,

    The efficient way is to catch the "table not found" exception: this avoids the overhead of checking if the table exists twice; and doesn't suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller:

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

    Hope this helps.

    For reference, here are the equivalent blocks for other object types:

    Sequence

    BEGIN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -2289 THEN
          RAISE;
        END IF;
    END;

    View

    BEGIN
      EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
          RAISE;
        END IF;
    END;

    Trigger

    BEGIN
      EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -4080 THEN
          RAISE;
        END IF;
    END;

    Index

    BEGIN
      EXECUTE IMMEDATE 'DROP INDEX ' || index_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -1418 THEN
          RAISE;
        END IF;
    END;

    Column

    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                    || ' DROP COLUMN ' || column_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -904 THEN
          RAISE;
        END IF;
    END;

    Database Link

    BEGIN
      EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -2024 THEN
          RAISE;
        END IF;
    END;

    Materialized View

    BEGIN
      EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -12003 THEN
          RAISE;
        END IF;
    END;

    Type

    BEGIN
      EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN
          RAISE;
        END IF;
    END;

    Constraint

    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                || ' DROP CONSTRAINT ' || constraint_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -2443 THEN
          RAISE;
        END IF;
    END;

    Scheduler Job

    BEGIN
      DBMS_SCHEDULER.drop_job(job_name);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -27475 THEN
          RAISE;
        END IF;
    END;

    User / Schema

    BEGIN
      EXECUTE IMMEDIATE 'DROP USER ' || user_name;
      /* you may or may not want to add CASCADE */
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -1918 THEN
          RAISE;
        END IF;
    END;

    Package

    BEGIN
      EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN
          RAISE;
        END IF;
    END;

    Procedure

    BEGIN
      EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN
          RAISE;
        END IF;
    END;

    Function

    BEGIN
      EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN
          RAISE;
        END IF;
    END;				
    14,450 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: