Oracle Database 23c - Thank you for the "if [not] exists" clause

People who know me for a longer time do know, that I started my "Oracle" career as Developer and , mainly with PL/SQL and Oracle Forms. One of the main things I was doing together with a colleague was to set up data models.
These Data Models were created for all kind of applications and customers. Part of the job was, to create all the scripts to set up the Oracle database schemas.
Later, as the application evoluted to new versions, we needed to change the data model from time to time. Adding tables, changing columns, dropping objects, deleting data, ...

For sure, you can use edition-based redefinition, or you can compare the databases between development and production and (let) create scripts with only the differences in it.
But the world is more complicated. The application is installed a couple of times with different versions at the customers. Due to fixes, patches, releases the data model does not look the same for all customers.
The workload to create hundreds of different scripts for any type of difference is uncredible. The solution was - there is one common script that can be used from one release to the next release and all changes are added.
The script tried to create tables or indexes that may already existed or tried to drop tables, which didn't exist anymore, as they have been dropped previously by a patch.
Thanks to that, the log file was full of ORA- errors with "... does already exist" etc. errors. For the installation team it was horrible to control the logs. Was anything added/changed/dropped as intended?
Is an "object does not exist" error fine (as it tried to drop an already dropped object) or is it a real error (because it tried to add a column on a table that does not exist)?
Scripts has been created to replace ORA- errors with comments "NOORAERR-" to allow to skip the "this thing is fine" errors.

Thank God, Oracle now has changed this for the new Oracle 23c database. They added a "if [not] exists" clause for DDL. This can help to avoid so many hours of work for all the checks mentioned before.

Let's do some tests on the already available 23c Developer Edition VM.

Test 1: Create a user.

First, we create the user and retry to create it again using the old and the new syntax. Also, we try to create the (already existing) user with another password as before and try to connect to the database.

• create user my_user identified by <*>;  
• create user my_user identified by <*>;
• create user my_user if not exists identified by <*>;
• create user my_user if not exists identified by <*>;

 

As we can see, the user is created with the first, the classic statement. With the new syntax we don't get an error message, if we try to create the user. That the whole statement is discarded we can see at the identified by clause followed by a try to connect.

 The "new" password is not set, so nothing is done at all and the message "user created" is thrown. This is, what I have expected, but be aware, if you need to use the new password for this user, it must be changed in addition (as alter user) in your scripts. 

Test 2: Drop a user.

For our second test, let's try to drop a user. First we drop it using the old syntax and then we try to drop it again with the old and the new syntax.

• drop user my_user1;
• drop user my_user1;
  
• drop user if exists my_user1;

 

Nothing special here, works as expected.

Test 3: Create a table.

For a next tests I use the user created at the first test scenario and try to create and modify a table in that schema (I have granted some rights already, so the user is allowed to).

Let's go:

• alter session set current_schema=my_user;
• create table my_sales (txt varchar2(20));
• create table if not exists my_sales (txt varchar2(20));

Ok, now we try to create the table again (with different columns as before).

• create table my_sales (txt varchar2(40), txt1 varchar2(10));
• create table if not exists my_sales (txt varchar2(40), txt1 varchar2(10));

As expected, we do get an error message with the old syntax, but not with the new one. If we do a describe on the table, we see it is still defined like it was originally created.

 Test 4: Modify a table (add column).

 Now we can try to alter the table - first we will add the new column varchar2(10).
Which of the following statements will run and which of them will do the right thing?
Which one will throw an error if it runs (twice)? 

Hint: We will start with the original table (see describe in Test 3) for every test.

• Statement 1: alter table my_sales add txt1 varchar2(10);
• Statement 2: alter table my_sales add if not exists txt1 varchar2(10);
• Statement 3: alter table if exists my_sales add  txt1 varchar2(10);
• Statement 4: alter table if exists my_sales add if not exists txt1 varchar2(10);
• Statement 5: alter table if exists my_sales_history add txt1 varchar2(10);

The test starts with Statement 1 - I expect, it will work the first time, the second time it throws an error.

Well, works as it did since I can think of. No wonder - this syntax is used since the first versions of Oracle Databases.

Statement 2 is the one with a not exists for the column. Will it work (twice)? What do you expect?

Unfortunately, this statement throws an error. I have checked therefore the "column clause" at the 23c documentation. And yes, there isn't a "if (not) exists" clause for this part of the statement.

With this in our minds we do know, that Statement 4 will also not run. But what about Statement 3?

Yes, this works - this is an allowed statement. But if you run it two times, you will get an error as the column does already exists.

Statement 5 (using a non-existing table name) should also work without throwing an error.

 Test 5: Modify a table (modify/drop/rename column).

What about modifying/renaming/dropping a column? There is the same restriction as adding, this means there is not a "if (not) exists" syntax at column level. This means, we still get some error at this level.

We only can check, if the table does (not) exists. Allowed syntax is:

• alter table if exists my_sales modify column txt varchar2(40);

not allowed syntax is

• alter table my_sales modify if exists txt varchar2(40);
• alter table my_sales modify txt varchar2(40);

Same with drop column:

• alter table my_sales drop column if exists txt;
• alter table my_sales drop column txt;

What you do think about this behaviour? On the one hand, there can be columns named "if", so this maybe influenced Oracle to not use the clause in an alter table statement for the columns.

On the other hand, if Oracle checks the whole syntax, they could figure out, if the column is named IF or if the IF is followed by a (NOT) EXISTS statement.

Without showing you any tests - if you want to add/change/drop (foreign/unique key) constraints to a table (column), there is also no if (not) exists clause. Now we leave tables and check for other objects, eg. with

Test 6: Create views (procedures/packages/...).

What about following syntax?

• create view if not exists my_view as select upper(txt) uptxt from my_sales;
• create view my_view as select upper(txt) uptxt from my_sales;

By the way - create or replace (views/procedures/...) combined with "if exists" does not make any sense - and the database is telling you that.

Conclusion:

For a lot of statements the new if (not) exists syntax works very well. Unfortunately, it does not work for adding, renaming or modifying table columns, constraints, etc.. There are still some open (weak points) in my opinion therefore.

As the statement is executed only if the if (not) exists succeeds, it can also happen that user or table does not have the status you think it should have, as it had existed before.

It's still the users/developers responsibility to write bullet proof scripts for migrations. We can't get rid of all "unnecessary" errors, but at least there is a bigger amount of error messages not showing up in the installation logs, using the new syntax.

At twitter, a discussion was started by people who said, it would be nice to get a different message for different states, e.g. if a create table if not exists statement should not throw "table created", but a "statement skipped, table exists" message (without ORA- error) to see the difference.

What's your opinion on that (please write a comment)?