How to Use IF NOT EXISTS in Oracle DDL

Installation and migration scripts should be safe to run more than once. Without help, a second run fails as soon as it tries to create a table that exists or drop one that does not, and scripts end up wrapping DDL in PL/SQL that checks the data dictionary first. Oracle AI Database 26ai adds IF NOT EXISTS to CREATE and IF EXISTS to DROP, so the statements themselves do nothing when there is nothing to do.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create {table | view | sequence | index | ...} if not exists name ...
drop   {table | view | sequence | index | ...} if exists name ...

The clauses work for most object types.

Create and Drop Safely

The example runs CREATE TABLE IF NOT EXISTS for a table that exists, DROP TABLE IF EXISTS for one that does not, and finally a plain CREATE TABLE for the existing table.

Example:

create table if not exists fare_rules (rule_id number);

drop table if exists no_such_table;

create table fare_rules (rule_id number);

Output:

Table FARE_RULES created.

Table NO_SUCH_TABLE dropped.

Error starting at line : 5
In command -
create table fare_rules (rule_id number)
Error report -
ORA-00955: name is already used by an existing object

SQLcl prints its usual messages for the first two statements, although nothing was created or dropped. Only the plain CREATE TABLE fails, with ORA-00955.

Other Object Types

Example:

create sequence if not exists rerun_seq;
create sequence if not exists rerun_seq;            -- second run: nothing happens, no error
create view if not exists rerun_v as select 1 as x from dual;
select count(*) as objects from user_objects where object_name in ('RERUN_SEQ', 'RERUN_V');

Output:

Sequence RERUN_SEQ created.

Sequence RERUN_SEQ created.

View RERUN_V created.

   OBJECTS
__________
         2

Running CREATE SEQUENCE IF NOT EXISTS twice creates one sequence. Drop the objects afterward with drop view if exists rerun_v and drop sequence if exists rerun_seq.

Not Together with OR REPLACE

OR REPLACE and IF NOT EXISTS contradict each other, one replacing and the other keeping an existing object, so they cannot be combined:

Example:

create or replace view if not exists rerun_v2 as select 1 as x from dual;

Output:

Error starting at line : 1
In command -
create or replace view if not exists rerun_v2 as select 1 as x from dual
Error report -
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement

Things to Know

• IF NOT EXISTS checks only the name: if the existing table has different columns, it is still kept as it is, so use it for objects whose definition does not change between runs.
• For objects that should always match the script, such as views and PL/SQL, use CREATE OR REPLACE.
• SQLcl's message after an IF NOT EXISTS statement does not tell whether the object was actually created; query the data dictionary when it matters.

Related Guides

• How to Create Identity Columns in Oracle

Conclusion

CREATE ... IF NOT EXISTS and DROP ... IF EXISTS make DDL scripts rerunnable without PL/SQL checks. Use them for tables and other objects that keep their definition, CREATE OR REPLACE for views and code, and never both in one statement.