Idempotent Script Execution: The Introduction of IF [NOT] EXISTS in Oracle Database

One of the new Oracle Database 23c features that I was directly involved in is the IF [NOT] EXISTS clause. Funnily enough, what I thought was a small simplification feature that will not get much attention and that other databases have had for a long time, turned out to be a lot more confusing to some users than I had anticipated. So, to set the record straight, here is the “insider” story of this feature.

The backstory of IF [NOT] EXISTS started during the COVID-19 time alongside with the other DevSimple features that made Oracle Database 23c the “Developer Release” (before AI came swooping in fast and furious). The feature is not complicated and not revolutionary. As a matter of fact, Postgres, MySQL and SQL Server already offered it long before.

I still have my original analysis of what other databases back then had and what Oracle should do:

Why IF EXISTS?

Having written many tests scripts myself, I didn’t think having to explain IF EXISTS much. The idea is to modify the DROP and ALTER commands to allow for an idempotent execution of these commands. Idempotence is the property of certain operations whereby they can be applied multiple times without changing the result beyond the initial application. You see, this makes a lot of sense when you start thinking about test scripts. Tests should be idempotent. When you write a test you usually want to be able to run it repeatedly and have no side effects. Hence, you clean up all objects first that are being created by your test so that any previous unsuccessful run does not interfere with your current run. These leftover objects are neither important for the current run, nor should they be reused as they would be in some unknown state. So, you want to make sure that your test always starts from scratch, and you usually do that by preemptively dropping all the database objects that the tests will create. But what if the previous run was successful and did clean up after itself? Or if it is the very first test run. A simple DROP TABLE foo; will produce an error:

SQL> DROP TABLE foo;

Error starting at line : 1 in command -
DROP TABLE foo
Error report -
ORA-00942: table or view "GERALD"."FOO" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

This error, in turn, can cause your test to fail with a false positive. Yes, your test encountered an error, but the error is not part of the actual test. Unfortunately, you can’t remove the DROP TABLE command because you need to account for having table foo left over from an unsuccessful run, so what do you do? The savvy Oracle user would say “just use PL/SQL, catch the error and if it is error -942, then ignore it, i.e.:

DECLARE
  e_table_missing EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_table_missing, -942);
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE foo';
EXCEPTION
  WHEN e_table_missing THEN
    NULL;
END;
/

But let’s face it, that’s a) a lot of code to write for a simple “I don’t care whether the table exists or not, just get rid of it if it does” and b) is easy for someone who knows PL/SQL but not so easy for someone who doesn’t know about anonymous PL/SQL blocks, dynamic SQL execution, exception declaration and handling, and the NULL; command to ignore the exception block. All in all, it’s an unnecessary cognitive load and bloat for a developer who doesn’t care about the object at that point.

So, in comes IF EXISTS, which is a modifier to the DROP/ALTER command to essentially just drop or alter the object if exists. It is just two more simple key words, it is self-explanatory and it is already present in other databases.

But the key point here is, the command returns successful either way, because you want the idempotence, because you want the test to never produce a false positive for this statement. Even more so, because a lot of test suites still do a simple diff between the output of the test run and a “good log file”, you want the command to return exactly the same regardless of whether the object was there or not. And that is IF EXISTS:

SQL> DROP TABLE IF EXISTS foo;

Table FOO dropped.

SQL> DROP TABLE IF EXISTS foo;

Table FOO dropped.

SQL> CREATE TABLE foo (id int);

Table FOO created.

SQL> DROP TABLE IF EXISTS foo;

Table FOO dropped.

SQL> DROP TABLE IF EXISTS foo;

Table FOO dropped.

If you look carefully, you will find that the internet has such DROP ... IF EXISTS statements all over the place, for example Hibernate, Hive, Quartz, Oracle’s JSON Duality View example, etc.

Why IF NOT EXISTS?

When we implemented the IF EXISTS feature, we also implemented IF NOT EXISTS for the CREATE command. We did this mostly out of parity to IF EXISTS and because other databases also had it, although other databases didn’t keep parity between object types very much.

When 23c Free – Developer Release went GA and the first blog posts started appearing, I saw that quite a few of them used the CREATE ... IF NOT EXISTS to illustrate the feature instead of the DROP ... IF EXISTS. To some extent, that is only natural because if you do not come out of a testing use case, you wouldn’t think of dropping the object first. However, there is a subtle but big difference between the two. The DROP command will always leave you with the same state, namely, that the object is gone. The CREATE command, however, will leave you with either an object created to your specification, or ignoring your command and leaving the original object. This can be useful if you have a genuine use case where you want to create the object only if it doesn’t already exist. For example, if the sequence is already there, leave it untouched including its current nextval but if the sequence isn’t, well, then create a new one starting with 1. The importance here is that CREATE ... IF NOT EXISTS gives you a non-destructive and idempotent command, while DROP and CREATE do not.

However, you must be careful when using CREATE ... IF NOT EXISTS as you may end up with objects that you think look a certain way because of what you specified but may not because you never created the object. Tables are a good example, let’s say you want to create a new table foo but foo was already there:

SQL> CREATE TABLE IF NOT EXISTS foo
     (
       id   NUMBER NOT NULL PRIMARY KEY,
       text VARCHAR2(255) NOT NULL,
       tms  DATE DEFAULT sysdate NOT NULL
     );

Table FOO created.

This command executed successfully, but you might be surprised running your next command:

SQL> SELECT text, tms FROM foo;

Error starting at line : 1 in command -
SELECT text, tms FROM foo
Error at Command Line : 1 Column : 14
Error report -
SQL Error: ORA-00904: "TMS": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/

Why would it fail saying that tms is an invalid identifier? Well, because the table foo already existed when you ran the CREATE TABLE IF NOT EXISTS command, but it looks entirely different:

SQL> desc foo;

Name    Null?    Type
_______ ________ _______________
VAR              TIMESTAMP(6)

In the end, the database did what you asked it to do, to create a table with a certain shape if the table does not already exist. But because the table did exist, the database skipped the table creation and reported a successful operation.

You run in the same situation with ALTER ... IF EXISTS, so treat the two alike. Most often you want to be informed if your CREATE is conflicting with an already existing object and that your ALTER couldn’t alter a nonexistent object.

When [not] to use IF [NOT] EXISTS?

Over the years I had several debates with users how the database should behave and even arguments that the IF [NOT] EXISTS feature contains bugs due to the behavior. For example, users have argued that CREATE TABLE IF NOT EXISTS should still throw an error when the table is not in the exact shape as specified in the DDL command. Others have said that DROP TABLE IF EXISTS should not print that the table was dropped if it didn’t exist. The list goes on.

In all the cases that were brought to me, it came down to the fact that people didn’t understand when and how to use this feature. The feature is for idempotent command execution, not more and not less. If you think that IF [NOT] EXISTS should do something different in certain cases, it means that you have misunderstood its use case and simply should not add IF [NOT] EXISTS to your DDL. There is no other answer to that. It’s a new optional syntax for a very specific use case and if that use case does not apply to your situation, the feature isn’t for your situation.

When to use IF EXISTS vs IF NOT EXISTS?

This is a simple rule of thumb from me, the guy who wrote up the requirement and analyzed other databases with the same feature:

Start with DROP ... IF EXISTS, that’s what you really want. Only use CREATE ... IF NOT EXISTS and ALTER ... IF EXISTS if you have a real good reason to.

And please, please, please, don’t just plaster CREATE ... IF NOT EXISTS in all your DDL commands. If you do this, you have most certainly not understood what this feature is for and are doing it wrong!