15 november 2023

If Exists
In the world of CI/CD, it is always a struggle to get the database scripts written in such a way that they can be run over and over again, without running into an error like ‘Object doesn’t exist’ or ‘Object already exists’. You can, of course, resort to homebrew solutions that absorb errors when they might occur, but this unnecessarily bloats your code.

I used to employ a script like this (with more object types, but you get the idea):

  table_or_view_does_not_exist exception;
  pragma exception_init( table_or_view_does_not_exist, -00942 );
  sequence_does_not_exist      exception;
  pragma exception_init( sequence_does_not_exist, -02289 );
  execute immediate q'[drop table mydrivers purge]';
  when table_or_view_does_not_exist
    or sequence_does_not_exist then null; -- on purpose, hide any error

I could run this script repeatedly and I would always get the same result:

PL/SQL procedure successfully completed

In Oracle Database 23c, this code can be replaced by a single statement:

drop table if exists mydrivers purge

Table dropped

Just like the anonymous block, it will not tell you if the object existed in the first place, but in my opinion, that is not important because the end result is the same - the object doesn’t exist (anymore).

If Not Exists
The more complex counterpart is creating objects. If you try to create an object that already exists you will encounter an error, and your script will halt (most of the time). To prevent this from happening, you can use the ‘if not exists’ construct.
create table if not exists mydrivers

  driverid      number generated by default on null as identity,
  driver_number number  (  2 ),
  code          varchar2(  3 ),
  driver_name   varchar2( 32 )

This statement creates the table if it doesn’t exist. If it already exists, nothing is done. This may be a bit counterintuitive, but this is how it is implemented. If the table already exists, then the engine just stops.

If you execute this statement after the previous one:

create table if not exists mydrivers
  driverid      number generated by default on null as identity,
  driver_number number  (  2 ),
  code          varchar2(  3 ),
  driver_name   varchar2( 32 ),
  dob           date,
  nationality   varchar2( 16 )

You will not get an error, but the layout of the table is not changed. It is not like ‘create or replace’, it’s designed to help you out with your CI/CD scripts, making it easier to run them repeatedly without having to do a lot of cleaning up if it fails at some point during testing.

The downside of this feature is that if your script completes successfully, the end result may not be what you expected or wanted. Like with any new functionality, test and use carefully. In my work, when investigating or testing a feature, I use this feature extensively, and I actually miss it when I have to run my scripts on an Oracle Database version below 23c.

The source code used in this article can be found at:


Also check my previous blogs:

Kun je de vacature die je zoekt niet vinden?

Maak een Jobalert aan en ontvang een melding per mail
wanneer er nieuwe vacatures zijn!