Forum Stats

  • 3,872,005 Users
  • 2,266,363 Discussions
  • 7,911,025 Comments

Discussions

Add support for IF [ NOT ] EXISTS clauses in DDL statements

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited Sep 8, 2017 9:52AM in Database Ideas - Ideas

A lot of other databases support IF [ NOT ] EXISTS clauses with all DDL statements, which can come in very handy when doing migrations and you're not sure if parts of a migration already ran, previously. For example:

CREATE TABLE IF NOT EXISTS t(a number);

ALTER TABLE t ADD COLUMN IF NOT EXISTS b number;

DROP TABLE IF EXISTS t;

I know this can be done with EXECUTE IMMEDIATE and a PL/SQL exception block


BEGIN

  EXECUTE IMMEDIATE 'CREATE TABLE t(a number)';

EXCEPTION

  WHEN OTHERS THEN NULL;

END;

/

... but the IF [ NOT ] EXISTS form is much more concise

Lukas EderFranck PachotAndreasBuckenhofer
6 votes

Active · Last Updated

Comments