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.

07 May 2015

Splitting a comma delimited string the RegExp way, Part Three

The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just to add to those two articles on the subject, here is a third one combining the first two articles.

Recently I was asked for help in a comment on how to go about and split up a string like the following

The expected outcome would be

As you can see the input string consists of two different delimiters, namely a comma and a forward slash (/).
To split this string up, you will need both techniques from the other articles.

Let's start with a variable containing the input string.

      SQL> var input varchar2(150)
      SQL> exec :input := 'ABC/FDF,RET/YRT,UYT/ERT'

      PL/SQL procedure successfully completed.

The first step is to split the string up using the first method, split up the string using the comma as a delimiter.

SQL> select regexp_substr (:input, '[^,]+',1, rownum) str
  2    from dual
  3   connect by level <= regexp_count (:input, '[^,]+')
  4  ;

This will leave us with three records each consisting of a string that needs further splitting up, but this time with the forward slash as the delimiter.

Using these rows as the input in the next phase, use the technique described in the second article.
By introducing Subquery Factoring (lines 1-5), create a named query "commas"

   SQL> with commas
     2  as
     3  (select regexp_substr (:input, '[^,]+',1, rownum) str
     4    from dual
     5   connect by level <= regexp_count (:input, '[^,]+'))
     6   select regexp_substr (str, '[^\/]+', 1, rn) split
     7   from commas
     8   cross
     9   join (select rownum rn
    10       from (select max (regexp_count(rtrim (str, '/')||'/', '\/')) mx
    11        from commas
    12     )
    13    connect by level <= mx
    14    )
    15   where regexp_substr (str, '[^\/]+', 1, rn) is not null
    16   ;

The forward slash has special meaning with regular expressions it needs to be escaped using a backslash.
You can see this on lines 6, 10, and 15.
What is interesting, or at least I find interesting, is the use of the RTRIM on line 10.
Each value per line is not completely delimited by the forward slashes, the trailing one is missing. Just to concatenate one to each line would be to easy, what if there is a trailing slash?
The RTRIM removes the trailing slash and concatenates one at the end, making sure that the string is split up at the right place.