23ai New feature: How To Use IF [NOT] EXISTS in DDL Statements

A new feature was introduced with 23c, now 23ai, that can help you with object creation or object drop. This feature helps you prevent errors during the DDL. Is this a good thing? I would say it depends on what you are trying to accomplish.

In previous versions when you tried creating an object, and the object already existed, you would receive an error: ORA-00955: name is already used by an existing object. Same was true if you tried dropping a table, and the table did not exist, you would receive an error: ORA-00942: table or view does not exist.

With this new feature, IF EXISTS and IF NOT EXISTS, Oracle masks the error, if one were to be returned. Let’s say you want to create a table, and the table already exists, Oracle will not create the table, and it will not return an error either. If you want to drop a table, and the table doesn’t exist, Oracle will not drop the table, and it will not return an error. Instead you will receive a message that the operation completed successfully: “Table created” or “Table dropped“.

Let’s see some examples to better understand this feature:

Using IF NOT EXISTS with CREATE DDL Statements

You would use the IF NOT EXISTS option when you are trying to create an object, and you don’t know if the object exists or not. With this clause, you are telling Oracle to create the object if the object doesn’t exist, otherwise supress the error. Have a look at the example below. First I create the table TEST1,and second time I submit the same table creation with the IF NOT EXISTS clause. In previous versions, the second create, would have returned an error. In 23ai, the second statement completes successfully, Oracle supresses the error, and it is not creating any table. You are still receiving a success message “Table created”, even thou nothing was created.

SQL> create table test1
(id number,
 description varchar2(30));
 
Table created.
 
SQL> create table IF NOT EXISTS test1 
(id number,
 description varchar2(30));
 
Table created.

Using IF EXISTS with DROP and ALTER DDL Statements

You would use the IF EXISTS option when you are trying to drop or alter an object, and you don’t know if the object exists or not. With this expression, you are telling Oracle to drop or alter the object if the object exist. Remember, that when using IF EXISTS with the alter statement, Oracle only checks for the existence of the object you are altering, ie only checks if the table exists. In case you want to add a column to a table and the column already exists, Oracle will return an error, as you cannot check for the existence of a column with this clause.

SQL> alter table IF EXISTS test1 add (current_time date);
 
Table altered.
 
SQL> drop table test1;
 
Table dropped.
 
SQL> drop table IF EXISTS test1; 
 
Table dropped.

As you can see from the examples above, Oracle will not return an error on creation or removal of the object.

What Type of Objects Are Supported For This Clause?

Many object types are supported for the IF EXISTS and IF NOT EXISTS clause, here I am listing a few. You can check Oracle’s documentation on all the object types that support this clause.

• database links
• directory
• function
• index
• view
• table
• package
• trigger
• tablespace
• user
• and many more…

Limitations and Consideration for IF [NOT] EXISTS Clause

As with anything, there are some limitations for this new clause.

1) Some of the DDL statements that are using the supported objects, will not allow the IF EXISTS clause, such as altering a table to move it to a different tablespace. An error will be raised, and you will need to use the original syntax to move the table.

2) You cannot use CREATE or REPLACE clause together with the IF NOT EXISTS clause. The two are incompatible, and you will receive an error. You can either use: create or replace view test1_view … or create view if not exists test1_view … ;

3) You don’t know if the object you are trying to create or drop existed prior to the DDL operation. The output you get from running the command is a success message, whether the command was executed or not in the background. You can see that in the examples above that I gave you. At the end, you will receive a success message, ie. “Table dropped.”, even thou no table was dropped. This opens up another issue. If you are using the output from your scripts for audit purposes, then the output can be misleading. You could get false positives for object creations, if the object already existed. I would recommend in these situations do not use the IF [NOT] EXISTS clause.

4) When you try to create an object and you use the IF NOT EXISTS clause, Oracle will not compare the structure of the object you are trying to create with the structure of the existing object. Oracle will not report on differences between the two objects either.

Overall if you use this new clause with the proper goal and purpose in mind, I think it is a great new feature! Let me sum it up for you below with this image!