04-04-2024
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):
declare
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 );
begin
execute immediate q'[drop table mydrivers purge]';
exception
when table_or_view_does_not_exist
or sequence_does_not_exist then null; -- on purpose, hide any error
end;
/
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:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/if%5Bnot%5Dexists.sql
Also check my previous blogs:
Auteur: Patrick Barel
