You will really appreciate this feature if you have called some of the cable companies to terminate your cable connection.   This is the response you generally get to hear ” Please call us on so and so date; I cannot update the system now”. Hopefully some of those companies can use these feature now.

Temporal Validity  associates one or more valid time dimensions to your data making it visible or invisible  depending on the time as set by the user. If adopted , may be with this feature, you can change your address or phone number months in advance, don’t have to wait for the last minute.

So how do you implement it?

PERIOD FOR clause of  CREATE/ALTER table command is used to implement temporal validity.

The syntax for PERIOD FOR clause is as below.

PERIOD FOR dimension_name [( start_time_column , end_time_column)]

You can explicitly  specify  columns “start_time_column”  & “end_time_column”  and when specified  they must be existing columns in the table.

If  “start_time_column”  & “end_time_column”   is skipped , Oracle will create  two invisible columns  dimension_name_start & dimension_name_end for time dimensions. The invisible columns cane be used in INSERT  & UPDATE statement like like any defined table columns.

See examples below for creating tables

In the 1st example below, I am creating a time dimension using columns of the table itself. Two columns SERVICE_START and SERVICE_END  specifies the time dimension for the row.

SQL> CREATE TABLE CUSTOMER_1
2  (
3  CUST_NO    NUMBER(12) PRIMARY KEY,
4  CUST_NAME  VARCHAR2(20) NOT NULL,
5  CUST_ADDR  VARCHAR2(40),
6  SERVICE_START      TIMESTAMP,
7  SERVICE_END        TIMESTAMP,
8  PERIOD FOR CUST_HIST_TIME(SERVICE_START, SERVICE_END)
9  );

Table created.

In the 2nd example below, I am creating the  table  without specifying the time dimensions for the data. In such case Oracle will create 2 invisible columns CUST_HIST_TIME_START & CUST_HIST_TIME_END that can be used  almost like any other regular table column.

Not: CUST_HIST_TIME is user defined value for time dimension specified with PERIOD FOR clause.

SQL> CREATE TABLE CUSTOMER_2
2  (
3  CUST_NO    NUMBER(12) PRIMARY KEY,
4  CUST_NAME  VARCHAR2(20) NOT NULL,
5  CUST_ADDR  VARCHAR2(40),
6  PERIOD FOR CUST_HIST_TIME ==> Invisible start and end columns are created
7  );

Table created.

Inserting data into temporal tables with invisible columns

SQL> INSERT INTO CUSTOMER_2 (CUST_NO, CUST_NAME, CUST_ADDR, CUST_HIST_TIME_START , CUST_HIST_TIME_END)
2  VALUES (1,’SHAN’,’1235 RIVER RD VA 20155′,SYSDATE, SYSDATE+90);

1 row created.

SQL> INSERT INTO CUSTOMER_2 (CUST_NO, CUST_NAME, CUST_ADDR, CUST_HIST_TIME_START , CUST_HIST_TIME_END)
2   VALUES (2,’JOE’,’78628 LAKE ST IL 60148′,SYSDATE , SYSDATE+120);

1 row created.

Click here for Part-2 : Work-in-progress

Tagged: oracle 12c PERIOD FOR, oracle 12c Temporal Validity, oracle PERIOD FOR, PERIOD FOR, Temporal Validity