Oracle 12C: Temporal Validity

I have read about the Temporal Validity quite a few times.  It is not the feature I thought I understood until I did the test myself. I thought, the Temporal Validity are having time column to keep track of when records got changed but it turned out just like an ordinary datetime columns that needs to be updated manually. The columns will be created and would not show up until queried explicitly.


SQL> create table test (testno number, testname varchar2(10), period for test_time);

Table created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TESTNO                                             NUMBER
 TESTNAME                                           VARCHAR2(10)

SQL> select * from test;

no rows selected

SQL> insert into test values (1, 'test1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

    TESTNO TESTNAME
---------- ----------
         1 test1

 
 
SQL> update test set testname='test1b' where testno=1;

1 row updated.

 
 
SQL> SELECT * FROM TEST AS OF PERIOD FOR TEST_TIME TO_TIMESTAMP('SYSDATE');

    TESTNO TESTNAME
---------- ----------
         1 test1b

select testname, to_char(test_time_start,'dd-mon-yyyy'), to_char(test_time_end,'dd-mon-yyyy') from test;

update test
set test_time_start = to_date('01-JUN-1995','dd-MON-yyyy'), test_time_end = to_date('15-SEP-2010','dd-MON-yyyy')
where testno=1;


SQL> select testname, to_char(test_time_start,'dd-mon-yyyy'), to_char(test_time_end,'dd-mon-yyyy') from test;

TESTNAME   TO_CHAR(TES TO_CHAR(TES
---------- ----------- -----------
test1b     01-jun-1995 15-sep-2010

No comments:

Post a Comment