IF [NOT] EXISTS, and annotations

Two features for the scripts that build your schema: DDL that doesn't fail when the object is already there (or already gone), and annotations, which attach your own labels to tables and columns for tools and people to read.

Part 1: IF NOT EXISTS and IF EXISTS

19c (19.28 and later) and 26ai. This one was added to 19c in a release update, so it works on current 19c too (our tests ran it on 19.33). On an older 19c, use the PL/SQL way below.

Install scripts have the same problem forever: run them twice and CREATE TABLE fails with ORA-00955: name is already used by an existing object, or DROP TABLE fails with ORA-00942: table or view does not exist. Now you can say what you mean:

Setup

drop table if exists cr_umpires purge;

Setup

create table if not exists cr_umpires (
  umpire_id   number(4) primary key,
  umpire_name varchar2(40) not null
);

Setup

create table if not exists cr_umpires (      -- already there: does nothing, no error
  umpire_id   number(4) primary key
);

The test case

After running the three statements above, how many CR_UMPIRES tables are there, and how many columns does it have?

select (select count(*) from user_tables      where table_name = 'CR_UMPIRES') as tables,
       (select count(*) from user_tab_columns where table_name = 'CR_UMPIRES') as columns
from   dual;

Two columns, not one: the second CREATE TABLE IF NOT EXISTS found the table there and did nothing. It doesn't compare definitions or change the table to match. If you need the table changed, that's an ALTER TABLE.

The old way (any 19c)

begin
  execute immediate 'drop table cr_umpires purge';
exception
  when others then
    if sqlcode != -942 then raise; end if;     -- ignore only "table does not exist"
end;
/

Every Oracle install script you've seen has a block like this. Catching only -942 matters: when others then null would also hide a lock timeout or a privilege error.

IF [NOT] EXISTS works for most object types: tables, views, indexes, sequences, procedures, packages, synonyms and more. You can't combine IF NOT EXISTS with OR REPLACE: the two contradict each other ("leave it alone if it's there" versus "replace it if it's there"), and Oracle says so: ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement.

Part 2: annotations

26ai only.

COMMENT ON has given tables and columns one free-text comment each since forever. Annotations are name/value pairs, as many as you like, on tables, columns, views, indexes and more. You invent the names (Sensitive, Owner, Display, Units), and any tool or report can read them from the data dictionary.

Setup

drop table if exists cr_grounds purge;

Setup

create table cr_grounds (
  ground_id    number(4)    annotations (Display 'Ground'),
  ground_name  varchar2(40) annotations (Display 'Name'),
  boundary_m   number(3)    annotations (Display 'Boundary', Units 'metres')
)
annotations (Description 'Cricket grounds and their boundary sizes', Owner 'Kathuko');

The test case

List every annotation on CR_GROUNDS: on the table and on each column.

26ai only

select object_name, column_name, annotation_name, annotation_value
from   user_annotations_usage
where  object_name = 'CR_GROUNDS'
order  by column_name nulls first, annotation_name;

The names come back in capitals (Display became DISPLAY), like unquoted identifiers. The values keep their case. Table-level annotations have no column name.

The 19c way: comments

On 19c you have one comment per table and per column, which is enough for a description but not for name/value pairs. People pack conventions into the text ('Units: metres') and parse them later.

Setup

begin
  execute immediate 'drop table cr_grounds_19c purge';
exception when others then
  if sqlcode != -942 then raise; end if;
end;
/

Setup

create table cr_grounds_19c (ground_id number(4), ground_name varchar2(40), boundary_m number(3));

Setup

comment on column cr_grounds_19c.boundary_m is 'Boundary. Units: metres';

select column_name, comments
from   user_col_comments
where  table_name = 'CR_GROUNDS_19C'
and    comments is not null;

SQL domains (not tested yet)

26ai also has domains: a reusable column type with its own checks, display format and annotations (create domain runs_d as number(1) check (value between 0 and 6)), so twenty tables can share one definition of "runs off a ball". apex.oracle.com doesn't allow CREATE DOMAIN (ORA-01031: insufficient privileges), so this guide will show domains once they're tested on a 26ai database where you have that privilege.

19c and 26ai

Gotchas

• IF NOT EXISTS doesn't check the definition. An old table with the wrong columns stays wrong.
• It's the database version that counts, not the client's. An old SQL*Plus against 19.28 is fine; the newest SQLcl against 19.27 isn't.
• Annotation names are identifiers, so Display and DISPLAY are the same annotation. Quote them ("Display") only if you really need mixed case.