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  /

    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


    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  /

    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  /
    ---------- ---------- ---------- ---------- --------------- ------------
            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.


    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.


    02 July 2015

    Conditional Compilation and Static Boolean

    One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
    This post is not about Logger, but some dealings with Conditional Compilation.

    With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
    The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where issue #103 is implemented). The dependency lies in the fact that the custom template is stored in the LOGGER_PREFS table and before issue #103 was resolved there was no way to add data to the LOGGER_PREFS table (or at least not a supported way).

    Conditinal Compilation is just what the doctor ordered. With a Conditional Compilation directive you can check if Logger is at least version 3, so we can have a supported way of writing into the LOGGER_PREFS table. Sounds easy enough.

    And this is where I made some discoveries about Conditional Compilation.

    Let's begin with a package specification with only CONSTANTS in there.

          create or replace package constants_pkg
             version   constant varchar2(10) := '1.2.3';
             major_num constant number := 1;
             major_int constant pls_integer := 1;
             major_vc  constant varchar2(1) := 'a';
          end constants_pkg;
    There are a few variations in there, starting with the current method that Logger has implemented the version number (the constant called VERSION).
    Second there is a NUMBER constant.
    Third is an PLS_INTEGER constant.
    Fourth a variation to the first constant, just one character.

    Following is a procedure, called conditional (how appropriate):

          create or replace
          procedure conditional
             $if constants_pkg.version like '1%'
                dbms_output.put_line ('string, with LIKE comparison');
             dbms_output.put_line ('This will always be displayed');
          end conditional;
    The $IF, $THEN, $END are part of the syntax used for Conditional Compilation.
    On line 5 the packaged constant is checked if the string start with a 1. When it does, line 7 is included in the compiled code. If the packaged constant doesn't start with a 1 then line 7 is not included in the compiled code.
    You might say: "Should you do a comparison like this"
          $if to_number (substr (constants_pkg.version, 1, 1)) > 1
    and you would be right, but... for this example it doesn't matter as both don't work. When you try to compile the code, you will see the following error:
    -------- -----------------------------------------------------------------
    4/8  PLS-00174: a static boolean expression must be used

    So my next attempt at getting this to work, was using the full version constant:

          $if constants_pkg.version = '1.2.3'
    With the same results, the same compilation error.

    What about just a single character string?

          $if constants_pkg.major_vc = '1'
    ...Nope, again the same compilation error.

    Next up, try a NUMBER constant instead:

          $if constants_pkg.major_num = 1.0
    I thought the ".0" at the end could make a difference, but alas.. same compilation error.

    Last attempt: the PLS_INTEGER:

          $if constants_pkg.major_int = 1
    This may not come as a surprise now, but this works. :D
    This is similar to the way that Oracle does it itself.

    When you want to know which release of the Oracle database you are on, you can check DBMS_DB_VERSION. There are constants defined in DBMS_DB_VERSION which you can use with Conditional Compilation.

    So Martin, if you are still reading: Can I have the version as a PLS_INTEGER, please?

    Links to related articles

    1. Speed Up Development with Logger
    2. Create Custom Template with LoggerUtil

    11 June 2015

    Deadlock with a Virtual Column

    Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
    In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
    While using the Virtual Columns, we ran into a little oddity with them.

    First of all let's start with the version of the database that I tested this on. Yes, I know it's an 11 database that's because the client is still running on this release.
    These tests were run on the Virtual Box image that is provided by Oracle.
    I still need to run these tests on Oracle 12c.
    I just ran the script on my Oracle 12c database (in a PDB) and the same deadlock occurs.

       SQL> select *
         2    from v$version
         3  /
       Oracle Database 11g Enterprise Edition Release - Production
       PL/SQL Release - Production
       CORE Production
       TNS for Linux: Version - Production
       NLSRTL Version - Production

    The setup for this test is based on a copy of the EMP table.

    SQL> create table emp
      2  as
      3  select *
      4    from scott.emp
      5  /
    Table created.

    To create a Virtual Column on my copy of the EMP table, I need a deterministic function.
    This function takes two arguments, one for the ENAME and one for the EMPNO. And what does the function do? Actually nothing, it returns NULL.

       SQL> create or replace
         2  function vc
         3    (p_ename in emp.ename%type
         4    ,p_empno in emp.empno%type
         5    )
         6   return varchar2 deterministic
         7  is
         8  begin
         9   return null;
        10  end vc;
        11  /
       Function created.

    The function needs to be deterministic because that is required when you want to define a Virtual Column.
    Now we can add the Virtual Column (called VC) to my copy of the EMP table.

    SQL> alter table emp
      2  add descr as (vc (ename, empno))
      3  /
    Table altered.

    So far, no problems. It all works.
    The trouble began when you execute a TRUNCATE TABLE statement.

       SQL> truncate table emp
         2  /
       truncate table emp
       ERROR at line 1:
       ORA-04020: deadlock detected while trying to lock object ALEX.EMP

    To be honest, this is not the first deadlock that I created and it probably won't be the last :)
    The snag with deadlocks is trying to figure out what caused it in the first place.
    Of all things that I thought would happen, a deadlock is not one of them.
    How can it? It is my own personal VirtualBox and I am the only one using it.

    The first step investigating a deadlock is usually the alert.log, however there was nothing in it regarding the deadlock... honest.

    After a bit of googling, I found a note on deadlocks by Yong Huang (link at the bottom) describing the causes of deadlocks.
    In that article he points out that you can get more insight if you set a certain event, and that's what I did.

       SQL> alter session set events '4020 trace name processstate forever, level 10'
      2  /
    Session altered.
    To find out where the trace file was located, and the name of it, I used a query by Tanel Poder (link at the bottom).
          SQL> select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
         2 (select spid||case when traceid is not null then '_'||traceid else null end
         3       from v$process where addr = (select paddr from v$session
         4       where sid = (select sid from v$mystat
         5           where rownum = 1
         6      )
         7         )
         8 ) || '.trc' tracefile
         9* from v$parameter where name = 'user_dump_dest'
       SQL> /

    In that trace file was the following information:

       A deadlock among DDL and parse locks is detected.
       This deadlock is usually due to user errors in
       the design of an application or from issuing a set
       of concurrent statements which can cause a deadlock.
       This should not be reported to Oracle Support.
       The following information may aid in finding
       the errors which cause the deadlock:
       ORA-04020: deadlock detected while trying to lock object ALEX.EMP
        object   waiting  waiting       blocking blocking
        handle   session     lock mode   session     lock mode
       --------  -------- -------- ----  -------- -------- ----
       0x31ae0d7c  0x3aab3cf4 0x31afc4c0    X  0x3aab3cf4 0x31aeb718    S
    As you can see in the text (taken from the trace file), you can see that the sessions involved in the deadlock is the same, both the waiting and the blocking session are 0x3aab3cf4.

    So at least my assumptions were correct, I was blocking myself.
    Not that it got me any further...

    After quite a long time fiddling around, I discovered the following.
    If I change the function like below, the deadlock doesn't occur. See if you can spot the difference.

       SQL> create or replace
      2  function vc
      3    (p_ename in varchar2
      4    ,p_empno in number
      5    )
      6   return varchar2 deterministic
      7  is
      8  begin
      9   return null;
    10  end vc;
    11  /
    Function created.
    SQL> truncate table emp
      2  /
    Table truncated.

    Did you spot the difference?
    The function at first used anchored datatypes for the arguments (%TYPE) and later on just a simple type (NUMBER and VARCHAR2).
    Using the simple types, the truncate works.
    There are some oddities when it exactly occurs and I haven't figured out yet when the deadlock occurs exactly. It seems that when an argument is anchored (%TYPE) and the underlying datatype is a NUMBER, the deadlock occurs...
    Like I said I haven't really figured out what causes it.


    1. Two common Deadlocks by Yong Huang
    2. Tanel Poder: Querying the current tracefile name, using SQL – with tracefile_identifier
    3. Oracle Base on Virtual Columns

    09 May 2015

    LoggerUtil: Create a Custom Template

    Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
    If you haven't read that blog about my pet project, here's the synopsis:

    I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have written a generator which takes the (packaged) procedure name and generates the body with all the instrumentation code in place. The only thing left to do is focus on the functionality that needs to be implemented in the first place.

    First of all the name of the package changed. Now it is called LoggerUtil, which I believe is more inline with the functionality.
    But that is only a minor change. The big change is that it is now possible to create your own templates for Procedures and Functions.

    The basic mechanism to generate a template is the same as before, see my previous blog for an example or refer to the README.md file on the project page.

    In order to use custom templates, you will need to use Logger release 3.0.1, because the supportive procedure to set and get custom preferences. Unfortunately it is not possible to use conditional compilation checking the version of Logger that you have installed.
    To create a custom template you can use the procedure called "set_custom_template". This procedure takes two arguments:

    1. P_TYPE: which kind of template do you want to store; a (F)unction or (P)rocedure.
    2. P_TEMPLATE: a string containing your custom template
    For example:
    loggerutil.set_custom_template (p_type     => 'P'
                                   ,p_template => 'your_custom_template'
    The custom template is stored in the standard LOGGER_PREFS table with the custom preferences:
    Because of the current limitations of the LOGGER_PREFS table, your custom template cannot be longer than 255 characters.
    There are some placeholders that you can use your custom template:
    The name of the procedure or function.
    All the arguments are listed (IN, OUT and IN/OUT). Handy for when you want to use this in the comments section. The text (or spaces) before the placeholder is placed before each argument.
    Only the IN and IN/OUT arguments are used for calls to Logger.

    When you want to reset the custom templates and go back to the original use:


    You can find the LoggerUtil project on Github.