18 March 2016

Temporal validity, multiple end dates

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you use Temporal Validity to get the correct rows?"

My first reaction was: "Yes, you can have multiple validity periods on a single table... but wouldn't it be better to have a single end date and another column to signal if it was set by an automatic proces or by a manual process?". In the latter case you would only need a single validity period.

It got me thinking though... would it be possible to define a single validity period when three (one start and two end dates) are involved?

Let's start by creating a table and populate it with some sample data.

   create table t
   (id              number       primary key
   ,name            varchar2(50) not null
   ,start_date      date         not null
   ,end_date        date
   ,manual_end_date date
   );

The table is fairly straight forward: a surrogate primary key, a name, a start date and two end dates: end_date and manual_end_date.

Next populate the table with some sample data. The name column is used to signal what the row represent, being a row that is current or not (based on sysdate).

   insert into t (id, name, start_date) values (1, 'no end, current', sysdate - 10);
   insert into t (id, name, start_date) values (2, 'no end, not current', sysdate + 10);

   insert into t (id, name, start_date, end_date) values (3, 'current', sysdate - 10, sysdate + 10);
   insert into t (id, name, start_date, end_date) values (4, 'not current, past', sysdate - 10, sysdate -9);
   insert into t (id, name, start_date, end_date) values (5, 'not current, future', sysdate + 10, sysdate +20);

   insert into t (id, name, start_date, manual_end_date) values (6, 'current', sysdate - 10, sysdate + 10);
   insert into t (id, name, start_date, manual_end_date) values (7, 'not current, past', sysdate - 10, sysdate -9);
   insert into t (id, name, start_date, manual_end_date) values (8, 'not current, future', sysdate + 10, sysdate +20);

   commit;

select *
  from t;

        ID NAME                                               START_DAT END_DATE  MANUAL_EN ABSOLUTE_
---------- -------------------------------------------------- --------- --------- --------- ---------
         1 current, no end                                    08-MAR-16
         2 not current, no end                                28-MAR-16
         3 current                                            08-MAR-16 28-MAR-16           28-MAR-16
         4 not current, past                                  08-MAR-16 09-MAR-16           09-MAR-16
         5 not current, future                                28-MAR-16 07-APR-16           07-APR-16
         6 current                                            08-MAR-16           28-MAR-16 28-MAR-16
         7 not current, past                                  08-MAR-16           09-MAR-16 09-MAR-16
         8 not current, future                                28-MAR-16           07-APR-16 07-APR-16

8 rows selected.

Now that the table is populated with the sample data, we can start the experiment. Can you have a temporal validity period based on an expression?

   SQL> alter table t
     2  add period for regular (start_date, coalesce (end_date, manual_end_date))
     3  /
   add period for regular (start_date, coalesce (end_date, manual_end_date))
                                                *
   ERROR at line 2:
   ORA-02000: missing ) keyword

Uhm,... no.
What about moving the expression over to a virtual column and use the virtual column in the temporal validity period?

   SQL> alter table t
     2  add absolute_end_date generated always
     3  as (coalesce (end_date, manual_end_date))
     4  /

   Table altered.


   SQL> alter table t
     2  add period for virt_col (start_date, absolute_end_date)
     3  /

   Table altered.

So far, so good. When playing around with temporal validity periods I ran into a bug by extracting the DDL using DBMS_METADATA, so let's see what Oracle makes of this combination of virtual column and the validity period.

   SQL> set long 9000
SQL> select dbms_metadata.get_ddl ('TABLE', 'T')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."T"
   ( "ID" NUMBER,
 "NAME" VARCHAR2(50) NOT NULL ENABLE,
 "START_DATE" DATE NOT NULL ENABLE,
 "END_DATE" DATE,
 "MANUAL_END_DATE" DATE,
 "ABSOLUTE_END_DATE" DATE GENERATED ALWAYS AS (COALESCE("END_DATE","MANUAL_END_D
ATE")) VIRTUAL ,
 PERIOD FOR "VIRT_COL"("START_DATE","ABSOLUTE_END_DATE") ,
  PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

As you can see from the output above, all seems to be just fine.
But now for the real test and query the table using the temporal validity.

   SQL> select *
     2    from t as of period for virt_col sysdate
     3  /

           ID NAME                                               START_DAT END_DATE  MANUAL_EN ABSOLUTE_
   ---------- -------------------------------------------------- --------- --------- --------- ---------
            1 current, no end                                    08-MAR-16
            3 current                                            08-MAR-16 28-MAR-16           28-MAR-16
            6 current                                            08-MAR-16           28-MAR-16 28-MAR-16

That worked! Awesome!.. Virtual Columns with a Temporal validity period, wonderful.

I ran these test on my virtual box, using the pre-built developer appliance, so it might not be the latest patch release or anything.

   SQL> select *
  2    from v$version
  3  /

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE 12.1.0.2.0 Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Links

22 November 2015

APEX 5.1: features shown at DOAG

On the last day of the DOAG conference, in the last time slot Patrick Wolf from the APEX development team did a session on the next release of APEX.
For the developer the most significant change in the Page Designer is the component view. This will be a tab in the center pane instead of a completely separate page.

Most time was spent showing the new Interactive Grid. The Interactive Grid will be a, loosley speaking, combination of an Interactive Report and a Tabular form. The menu from the Interactive Report also gets a make over. The functionality to format the result set (like pivot, group by) will be taken out of the action-menu and will get its own button next to the search bar.

Initially the Interactive Grid will be read only (like the Interactive Report) and can be set to allow data changes. As a developer you can specify which operations are allowed and add authorization to each of the DML. What is really neat is that you can also specify which column can be edited.
From a right click menu in the Interactive Grid several actions can be performed like adding or removing a row.

It is also possible to specify what item type will need to be used when you edit the field.

After this demo, Patrick showed the master-detail functionality, which also allows editing in the master and the detail. He described that only the changes are sent to the database when the save button is pressed.

Of course the new charting engine was also shown and emphasized that not all chart-types available in Oracle JET will be created in APEX through a declaritive wizard.
In case you hadn't heard: the new charting engine for APEX will be using Oracle JET.
What surprised me was that Patrick mentioned that some charts in the JET toolkit don't offer the functionality which is currently provided in AnyChart (the current charting engine) and that in some cases they will continue to use AnyChart for those components. If I remember correctly this was the case for the maps. The European maps aren't as detailed as the APEX team would like them to be, but who knows by the time that APEX 5.1 is ready for release the JET charts might be up to par.

Declarative column group headers, the ability to move columns and creating an overflow report (just like the ones that you know from Oracle Forms) were also briefly demonstrated by Patrick.
All in all, a great last session showing a glimpse into the future of Oracle APEX.
Of course all of what is shown might not be in APEX 5.1, the first slide (after the title slide) was Oracle's safe harbor statement.

16 October 2015

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Identity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:
SQL> create table t
  2  (id number  generated as identity
  3  ,name varchar2(35)
  4  );

Table created.

SQL> insert into t(name) values ('hello')
  2  /

1 row created.
   
In the first section the table is created with a "Generated Always Identity" column.
The second part shows that you can insert into the table, as long as the identity column isn't used in the insert statement. Trying to do so will lead to an exception.
SQL> insert into t values (1, 'World')
  2  /
insert into t values (1, 'World')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
   
The same is true when you attempt to insert a record type:
SQL> declare
  2     r t%rowtype;
  3  begin
  4     r.id := 42;
  5     r.name := 'world';
  6     insert into t values r;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 6
   
Now back to the question, what about an update? Let's try some different methods:
SQL> update t
  2     set id = id
  3  /
   set id = id
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
  2     set id = id + 1
  3  /
   set id = id + 1
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
  2     set id = id + 0
  3  /
   set id = id + 0
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
   
In short: updating a "Generated Always Identity" column is not allowed either.
One final remark: an identity column is not the same as a primary key. If you want to use a "Generated Always Identity" column as your primary key, then you will have to specify that explicitly.
SQL> create table t
  2  (id number  generated as identity primary key
  3  ,name varchar2(35)
  4  );

Table created.
   

12 October 2015

Busy October

October is going to be a busy month, and it already started.
The First one is the Slovenian User Group immediately followed by the Kroatian User Group.

For the Sloveninan User Group (SIOUG) I will do the following presentations:

    • Oracle 12c for Developers
      Analytic Functions: Unleash the Power
      APEX, Node.js and HTML5: Magic!
  • On top of that I was asked to join the "Ask the Experts" panel on APEX.

    In Kroatia (for the HrOUG) these presentations are on the agenda:

    • Oracle 12c for Developers
      Analytic Functions: Unleash the Power
  • Next week wednesday Oracle Open World kicks off with the ACE Director Briefing and of course OOW itself.
    On the Usergroup day (Sunday) I will participate in two presentations. First "APEX, Node.js and HTML5: Magic!" and later that day I have a 7-minute session during the EOUC "12 more things about Oracle 12c".
    The last session is going to be a fun one. As the title claims there are going to be more than 12 topics in one session with a lot of excellent speakers.

    Jonathan Lewis Less well-known enhancements of the 12c Optimizer
    Julian Dontcheff Oracle Database 12c In-Memory Advisor
    Gurcan Orhan Adapting DB 12C In-Memory to ODI 12c
    Osama Mustafa How to plugin a non-CDB database to a Container Database (CDB)
    Bjoern Rost How ASM has reached maturity in 12c
    Alex Nuijten JSON in the database
    Brendan Tierney Running R in the Database using Oracle R Enterprise
    Douwe Pieter van den Bos Maximum security architecture
    Christian Antognini Real-time Monitoring of Composite Database Operations
    Martin Widlake 12C - Clustered Data aware TABLE_CACHED_BLOCKS
    Heli Helskyaho Design your 12c Databases using Oracle SQL Dev Data Modeler
    Oded Raz Oracle 12c Privileges Analysis
    Tim Hall
    Alex Zaballa
    Maria Colgan
    Lucas Jemella

    I believe you can still register for this session.
    More Than Another 12 on Oracle Database 12c [UGF3190] Moscone South Room 306 time 13:30 - 15:15

    By the time OOW is done, so is October.

    07 August 2015

    Rounding Amounts, the missing cent: with the MODEL clause

    Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
    Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

    To create an example, first let's create a table with only three records in it.

       SQL> create table t
         2  as
         3  select rownum + 42 id
         4    from dual
         5  connect by level <= 3
         6  ;
    
       Table created.
       SQL> select *
          2    from t
          3  /
    
                    ID
            ----------
                    43
                    44
                    45
    

    In the code below the DIMENSION is the ID from the table. The measures (the "cells" that we can manipulate in the MODEL clause) are used as follows

    Amount The amount that is the result of the division per record
    Total The amount that we want to divide over the records
    Diff Placeholder for the difference between the sum of the rounded amounts and the amount that needs to be divided.
    Indicator We need to know the highest value for the DIMENSION (to add the difference to). Since we can't determine the highest value of the DIMENSION, this MEASURE is used for that.
    When we execute the query we get the following results, except for the DIMENSION column (ID) and the MEASURE Indicator all columns are empty.
          SQL> select *
            2    from t
            3  model
            4  dimension by (id)
            5    measures (cast (null as number) amount
            6    ,cast (null as number) total
            7    ,cast (null as number) diff
            8    ,id indicator
            9    )
           10  rules (
           11  )
           12  /
    
           ID           AMOUNT      TOTAL       DIFF  INDICATOR
          ---------- ---------- ---------- ---------- ----------
                  43                                          43
                  44                                          44
                  45                                          45
    
    It is time to add some rules to the MODEL clause.
    The first rule is to add the amount which needs to be divided.
    total [0] = 100   
    
    This will add a "magic" row to the resultset with Dimension 0 where the measure column total is filled with 100, the amount that we want to divide.
    The reason I call it a "magic" row is, is because it is not in the table and made up. In this row I will store some values that I need to do my calculations and such.
    The second rule is
     indicator [0] = max (indicator) [any]
     
    In this rule I will determine which row is the "last" row, the one with the highest ID.
    Next rule is to do the actual calculation: divide the amount by the number of rows in the resultset. Of course don't count the "magic" row, hence the condition id > 0.
     amount[id > 0] = round (total[0]/(count(*)[id > 0]), 2)
     
    To determine the total of the rounded values, we will use the following rule:
     amount[0] = sum (amount)[id > 0]
     
    This total amount is also placed on the "magic" row.
    Calculating the difference between the amount that we want to divide and the actual divided amount is done in the following rule:
     diff[0] = total[cv()] - amount[cv()]
     
    The difference is added to the "last" row in the last rule:
     amount[indicator[0]]  = amount[cv()] + diff[0]
     
    To see the complete query in action:
       SQL> select *
         2    from t
         3  model
         4  dimension by (id)
         5    measures (cast (null as number) amount
         6    ,cast (null as number) total
         7    ,cast (null as number) diff
         8    ,id indicator
         9    )
        10  rules (
        11    total [0] = 100
        12   ,indicator [0] = max (indicator) [any]
        13   ,amount[id> 0] = round (total[0]/(count(*)[id>0]), 2)
        14   ,amount[0] = sum (amount)[id>0]
        15   ,diff[0] = total[cv()] - amount[cv()]
        16   ,amount[indicator[0]]  = amount[cv()] + diff[0]
        17  )
        18  /
    
           ID     AMOUNT      TOTAL       DIFF  INDICATOR
       ---------- ---------- ---------- ---------- ----------
               43      33.33                               43
               44      33.33                               44
               45      33.34                               45
                0      99.99        100        .01         45
       
    As you can see in the output above the values are rounded (in the AMOUNT column) and the last row takes the difference.
    But also our "magic" row is added to the output, to filter that one out of the resultset simply add a where clause.
          SQL> select id
            2      ,amount
            3    from (select *
            4    from t
            5  model
            6  dimension by (id)
            7    measures (cast (null as number) amount
            8    ,cast (null as number) total
            9    ,cast (null as number) diff
           10    ,id indicator
           11    )
           12  rules (
           13    total [0] = 100
           14   ,indicator [0] = max (indicator) [any]
           15   ,amount[id> 0] = round (total[0]/(count(*)[id>0]), 2)
           16   ,amount[0] = sum (amount)[id>0]
           17   ,diff[0] = total[cv()] - amount[cv()]
           18   ,amount[indicator[0]]  = amount[cv()] + diff[0]
           19  ))
           20  where id> 0 order by id
           21  /
    
                  ID     AMOUNT
          ---------- ----------
                  43      33.33
                  44      33.33
                  45      33.34
          

    Links

    05 August 2015

    Rounding Amounts, the missing cent

    Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
    Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
    Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
    To solve this cent-problem, we decide that the difference should be added (or subtracted) on the last row.

    To create an example, first let's create a table with only three records in it.

       SQL> create table t
         2  as
         3  select rownum + 42 id
         4    from dual
         5  connect by level <= 3
         6  ;
    
       Table created.
       SQL> select *
          2    from t
          3  /
    
                    ID
            ----------
                    43
                    44
                    45
    

    In the code below the amount that we want to divide is included in the query on line 2. On line 3 the analytic counterpart of the COUNT(*) function is used to determine the number of records in the resultset. On line 4 you can see the result when you round the amount divided by the number of records in the resultset. All records show 33.33, just as we expected.
    Line 5 shows a trick using the LEAD function to identify the last record.

          SQL> select id
            2      ,100 amount
            3      ,count(*) over () entries
            4      ,round (100 / count(*) over (), 2) rounded
            5      ,lead (null, 1, 'x') over (order by id) lastrow
            6    from t
            7  /
    
           ID            AMOUNT    ENTRIES    ROUNDED L
          ---------- ---------- ---------- ---------- -
                  43       100           3      33.33
                  44       100           3      33.33
                  45       100           3      33.33 x
       

    Because we identified the last record in the resultset, it is easy to calculate the difference between the amount that we want to divide and the total of the rounded amount.
    In the code below this is done on lines 6 through 9. In plain English it reads: "Take the rounded amount and add to that the difference between the amount and the sum of the rounded amount, but only if you're on the last record"

    SQL> select id
      2      ,amount
      3      ,entries
      4      ,rounded
      5      ,sum (rounded) over (order by id) running_rounded
      6      ,rounded + case
      7          when lastrow = 'x'
      8          then amount - sum (rounded) over (order by id)
      9          else 0 end final_amount
     10    from (
     11  select id
     12      ,100 amount
     13      ,count(*) over () entries
     14      ,round (100 / count(*) over (), 2) rounded
     15      ,lead (null, 1, 'x') over (order by id) lastrow
     16    from t
     17  )
     18  /
    
            ID     AMOUNT    ENTRIES    ROUNDED RUNNING_ROUNDED FINAL_AMOUNT
    ---------- ---------- ---------- ---------- --------------- ------------
            43        100          3      33.33           33.33        33.33
            44        100          3      33.33           66.66        33.33
            45        100          3      33.33           99.99        33.34
    
    As you can see in the result, the missing cent is added to the last record.
    Looking at the query again, I realize that it is not necessary to use the ORDER BY in the SUM function.

    Links

    20 July 2015

    Object Type with Optional Attribute: Extra Constructor Function

    When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
    The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
    If you are unfamiliair with this technique, there are some links at the bottom of this article.

    Sometimes not all attributes of the Object Types are being passed down to the stored procedure, especially when attributes are optional.

    Although it appears to be possible to create an Object Type like the following, it will not work:

      SQL> create or replace type test_ot
      2  as object
      3  (name varchar2(20)
      4  ,description varchar2(150) null
      5  );
      6  /
    
    Type created.
       
    Notice that the Object Type named TEST_OT has two attributes of which the second one (description) is optional.
    When you try to create an instance of that Object Type, you will get an exception.
          SQL> declare
      2   o test_ot;
      3  begin
      4   o := test_ot ('name');
      5  end;
      6  /
       o := test_ot ('name');
            *
    ERROR at line 4:
    ORA-06550: line 4, column 9:
    PLS-00306: wrong number or types of arguments in call to 'TEST_OT'
    ORA-06550: line 4, column 4:
    PL/SQL: Statement ignored
       
    Both attributes need to be specified to instantiate the Object.
          SQL> declare
      2   t test_ot;
      3  begin
      4   t := test_ot ('a name','some description');
      5   dbms_output.put_line (t.name||' - '||t.description);
      6  end;
      7  /
    a name - some description
    
    PL/SQL procedure successfully completed.
       
    But this is not what we wanted, we want to instantiate the Object Type with only one attribute.
    To accomplish this, you would need to create a new CONSTRUCTOR function for the Object Type.
          SQL> create or replace type test_ot
      2  as object
      3  (name varchar2(20)
      4  ,description varchar2(150)
      5  ,constructor
      6   function test_ot (name in varchar2)
      7    return self as result
      8  );
      9  /
    
    Type created.
       
    Now the Object Type also needs an Object Type Body:
          SQL> create or replace type body test_ot
      2  as
      3   constructor
      4   function test_ot (name in varchar2)
      5      return self as result
      6   is
      7   begin
      8      self.name := name;
      9      self.description := 'created by constructor';
     10      return;
     11   end test_ot;
     12  end;
     13  /
    
    Type body created.
       
    This Constructor Function takes one argument, just for the name. In the Constructor Function the description attribute gets a static value. Of course this can also be a NULL.
    Now it is possible to instantiate the Object Type with only one argument.
          SQL> declare
      2   t test_ot;
      3  begin
      4   t := test_ot ('a name');
      5   dbms_output.put_line (t.name||' - '||t.description);
      6  end;
      7  /
    a name - created by constructor
    
    PL/SQL procedure successfully completed.
       

    Links