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.
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.
-- 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;
/
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:
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.
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.
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;
/
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;
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 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.
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:
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.
