In the last post I talked about a form of syntactic sugar I’m not convinced we really needed. This post on the other hand is about one that I think I’ve needed since the first time I logged on to a SQL-based database.

When you put together a test case it is often setup to create misc objects and populate them. The first time you run it it works fine but when you rerun it your create commands fail with object of that name already exists. So you add drop commands for all the objects.

Now when you move to a new environment those drop-commands will fail the first time you run it because they are not present in the new environment.

It would be nice to be able to set up adhoc test cases so you get no errors in any situation. With 23ai it is possible.

But first, let’s talk a little about if this is how you should deal with production deploy for your solution. I would not, I would use liquibase or even better SQLcl’s features for liquibase. That is a much better control of what should be done incrementally to your database objects.

So before 23ai you may start with something like this:

create table tb_1 ( col1 number(4)    not null);

create table tb_2 ( col1 number(4)    not null
                  , col2 varchar2(25) not null
                  );

< Add data for the test case to both tables >

It works fine but you tweak the data you add a bit to prove the issue and then rerun it. Of course both the create statements fail so you add drop statements to it.

drop   table tb_1;
create table tb_1 ( col1 number(4)    not null);

drop   table tb_2;
create table tb_2 ( col1 number(4)    not null
                  , col2 varchar2(25) not null
                  );

< Add data for the test case to both tables >

While that works now, when you give it to a colleague to run in his or her database, they will get errors from this. So you end up commenting out the drop commands and then you add them in when needed.

To handle this better we can from 23ai opt to have statements only run if the objects exists or not.

We could adjust the first example to have the objects created only if they don’t already exist.

create table if not exists tb_1 ( col1 number(4)    not null);

create table if not exists tb_2 ( col1 number(4)    not null
                                , col2 varchar2(25) not null
                                );

< Add data for the test case to both tables >

This now works if that table does not need to change. If the case is rather to test different variants for how the tables are created you want to drop them if they already exist.

drop   if exists table tb_1;
create table tb_1 ( col1 number(4)    not null);

drop   if exists table tb_2;
create table tb_2 ( col1 number(4)    not null
                  , col2 varchar2(25) not null
                  );

< Add data for the test case to both tables >

Here we drop the table if it exists and avoid getting errors when it doesn’t.

That was create and drop, it also exist for alter. However, there are situations that cannot be managed with this for an alter.

Consider this test case. Create a table with two columns. Modify one column’s datatype and do it again to show that the if exists running again works for it. Now do the same with adding a primary key and do it again.

drop table if exists a;
create table if not exists a (b number, c number);

alter table if exists a modify (c varchar2(10));
alter table if exists a modify (c varchar2(10));

alter table if exists a add constraint a_pk primary key (b);
alter table if exists a add constraint a_pk primary key (b);

Error report -
ORA-02260: en tabell kan bara ha en primärnyckel
02260. 00000 -  "table can have only one primary key"
*Cause:    Self-evident.
*Action:   Remove the extra primary key.

For the primary key it fails. You cannot issue it when it has one. The table already exists so it passes that validation. The same is true for setting a column to “not null”, it results in an error about you setting a column that already is not null to nut null.

Not too surprising when you think about it as the condition as about the existence of the table. You could for an alter have it to only run when the tables to modify does exist.

This is great improvement to the syntax. But is with all features you have to understand what it can and what it cannot do for you. This feature allow you to include this kind of logic for quite a few kinds of object types.

Tak a look at the docs to see more details.