Oracle Database 23ai introduces a long-awaited improvement for managing database objects gracefully support for the IF [NOT] EXISTS clause in DDL statements. This enhancement simplifies schema management by preventing runtime errors when creating or dropping objects that may or may not already exist. For DBAs and developers building reusable, idempotent deployment scripts, this feature marks a significant milestone in usability. IF [NOT] EXISTS Let’s walk through this enhancement using practical examples, comparing how this clause mitigates object creation and deletion errors that would otherwise disrupt application deployments or schema refresh tasks. Setup: Preparing Test Schema and Objects We begin by creating a dedicated test schema. These operations assume the containerized database is named freepdb1 and are executed as a SYSDBA user. This approach allows testing both privilege-sensitive and schema-level operations. freepdb1 -- Connect as SYS
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

-- Create test user and grant privileges
create user testuser1 identified by testuser1;
grant connect, resource to testuser1; -- Connect as SYS
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

-- Create test user and grant privileges
create user testuser1 identified by testuser1;
grant connect, resource to testuser1; Switching to the new user, we prepare some schema objects for our demonstrations: -- Connect as testuser1
conn testuser1/testuser1@//localhost:1521/freepdb1

-- Create sample objects
create table t1 (id number);
create sequence t1_seq;

create view t1_v as
select * from t1;

create procedure p1 as
begin
  null;
end;
/ -- Connect as testuser1
conn testuser1/testuser1@//localhost:1521/freepdb1

-- Create sample objects
create table t1 (id number);
create sequence t1_seq;

create view t1_v as
select * from t1;

create procedure p1 as
begin
  null;
end;
/ Problem: Traditional DDL Limitations In earlier versions of Oracle, creating or dropping database objects without first checking their existence would often result in runtime errors. This has long been a challenge in writing reusable deployment scripts or CI/CD automation. For instance, trying to re-create the user TESTUSER1 or table T1 without a check, it would trigger errors like: TESTUSER1 T1 create user testuser1 identified by testuser1;
-- ORA-01920: user name 'TESTUSER1' conflicts with another user or role name

create table t1 (id number);
-- ORA-00955: name is already used by an existing object

drop table t3 purge;
-- ORA-00942: table or view does not exist create user testuser1 identified by testuser1;
-- ORA-01920: user name 'TESTUSER1' conflicts with another user or role name

create table t1 (id number);
-- ORA-00955: name is already used by an existing object

drop table t3 purge;
-- ORA-00942: table or view does not exist These failures halt scripts and demand conditional logic, making deployments more fragile and verbose. Solution: IF [NOT] EXISTS With Oracle 23ai, we now have a cleaner, more declarative solution. The IF NOT EXISTS clause allows us to suppress errors when attempting to create existing objects, while IF EXISTS it does the same when dropping objects that may not exist. IF NOT EXISTS IF EXISTS Here’s a simplified user management example: -- Connect as SYS again
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create user if not exists testuser1 identified by testuser1;
-- Output: User created.

drop user if exists testuser3 cascade;
-- Output: User dropped. -- Connect as SYS again
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create user if not exists testuser1 identified by testuser1;
-- Output: User created.

drop user if exists testuser3 cascade;
-- Output: User dropped. Similarly, object creation and deletion within a schema becomes straightforward: -- Back to testuser1
conn testuser1/testuser1@//localhost:1521/freepdb1

create table if not exists t1 (id number);
create sequence if not exists t1_seq;

create view if not exists t1_v as
select * from t1;

create procedure if not exists p1 as
begin
  null;
end;
/ -- Back to testuser1
conn testuser1/testuser1@//localhost:1521/freepdb1

create table if not exists t1 (id number);
create sequence if not exists t1_seq;

create view if not exists t1_v as
select * from t1;

create procedure if not exists p1 as
begin
  null;
end;
/ Dropping non-existent objects no longer requires elaborate checks: drop table if exists t3;
drop sequence if exists t3_seq;
drop view if exists t3_v;
drop procedure if exists p3; drop table if exists t3;
drop sequence if exists t3_seq;
drop view if exists t3_v;
drop procedure if exists p3; All of these execute cleanly, even when the target object isn’t present. CREATE OR REPLACE: A Limitation to Note The CREATE OR REPLACE syntax—commonly used for views, procedures, and functions—cannot be combined with IF NOT EXISTS. Attempts to do so will raise an error: CREATE OR REPLACE IF NOT EXISTS create or replace view if not exists t1_v as
select * from t1;
-- ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement create or replace view if not exists t1_v as
select * from t1;
-- ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement This restriction highlights that IF NOT EXISTS is intended as a simple presence check, not a mechanism for conditionally replacing existing objects. IF NOT EXISTS A Subtle Trap: ALTER Statements Aren’t Fully Protected Although you can use IF EXISTS in ALTER Statements for checking table existence it does not protect against lower-level conflicts, such as trying to add a column that already exists: IF EXISTS ALTER alter table if exists t1 add (id number);
-- ORA-01430: column being added already exists in table alter table if exists t1 add (id number);
-- ORA-01430: column being added already exists in table This is because Oracle only checks the existence of the table, not the internal schema definition of its columns. Considerations When Using IF [NOT] EXISTS This new clause is a game-changer for error-tolerant deployment and cleanup scripts, but it’s not without caveats. When generating audit trails or logs, the output can be misleading. For instance, you’ll receive “User created” or “Table dropped” messages even if the action was skipped due to object presence or absence. Also, note that this clause performs a presence check by object name only; it does not compare structure or compatibility. Therefore, you can’t use it to detect changes or drift in object definitions. Furthermore, different versions of client tools (such as SQLcl, SQL Developer, or SQL Developer Web) might display varying outputs for these operations. Upgrading to the latest versions ensures consistent behavior, especially when scripting across environments.

Aren

Oracle Autonomous Database Auto-Scaling: A DBA's Guide to Elastic Performance

Simplifying Object Creation and Cleanup With IF [NOT] EXISTS in Oracle Database 23ai

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Creating DB Links in Oracle AWS RDS: A Comprehensive Guide

10 Principles of Proper Database Benchmarking

5 Most Common Data Quality Issues For Business

A Brief Introduction to MongoDB

A Comprehensive Guide for Using DuckDB With Go

Creating DB Links in Oracle AWS RDS: A Comprehensive Guide

10 Principles of Proper Database Benchmarking

5 Most Common Data Quality Issues For Business

A Brief Introduction to MongoDB

A Comprehensive Guide for Using DuckDB With Go

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps