IF [NOT] EXISTS DDL Clause in Oracle Database 23c

Prevent errors during object creation and removal using the IF [NOT] EXISTS DDL clause in Oracle Database 23c.

◉ Setup

The following objects are required by the examples below.

-- Test user.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create user testuser1 identified by testuser1;

grant connect, resource to testuser1;

-- Test objects.

conn testuser1/testuser1@//localhost:1521/freepdb1

create table t1 (id number);

create sequence t1_seq;

create view t1_v as

select * from t1;

create procedure p1 as

begin

  null;

end;

/

◉ The Problem

When creating database change scripts for applications we may not know if an object already exists, which can lead to errors. For example, the following CREATE commands would fail because the objects are already present.

SQL> conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

Connected.

SQL> create user testuser1 identified by testuser1;

create user testuser1 identified by testuser1

            *

ERROR at line 1:

ORA-01920: user name 'TESTUSER1' conflicts with another user or role name

SQL>

SQL> conn testuser1/testuser1@//localhost:1521/freepdb1

Connected.

SQL> create table t1 (id number);

create table t1 (id number)

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL>

SQL> create sequence t1_seq;

create sequence t1_seq

                *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL>

In a similar way, attempting to drop an object that doesn't exist will result in an error.

SQL> drop table t3 purge;

drop table t3 purge

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

◉ The Solution : IF [NOT] EXISTS

Oracle 23c allows us to use the IF [NOT] EXISTS DDL clause to mask errors when we attempt to create objects that already exist, or drop objects that don't exist.

Here is an example with user creation. The TESTUSER1 user already exists, so it is not created, but the command doesn't return an error. When we attempt to drop the TESTUSER3 user, which doesn't exist, it also doesn't give an error.

SQL> conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

Connected.

SQL> create user if not exists testuser1 identified by testuser1;

User created.

SQL>

SQL> drop user if exists testuser3 cascade;

User dropped.

SQL>

We see similar behaviour with object creation.

SQL> conn testuser1/testuser1@//localhost:1521/freepdb1

Connected.

SQL> create table if not exists t1 (id number);

Table created.

SQL>

SQL> create sequence if not exists t1_seq;

Sequence created.

SQL>

SQL> create view if not exists t1_v as

select * from t1;

View created.

SQL>

SQL> create procedure if not exists p1 as

begin

  null;

end;

/

Procedure created.

SQL>

As expected, we can attempt to drop objects which do not exist without errors.

SQL> drop table if exists t3;

Table dropped.

SQL> drop sequence if exists t3_seq;

Sequence dropped.

SQL> drop view if exists t3_v;

View dropped.

SQL> drop procedure if exists p3;

Procedure dropped.

SQL>

We can include a presence check in an ALTER statement, but in the case of adding column, we can't guard against a column already being present.

SQL> alter table if exists t1 add (id number);

alter table if exists t1 add (id number)

                              *

ERROR at line 1:

ORA-01430: column being added already exists in table

SQL>

◉ CREATE OR REPLACE

We can't mix CREATE OR REPLACE and the IF NOT EXISTS clause.

SQL> create or replace view if not exists t1_v as

select * from t1;

create or replace view if not exists t1_v as

                                     *

ERROR at line 1:

ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement

SQL>

SQL> create or replace procedure if not exists p1 as

begin

  null;

end;

/

create or replace procedure if not exists p1 as

                                          *

ERROR at line 1:

ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement

SQL>

◉ Considerations

Here are some things to consider when using this functionality.

- If we are using the output from a script as an audit of the actions processed, this can be misleading. We will get reports of object creations that didn't actually happen.

- During object creation the IF NOT EXISTS clause is a simple presence check based on the object name. It doesn't compare the structure of the object and report if there is a difference.

- There are limitations to the IF NOT EXISTS clause. As demonstrated above, we can't prevent an error if we try to add a column to a table that is already present.

- The output of some older versions of tools is different to what was presented above in 23c SQL*Plus. For example older versions of SQL Developer, SQLcl and SQL Developer Web all return "Table IF created." rather than "Table {table-name} created" when the presence check is added. Upgrade to the latest tools where possible.

Source: oracle-base.com