23ai: Using IF [NOT] EXISTS Clause When Creating or Dropping an Object

 When creating an object, if that object already exists, an “already exists” error will occur. Similarly, if you try to drop an object that does not exist, you’ll encounter a “does not exist” error. To prevent these two errors, you can use the IF [NOT] EXISTS clause.

For example, if a table named TB already exists but you're not sure about it, you can use the IF NOT EXISTS clause in the CREATE TABLE statement to avoid a possible ORA-00955 error:

SQL> create table tb (c1 number(10));
ORA-00955: name is already used by an existing object

SQL> create table if not exists tb (c1 number(10));
Table created

SQL> create table if not exists tb (c1 number(10));
Table created

This clause is also applicable to other DDL statements:

SQL> drop sequence ss;
ORA-02289: sequence does not exist

SQL> drop sequence if exists ss;
Sequence dropped

SQL> create user usef identified by ss;
ORA-01920: user name 'USEF' conflicts with another user or role name

SQL> create user if not exists usef identified by ss;
User created

SQL> create user if not exists usef identified by ss;
User created

SQL>  drop user vahid cascade;
ORA-01918: user 'VAHID' does not exist

SQL> drop user if exists vahid cascade;
User dropped

SQL> drop procedure proc1;
ORA-04043: Object PROC1 does not exist.

SQL> drop procedure if exists proc1;
Procedure dropped

However, note that the IF [NOT] EXISTS clause does not cover all DDL statements:

SQL> alter table if exists tb add (id number);
Table altered

SQL> alter table if exists tb add (id number);
ORA-01430: column being added already exists in table

SQL> create or replace view vw_tb as select * from tb;
View created

SQL> create or replace view if not exists vw_tb as select * from tb;
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement