17 October 2014

Oracle 12c: Temporal Validity, multiple on one table - Part Deux

One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!

In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that blog by following this link.

As you can read in that blog it is possible to create multiple Validity Periods on one table, but only via an ALTER TABLE statement.

That kept me wondering whether what I was doing was supported and what would be the proper way to go and create multiple Validity Periods for a single table.

Because I was still at Oracle Open World when I wrote that blog, it was easy for me to go over to the Demo-Grounds and get my answers.. However this was not as easy as it sounds.

After having finally found the correct station the gentleman I spoke to couldn't give me a satisfying answer, which made me doubt if I had indeed found the correct station. Maybe I didn't find the correct station, or maybe it was the language barriere that I couldn't explain what I wanted to know. Or maybe he just didn't know the answer... anyway, he was very kind and showed me some other things.

By chance I ran into Bryn Llewellyn, Distinguished Product Manager for PL/SQL, and talked to him about my quest to find an answer (among other things; like why I didn't join him for the Bike trip he organized right before OOW #BikeB4OOW).

Bryn suggested to contact Kevin Jernigan, who is a Senior Director Product Management for a number of products including Temporal Validity.
So, after getting back from Oracle Open World I contacted Kevin and he was very helpful.

Turns out there is no supporting syntax to define multiple Validity Periods for a single statement with the CREATE TABLE syntax.
This is stated in the CREATE TABLE section of the documentation:

You can specify at most one valid time dimension when you create a table. You can subsequently add additional valid time dimensions to a table with the add_period_clause of ALTER TABLE.

The limitation is with the CREATE TABLE syntax, not with the Temporal Validity implementation.

One final thing I asked Kevin: How about that DBMS_METADATA behaviour that I encountered in my blog?

Re: the problem with DBMS_METADA, I will check with development to see if this is a known issue (and maybe already fixed in a test environment?), and if not, we will file a bug to have it fixed.

Good to know that this issue is (going to be) addressed and that I found my answer about Temporal Validity.

Documentation Links

Create Table documentation Temporal Validity

1 comment: