How to use temporary validity in Oracle 12c?

Question

How to create a table in Oracle 11g so that later it is compatible with the new "Temporary Validity" feature in Oracle 12c?

The Oracle 12c online documentation shows how to determine temporal validity in a SQL language guide ( http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#CJADHJHB )

ALTER TABLE my_table ADD (PERIOD FOR my_valid_time (my_valid_start, my_valid_end) ); 

So, you can use the good old columns valid_from and valid_till already in 11g and bring them to the corresponding periods in 12c, right?

I have inherited databases that use fixed magic dates for "as always" and "forever," for example DATE '1900-01-01' and DATE '3999-12-31' . Apparently 12c uses NULL instead.

So, we need to abandon the use of fixed magic dates and switch to NULL dates?

Answer 1

Yes, you can add an ALTER table in 12c to enable Temporal Validity (see ALTER TABLE in the docs: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_3001.htm#CJAEGCFI )

It works by converting the where clause to the "<=" and "> or null" clause, so you don't need to change fixed dates if you don't want to.

Tom Kit has just posted this on his blog today, with some excellent examples: http://tkyte.blogspot.com/2013/07/12c-flashforward-flashback-or-see-it-as.html