Idempotent Oracle SQL migrations

oracle-idempotent-ddl.sql

--
-- create new table
--
declare 
    table_exists number;
begin
    select count(1) into table_exists from USER_TABLES where table_name = 'PRODUCTS';
    
    if (table_exists = 0) then
        execute immediate 'create table PRODUCTS (ID number(19) not null ...';
    end if;
end;

--
-- create new index
--
declare 
    index_exists number;
begin
    select count(1) into index_exists from USER_INDEXES where index_name = 'PRODUCTS_BK';
    
    if (index_exists = 0) then
        execute immediate 'create unique index PRODUCTS_BK ...';
    end if;
end;

--
-- create new constraint
--
declare
  constraint_exists number;
begin
  select count(1) into constraint_exists from USER_CONSTRAINTS where table_name = 'PRODUCTS' and constraint_name = 'PRODUCTS_FK_COMPANIES';

  if (constraint_exists = 0) then
    execute immediate 'alter table PRODUCTS add constraint PRODUCTS_FK_COMPANIES foreign key (COMPANY_ID) references COMPANIES (ID)';
  end if;
end;

--
-- add column to existing table
--
declare 
    col_exists number;
begin
    select count(1) into col_exists from USER_TAB_COLS where table_name = 'PRODUCTS' and column_name = 'SURCHARGE';
    
    if (col_exists = 0) then
        execute immediate 'alter table PRODUCTS add (SURCHARGE number(12, 3))';
    end if;
end;

--
-- drop column from existing table
--
declare 
    col_exists number;
begin
    select count(1) into col_exists from USER_TAB_COLS where table_name = 'PRODUCTS' and column_name = 'SURCHARGE';
    
    if (col_exists > 0) then
        execute immediate 'alter table PRODUCTS drop column SURCHARGE';
    end if;
end;