Oracle 12c New Features " Period Definition "

Oracle 12c allows you to easily define when a record is effective from. Before having this feature, it was often done using start and end dates, or an effective date, in the database table.

Now, when you create a table, you add a PERIOD clause:

Hide Copy Code

CREATE TABLE student ( 

student_id NUMBER,

first_name VARCHAR2(50), 

last_name VARCHAR2(50), 

start_date DATE, 

end_date DATE, PERIOD FOR VALID (start_date, end_date));

This PERIOD clause refers to two columns, which are the start and end date. Now, you'll still need these columns, but it makes your queries a lot easier.

Hide Copy Code

SELECT student_id, 

       first_name, 

       last_name, 

       start_date, 

       end_date 

FROM student 

AS OF PERIOD FOR VALID sysdate;

You add in the AS OF PERIOD FOR VALID, and then a date. The date can be any value you like, and the records returned will be the ones where the date is between the start and end date.

It's a bit of an improvement to a process that I've seen quite often.