Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.4k points)

To avoid the overhead of checking if the table exists twice or not” and to avoid the exception which is raised to the caller due to the DROP failure for some other reason. The most efficient way is to catch the exception “table not found”.

Here is an informative video for you on Exception Handling in SQL.

Code:

BEGIN

   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;

EXCEPTION

   WHEN OTHERS THEN

      IF SQLCODE != -942 THEN

         RAISE;

      END IF;

END;

For other object types, you can refer to these blocks of code given below:

  • For Sequence:

BEGIN

  EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -2289 THEN

      RAISE;

    END IF;

END;

  • For View:

BEGIN

EXECUTE IMMEDIATE 'DROP VIEW ' || viewName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -942 THEN

      RAISE;

    END IF;

END;

  • For Trigger:

BEGIN

EXECUTE IMMEDIATE 'DROP TRIGGER ' || triggerName;

EXCEPTION

 WHEN OTHERS THEN

  IF SQLCODE != -4080 THEN

   RAISE;

  END IF;

END;

  • For Index:

BEGIN

EXECUTE IMMEDIATE 'DROP INDEX ' || indexName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -1418 THEN

      RAISE;

    END IF;

END;

  • For Column:

BEGIN

  EXECUTE IMMEDIATE 'ALTER TABLE ' || Mytable

                || ' DROP COLUMN ' || columnName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -904 THEN

      RAISE;

    END IF;

END;

  • For Database Link

BEGIN

 EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblinkName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -2024 THEN

      RAISE;

    END IF;

END;

  • For Materialized View:

BEGIN

  EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mviewName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -12003 THEN

      RAISE;

    END IF;

END;

  • For Type:

BEGIN

  EXECUTE IMMEDIATE 'DROP TYPE ' || typeName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -4043 THEN

      RAISE;

    END IF;

END;

  • For Constraint:

BEGIN

  EXECUTE IMMEDIATE 'ALTER TABLE ' || Mytable

            || ' DROP CONSTRAINT ' || constraintName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -2443 THEN

      RAISE;

    END IF;

END;

  • For Scheduler Job

BEGIN

DBMS_SCHEDULER.drop_job(jobName);

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -27475 THEN

      RAISE;

    END IF;

END;

  • For User / Schema:

BEGIN

  EXECUTE IMMEDIATE 'DROP USER ' || userName;

  /* you can add CASCADE */

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -1918 THEN

      RAISE;

    END IF;

END;

  • For Package:

BEGIN

EXECUTE IMMEDIATE 'DROP PACKAGE ' || packageName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -4043 THEN

      RAISE;

    END IF;

END;

  • For Procedure:

BEGIN

EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedureName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -4043 THEN

      RAISE;

    END IF;

END;

  • For Function:

BEGIN

EXECUTE IMMEDIATE 'DROP FUNCTION ' || functionName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -4043 THEN

      RAISE;

    END IF;

END;

For Tablespace:

BEGIN

  EXECUTE IMMEDIATE 'DROP TABLESPACE' || tablespaceName;

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -959 THEN

      RAISE;

    END IF;

END;

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...