System versioned tables in Oracle

Question

A few developers approached me and asked for the easiest way to add historization to a certain table containing master data. As someone familiar with system versioned tables in the context of SQL 2011, SQL-Server (as standard feature) and PostgreSQL (as proposal), I was decently sure that Oracle would support that standard feature.

However, after reading and checking the whole day, I cannot find any references to actual SYSTEM VERSIONED tables. For reference, here's what the Wikipedia says about the standard and Oracles implementation:

...
Definition of system-versioned tables (elsewhere called transaction time tables), using the PERIOD FOR SYSTEM_TIME annotation and WITH SYSTEM VERSIONING modifier. System time periods are maintained automatically. Constraints for system-versioned tables are not required to be temporal and are only enforced on current rows
...
Oracle Oracle 12c supports temporal functionality in compliance with SQL:2011.[9] Versions 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP.[10] Notably both of Oracle's implementations depend on the database transaction log and so only allow temporal queries against recent changes which are still being retained for backup.

However, even following the referenced link I cannot find a single reference that shows how to get Oracle to version the rows for me. All examples reference only manual updates for the validity from and to columns.

I think I could implement something like SCD2 from Data Warehousing, but that's just a development pattern with no reference how the validity is actually updated. I would really like an easy solution for the developers, as doing the updates for the validity manually is error prone (overlapping is a factor) and more work than I think is necessary.

Is there really no system versioning in Oracle for what the SQL 2011 standard describes?

Answer 1

I use Flashback Data Archive for SCD Type 4 and Temporal Validity (plus code for automation) for SCD Type 2.

To automate, I'd use a VIEW on the actual table with an INSTEAD OF trigger to do the actual work. Grant CRUD capability on the VIEW and Read-Only on the actual table.

create sequence my_data_seq;

create table my_data_history (
  surrogate_key int  generated always as identity,
  my_data_id  int default on null my_data_seq.nextval not null,
  some_data   varchar2(10),
  start_date  date not null,
  end_date    date,
  period for current_data (start_date, end_date),
  constraint my_data_history_pk primary key (surrogate_key),
  constraint my_data_history_unique_identifier unique (my_data_id, start_date),
  constraint my_data_history_valid_date_range check ( start_date < end_date )
);

create view my_data
as
select my_data_id, some_data, start_date, end_date
from my_data_history
  as of period for current_data( sysdate );

create or replace trigger my_data_trg
instead of insert or update or delete
on my_data
for each row
begin
  if deleting
  then
    update my_data_history set end_date = sysdate
    where my_data_id = :old.my_data_id and start_date = :old.start_date;
  end if;

  if updating or inserting
  then
    if :old.my_data_id <> :new.my_data_id
    then
      raise_application_error( -20000, 'This method does not support modifying PK' );
    end if;

    if inserting and ( :new.my_data_id is not null or :new.start_date is not null or :new.end_date is not null)
    then
      raise_application_error( -20001, q'[No thank you! We've alrady got one (DATA_ID, START_DATE, END_DATE)]' );
    end if;

    if updating and :new.end_date is not null
    then
      raise_application_error( -20002, q'[Don't set END_DATE. use DELETE instead]' );
    end if;

    if updating and :new.start_date <> :old.start_date
    then
      raise_application_error( -20002, q'[YOU aren't allowed to modify the START_DATE]' );
    end if;




    merge into my_data_history a
    using (
      select my_data_id, some_data, start_date, end_date
      from my_data_history
      where my_data_id = :old.my_data_id and start_date = :old.start_date
      union all
      select :new.my_data_id my_data_id, :new.some_data some_data
         ,sysdate start_date, null end_date
      from dual
    ) b
    on (a.my_data_id = b.my_data_id and a.start_date = b.start_date)
    when matched then update set a.some_data=b.some_data, a.end_date = sysdate
    when not matched then insert (my_data_id, some_data, start_date)
       values ( nvl(b.my_data_id, my_data_seq.nextval), b.some_data, b.start_date);
  end if;
end;

Test Script:

select * from my_data;
insert into my_data some_data (some_data) values ( 'abcdefg' );
select * from my_data;
update my_data set some_data = 'xyz' WHERE my_data_id = 1;
select * from my_data;
delete from my_data where my_data_id = 1;
select * from my_data;

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select * from my_data_history;