Qualified Expressions Quite Puzzling - Part Three: Bug!

After some back and forth with Patrick Barel and Steven Feuerstein, it became clear that it most likely is a bug with Qualified Expressions.
A simple case to reproduce the bug is below

SQL> set serveroutput on


SQL> declare
  2     type tbl_t is table of pls_integer
  3        index by pls_integer;
  4     l_tbl tbl_t;
  5     l_idx pls_integer;
  6  begin
  7     for i in 1..3
  8     loop
  9        l_tbl := tbl_t (i => i);
 10     end loop;
 11     --
 12     l_idx := l_tbl.first;
 13     while l_idx is not null
 14     loop
 15        sys.dbms_output.put_line (to_char (l_idx)
 16                                 ||' - '||
 17                                 to_char (l_tbl(l_idx))
 18                                 );
 19        l_idx := l_tbl.next (l_idx);
 20     end loop;
 21  end;
 22  /


1 - 1
2 - 2
3 - 3


PL/SQL procedure successfully completed.

Why it is the above a bug? If you do the a similar assignment, but without a loop, the outcome is different (and most likely correct)

SQL> declare
  2     type tbl_t is table of pls_integer
  3        index by pls_integer;
  4     l_tbl tbl_t;
  5     l_idx pls_integer;
  6  begin
  7     l_tbl := tbl_t (1 => 1);
  8     l_tbl := tbl_t (2 => 2);
  9     l_tbl := tbl_t (3 => 3);
 10     --
 11     l_idx := l_tbl.first;
 12     while l_idx is not null
 13     loop
 14        sys.dbms_output.put_line (to_char (l_idx)
 15                                 ||' - '||
 16                                 to_char (l_tbl(l_idx))
 17                                 );
 18        l_idx := l_tbl.next (l_idx);
 19     end loop;
 20  end;
 21  /


3 - 3


PL/SQL procedure successfully completed.

Bug 32057533 - ASSIGNMENT TO COLLECTION USING QUALIFIED EXPRESSION APPENDS INSTEAD OF REPLACING.

Qualified Expressions Quite Puzzling - Part Two

After I published my blog about Qualified Expressions, my dear friend Patrick Barel made a comment that my remarks about the Cursor For Loop was not at all what he expected to happen.

This is the code block that Patrick referred to.

 10     for r in (select * from t)
 11     loop
 12           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 13                                                    ,num2 => r.id));
 14     end loop;
  

with these results

1 - 1
2 - 2
3 - 3
   

A different way of looking at this code example might expect the outcome to be only one record, being the last one.
Each iteration would replace the complete Associative Array with a new instance of it.

So, there was another Puzzling thing in the original blogpost that I hadn't noticed before, probably because I wasn't looking for it. Why isn't the whole Associative Array replaced? It seems that this code would append values in the Associative Array and this was my objective. Strange indeed.
Anyhoo, probably the better way of working with Qualified Expressions to append values to the Associative Array, the index should be on the other side of the assignment:

 10     for r in (select * from t)
 11     loop
 12        l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 13                                         ,num2 => r.id);
 14     end loop;
   

When inserting the pointless conditions inside the For Loop, all values from the table are inserted into the Associative Array

 10     for r in (select * from t)
 11     loop
 12        if r.yes_no = 'Y'
 13        then
 14           l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 15                                            ,num2 => r.id);
 16        else
 17           l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 18                                            ,num2 => r.id);
 19        end if;
 20     end loop;
  

with all records from the table that get populated in the Associative Array:

1 - 1
2 - 2
3 - 3
   

Qualified Expressions Quite Puzzling

Qualified expressions for PL/SQL were introduced in version 18 of the Oracle database. They provide a clearer way of working with collections. At the bottom of this post is the link to the official documentation.
However recently I encountered something that left me puzzled and I still can't explain the reason why. So this blogpost is just about my observations and I welcome any comments.

First a short demo of how you can work with Qualified Expressions.

  SQL> select banner
  2    from v$version;

BANNER 
___________________________________________________________________ 
Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production   
  

  SQL> set serveroutput on


SQL> declare
  2     type rec_t is record
  3         (num1 number
  4         ,num2 number);
  5     type tbl_t is table of rec_t
  6        index by pls_integer;
  7     l_tbl tbl_t;
  8     l_idx pls_integer;
  9  begin
 10     for i in 1..3
 11     loop
 12        l_tbl := tbl_t (i => rec_t (num1 => i
 13                                   ,num2 => i));
 14     end loop;
 15     --
 16     l_idx := l_tbl.first;
 17     while l_idx is not null
 18     loop
 19        sys.dbms_output.put_line (to_char (l_tbl(l_idx).num1)
 20                                 ||' - '||
 21                                 to_char (l_tbl(l_idx).num2)
 22                                 );
 23        l_idx := l_tbl.next (l_idx);
 24     end loop;
 25  end;
 26  /
   

On line 2 - 4 a Record Type is declared with two numeric attributes. Line 5 and 6 define an Associative Array of the Record Type.
The Qualified Expression is on lines 12 and 13.
In the loop we assign the loop index variable to the Record Type using the Qualified Expression syntax.
To view the content of the Associative Array, lines 16 through 24 are used. When running this block of code the output is:

1 - 1
2 - 2
3 - 3
   

No surprises here, it works as expected. Well, actually it is surprising take a look at the follow-up blog.

Cursor For Loop

The previous example used a Numeric For Loop, but I observed strange things when using a Cursor For Loop.

  SQL> drop table t purge
  2  /



Table T dropped.

SQL> create table t
  2  (id number
  3  ,yes_no varchar2(1)
  4  )
  5  /



Table T created.

SQL> insert into t values (1, 'Y');



1 row inserted.

SQL> insert into t values (2, 'N');



1 row inserted.

SQL> insert into t values (3, 'Y');



1 row inserted.

SQL> commit;



Commit complete.
  

The table contains three records with different Yes/No-indicators. Instead of showing the complete block of code I will only show the changes that I made using the previous example.

 10     for r in (select * from t)
 11     loop
 12           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 13                                                    ,num2 => r.id));
 14     end loop;
  

The Numeric For Loop is removed and a Cursor For Loop is inserted in the code block. Instead of using the index-variable from the first code example, I am using the current count of the Associative Array and add one to that (l_tbl.count + 1).
The results are the same as the first code block.

1 - 1
2 - 2
3 - 3
   

...but you said Strange Things?

Yes, I did... but so far, it seems normal.
The strange thing happens when a condition is added inside the For-Loop.

 10     for r in (select * from t)
 11     loop
 12        if r.yes_no = 'Y'
 13        then
 14           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 15                                                    ,num2 => r.id));
 16        else
 17           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 18                                                    ,num2 => r.id));
 19  
 20        end if;
 21     end loop;
  

The table has a Yes_No column and inside the For Loop, the value of this column is used.
When you examine the code above, you will notice that it will assign a value to the collection regardless of the value of the column. The assignment is exactly the same in lines 14-15 and 17-18. The content of the Associative Array after the loop should be the same as before, but this is not the case.
The result looks like this:

  1 - 1
  3 - 3
  

What happened to the second record?... Your guess is as good as mine.

I've tried several variations, like using a local counter to assign instead of the collection.count + 1, but that doesn't make a difference.
Like said before: I welcome your comments for more insight into this strange behavior.

The same is reproducable on Oracle 19...

  SQL> select banner 
  2    from v$version
  3  /

BANNER 
_________________________________________________________________________ 
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production  
  

Links

• Oracle 19 Qualified Expressions

Conditional Compilation; One Dynamic Package Specification

When you want to make a distinction between different databases (like Development, Test, Acceptance, and Production) and have a single code base, conditional compilation can help out. Most of the time I read about conditional compilation when the use case is to eliminate code from the production environment, like instrumentation, which is of course a legit scenario. Some functionality should never run on Production, but likewise some functionality should never run on Dev, Test or Acceptance.

For example; we sent out emails to customers about their order statusses. This functionality should only be run on the Production database, and never-ever on any of the other databases. Initially there was a toggle in the application dictating if the emails should go to customers or to a dummy email address within the company. How fool-proof is this solution? Short answer: it's not. Sooner or later there will be someone who sets the toggle to "Sent to Customers" in the wrong environment. Of course there are many more examples like this one.

Anyway, to determine the environment which can be used for conditional compilation, I use a package specification which is compiled dynamically. Using the database name, which can be found in the USERENV context, the value is determined. To output a textual representation of a boolean value, I use LOGGER.TOCHAR to output "true", "false" or "null", but of course you can also write your own boolean-to-text-translation.

declare
   l_pack_spec varchar2(32767);
   l_env varchar2(25);
begin
   l_env := sys_context ('userenv', 'db_name');
   l_pack_spec := 'create or replace package environment_pkg'||chr(10);
   l_pack_spec := l_pack_spec ||'is'||chr(10);
   l_pack_spec := l_pack_spec ||'   --=='||chr(10);
   l_pack_spec := l_pack_spec ||'   -- Environment Information, useful for conditional compilation'||chr(10);
   l_pack_spec := l_pack_spec ||'   development constant boolean := '||lower (logger.tochar (l_env = 'DEV'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   test        constant boolean := '||lower (logger.tochar (l_env = 'TST'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   acceptance  constant boolean := '||lower (logger.tochar (l_env = 'ACC'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   production  constant boolean := '||lower (logger.tochar (l_env = 'PRD'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   --=='||chr(10);
   l_pack_spec := l_pack_spec ||'end environment_pkg;'||chr(10);
   execute immediate l_pack_spec;
end;

When you run the code above, the resulting code will be something like:

package environment_pkg
is
   --==
   -- Environment Information, useful for conditional compilation
   development constant boolean := true;
   test        constant boolean := false;
   acceptance  constant boolean := false;
   production  constant boolean := false;
   --==
end environment_pkg;

Indicating that the current database is the Development database.

Now you can use conditional compilation to include or exclude certain code sections, like:

$if environment_pkg.production $then
   -- Here comes the code to send an email to customers
   ....
$else
   -- Here comes the code to send an email to a dummy internal address
   ....
$end

To see the effect of conditional compilation, what does the code actually look like, you can use the built in package DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE which output the result to DBMS_OUTPUT

set serveroutput on
begin
   dbms_preprocessor.print_post_processed_source (object_type    => 'PACKAGE BODY'
                                                 ,schema_name    => user
                                                 ,object_name    => 'MAIL_PKG' -- package where you use the conditional compilation
                                                 );
end;
/

Taming The Queue - when the third party can only handle so many connections

We had a problem with the awesome power that the Oracle database offers... or to be more specific the third party had a problem with that awesome power.

Setting the stage

Our applications handles many, many orders per day and mostly they are processed in waves during the day. Meaning there are peaks and valleys in the amount of orders that need to be processed. When the orders are processed they are send to a third party application by using a REST Service. Informing the third party doesn't have to be "real time", as long as they get the information.
The way we set up our side of the application is as follows:

• when the order has a certain status, enough information is placed on a Queue (Advanced Queueing)
• A callback function on the Queue will construct the JSON and do the REST Call (using APEX_WEBSERVICE)

The advantage of this method is that the operational system can continue working, without having to wait for the REST service to complete. Informing the other system is done in the background.

The Problem

The advantage of having the database of the Queue is that it "auto-scales". Because it runs in the background it can spin up as many "sessions" as it wants to perform the callback function and clear the queue. If the system gets busy, it will scale down the processing of the Queue so that the operational system doesn't slow down to a crawl. Simply put: Awesome.
However the third party could only handle a limited number of connections, four to be precise. The consequence of this was that when the system would spin up many sessions, a lot of them would get an Error: "Concurrent request limit exceeded. Request blocked." and the orders had to be tried again.

Solutions?

As we know that the third party could only handle four "sessions" we prefer to reduce the number of sessions that the database would spin up. However there is (as far as I could tell) no way to tell Oracle "you can only use at most four sessions to clear the Queue".
Reaching out on Twitter suggested adjusting the AQ_TM_PROCESSES parameter but that controls the time, not the number of "sessions".
Another suggestion was to tweak JOB_QUEUE_PROCESSES, but we didn't try that to be honest. There are other processes that utilize DBMS_SCHEDULER (e.g. for importing orders) that we didn't want to interfere with this parameter.
Thanks to Vidar Eidissen and Philipp Salvisberg for your responses on Twitter.

Our Solution

The method that we implemented was as follows:

1. Get an exclusive lock
2. Construct the necessary JSON and do the REST Call
3. Release the lock

To facilitate this method, I created a table which is just to coordinate the locks:

create table lock_handles
   (handle varchar2(10)
   ,nr    number
   ,constraint lh_pk primary key (handle)
   );

insert into lock_handles values ('LOCK1', 0);
insert into lock_handles values ('LOCK2', 0);
insert into lock_handles values ('LOCK3', 0);
insert into lock_handles values ('LOCK4', 0);

commit;

The only purpose that this table serves is just for locking, thinking about it a little bit more I doubt that I need more than one column though.

Next a function to get a lock, of course this function is placed in a package (for the example, the name of the package is "MYPKG".

  function get_lock
    return lock_handles.handle%type
  is
    e_locked exception;
    pragma exception_init (e_locked, -54);
    function get_lock (p_handle in lock_handles.handle%type)
        return lock_handles.handle%type
    is
        l_nr  lock_handles.nr%type;
    begin
        select lh.nr
          into l_nr
          from lock_handles lh
        where lh.handle = p_handle
          for update nowait;
        return p_handle;            
    end get_lock;
  begin
     for i in 1..4
     loop
        begin
           return get_lock ('LOCK'||to_char (i));
        exception
        when e_locked
        then
          continue;
        end;
      end loop;
      -- wait for a little bit before attempting 
      -- to get the lock again by recursively 
      -- calling this function
      sys.dbms_session.sleep (10);
     return mypkg.get_lock;
  end get_lock;

When calling this function, at first it will attempt to lock the row in the table with handle "LOCK1". Instead of waiting (FOR UPDATE NOWAIT) when that row is locked, an exception is raised. With the Pragma Exception Init, the exception is named E_LOCKED and thus can be handled by name.
When the exception is raised, another session has a lock on that row, it will attempt to lock the row in the table with handle "LOCK2", and so on.
If no locks can be obtained, tried and exception is raised each time, the function will wait for a little bit (10 seconds) and than call the function recursively to start all over again.
Let's say that a certain function call obtains a lock on the row, the calling program can continue and do the REST call.
When the REST Call is completed, the lock on the table is released by ending the transaction by issuing a commit.

Does it work?

Until sofar the theory, but does it work? Which is of course a valid question and I was shocked by my first tests. I tried processing a mere 15 orders and to my surprise I discovered that only the first lockhandle, LOCK1, was used.
It dawned that the queue could easily be handled by a single session, so the callback would just serialize the processing.
Increasing the load on the Queue did indeed show that all lockhandles were used. Also nice to see that at after a certain time, the usage of the lockhandle also decreases. It scales to whatever Oracle deems a good method of processing the Queue.

Could it be handled differently? Yes, probably and I'm open for suggestions.
Could the code be improved? Yes, probably and I'm open for suggestions.

Doh!

Just as I finishing up this blogpost, Connor McDonald chimed in on Twitter.

Well, yes Connor, yes, it certainly does...
The rewritten method is as follows

   function get_lock
      return varchar2
   is
      l_lock integer;
   begin
      loop
         for i in 1..4 -- max number of concurrent sessions
         loop
            l_lock := dbms_lock.request (id                => to_number ('223344'||to_char (i)) -- arbitrary locknr
                                        ,lockmode          => dbms_lock.x_mode -- exclusive mode
                                        ,timeout           => 0 -- don't wait to obtain a lock
                                        ,release_on_commit => true -- release when transaction ends
                                        );
            if l_lock = 1 -- Timeout
            then
               continue; -- try the next lock
            else
               return 'LOCK'||to_char (i);
            end if;
         end loop;
         -- A variation to the suggestion that
         -- Connor made in the comments.
         -- I've added an extra pause here
         -- before attempting to get another lock
         sys.dbms_session.sleep (10);
      end loop;
   end get_lock;

No need for the extra table, and the code is a lot shorter.

OGB Appreciation Day: APEX_DATA_PARSER: Flexible, Powerful, Awesome (#ThanksOGB #OrclAPEX)

For this years OGB Appreciation Day I wanted to highlight the very awesome APEX_DATA_PARSER.

At my current project one of the requirements is that CSV-files are to be uploaded by the user and the application has to figure out what type of file it is and process accordingly.
So the challenge with this is:

• Which type of file is it?
• What delimiter was used?

Uploading the file is straight forward enough with Application Express (APEX) and out of scope of this blog. The files are stored in a APEX Collection before they are processed.

The original procedure to determine what type of file it was and how to process it was quite cumbersome.
The file is parsed and the first line was extracted. The string which effectively was the header line of the CSV file was compared with a predefined string and based on this comparison it was determined what type of file it was.
The problem was that sometimes the header line would look like this:

      DATE_,PAYMENT_METHOD,CURRENCY,AMOUNT,STATUS,ID,DESCRIPTION
         

and sometimes it would like this:

      "DATE_","PAYMENT_METHOD","CURRENCY","AMOUNT","STATUS","ID","DESCRIPTION"
         

or maybe even sometime like this:

      "DATE_";"PAYMENT_METHOD";"CURRENCY";"AMOUNT";"STATUS";"ID";"DESCRIPTION"
         

The header line could be enclosed in quotes, or not.. The delimiter was a comma, or not...
I've simplified the example, the real header lines where a lot longer.
You can imagine that parsing the rest of the file was quite tricky, as the file content could have commas as delimiter or semi-colons or ...
Because of all the variations the procedure was quite lengthy and error prone.

Simplifying with APEX_DATA_PARSER

With APEX_DATA_PARSER things got a lot cleaner, not to mention a lot faster. The example below will only give you the gist of the procedure.

First the different header lines where declared as a record type of datatype APEX_T_VARCHAR2.

         l_file1_header apex_t_varchar2 := apex_t_varchar2 ('DATE_'
                                                           ,'PAYMENT_METHOD'
                                                           ,'CURRENCY'
                                                           ,'AMOUNT'
                                                           ,'STATUS'
                                                           ,'DESCRIPTION'
                                                           );
         l_file2_headers apex_t_varchar2 := apex_t_varchar2 ('ID'
                                                            ,'REF'
                                                            ,'ORDER_'
                                                            ,'STATUS'
                                                            ,'LIB'
                                                            );
         

Second we need to get the header line of the file that was uploaded through the APEX application.

         select column_name
           bulk collect
           into l_file_header
           from table (apex_data_parser.get_columns
                         (apex_data_parser.discover (p_content   => r_files.file_content
                                                    ,p_file_name => r_files.filename
                                                    )));
         

The APEX_DATA_PARSER offers a function to discover information about the file. The function return a file-profile and contains information about the file-encoding, file-type, how many rows it parsed, what type of delimited is used in case of CSV, etc. etc.. This function is aptly named DISCOVER.
With the APEX_DATA_PARSER.GET_COLUMNS function uses the file profile created by the DISCOVER-function to obtain information about the content: Which columns are in the file, what might the datatype be, what is the format mask that was used, in which order are the columns.
With the simple SELECT statement above we know the header columns in the uploaded file.

We're almost there..

Now that the header columns are known, we can determine how to proceed from here with a straightforward CASE statement.
Why a CASE statement you might ask. It gives a easy opportunity to raise an exception in case an unknown file is uploaded... i.e. it will raise a CASE_NOT_FOUND exception.

         case l_file_header
         when l_file1_header
         then
            process_file1;
         when l_file2_header
         then
            process_file2;
         end case;
         

The uploaded files are moved from the APEX collection to a regular heap table called ALL_UPLOADED_FILES (omitted from this blogpost).

To process the content of the file we can now do an INSERT-SELECT and drop the content into the destination table:

         insert into destination_tbl
            (date_
            ,payment_method
            ,currency
            ,amount
            ,status
            ,description
            )
         select to_date (col001, 'yyyy-mm-dd hh24:mi:ss')
               ,col002
               ,col003
               ,col005
               ,col006
               ,col007
           from all_uploaded_files fle
               ,table (apex_data_parser.parse (p_content   => fle.csv
                                              ,p_file_name => fle.csv_filename
                                              ,p_skip_rows => 1 -- skip the header line
                                              ))      
         

On a sidenote: we're using DropZone, so the file(s) are in an APEX collection which is read with the following query:

         select c001    as filename
               ,c002    as mime_type
               ,d001    as date_created
               ,n001    as file_id
               ,blob001 as file_content
           from apex_collections
          where collection_name = 'DROPZONE_UPLOAD'
         

A big thank you to the APEX team for implementing this awesome functionality! Thanks ODC!

ODC Appreciation Day: EMP #ThanksODC

Here is my very short entry for the Oracle Developer Community Appreciation Day 2017.

Very often I use the good ol' EMP table to demonstrate even the latest features of SQL and PL/SQL.
Everybody seems to know the EMP table, and some even know some of the content off the top of their head (yes, I'm guilty of that too). Whenever I need to write a hierarchical query and am not really sure what which column goes on which side in the CONNECT BY clause, I will use EMP to test and verify my assumptions. Something I did just this afternoon.

I found this old screenshot showing that the EMP table was around in Oracle database version 4.1.1.. history doesn't tell whatever happened to Carter though...

Update

Apparently I'm not the only one who's a fan of the EMP, there is even a store and a museum...

Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.

The requirement that I needed to implement was the following:

A number of tasks need to be fulfilled, the order doesn't matter, and they need to be checked by a supervisor. Before the supervisor gives his/her stamp of approval, the tasks all need to be completed.

For this example I will leave the authorization out, it is irrelevant.

create table tasks
(task_date       date   not null
,task_a          number not null check (task_a in (0,1))
,task_b          number not null check (task_b in (0,1))
,task_c          number not null check (task_c in (0,1))
,tasks_checked   number not null check (tasks_checked in (0,1))
)
/

The table contains a date for which the tasks need to be completed, indicators for the individual tasks (A,B, and C) and the Tasks Checked indicator.
All indicators are NOT NULL, datatype NUMBER and only allowed to contain a zero or one. An zero indicates FALSE, a one indicates TRUE.

The requirement states that the verification of the tasks should take place before the tasks are done. There are several ways to implement this requirement, and this time I'm going to use the combination of zeros and ones to create a binary representation.
I will add to overall status as a virtual column to the table using the built-in function BIN_TO_NUM.

alter table tasks
add (bin_status as (bin_to_num (task_a, task_b, task_c, tasks_checked)))
/

Now the (not so) magic part:
When the binary status equals 14, all tasks are completed and can be checked by a supervisor.
When the binary status is an odd number, the checking of the tasks is done before the tasks are completed and this is not allowed. The only exception is when the binary status equals fifteen (15), then all tasks are done and it is checked by a supervisor.
When the binary status an even number, all is good.
All this can easily be captured in a CHECK constraint:

alter table tasks
add constraint chk check (
   case when bin_status = 15 then 1
        when mod (bin_status, 2) = 0 then 1 
        else 0 end = 1
)
/

Finally two inserts to show that it works as expected.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,0
   ,0
   ,1
   ,0
 14     );

1 row created.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,1
   ,0
   ,1
   ,1
   );

insert into tasks
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.CHK) violated

For the first time I've used LiveSQL to create the scripts that go along this blogpost, you can find that right here.

Links

• Oracle documentation: BIN_TO_NUM
• LiveSQL demo

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with a single column.

   create table test
   (str varchar2(10));

Now the magic part: add a check constraint using a regular expression

   alter table test
   add constraint str_chk check (
      regexp_like (str, '^[^[:space:]].+[^[:space:]]$')
    );

The regular expression reads: The string should start (the first caret) with any character which is not in the character class of [:space:], followed by one or more characters (the period) and it should end with (the dollar) any character as long as it's not in the character class of [:space:].

UPDATE - 07-Sept-2016

The expression that is used in the above constraint will also prevent from a single allowed character or double character to be entered. This omission has been corrected by David Grimberg. The correct regular expression should be:

^[^[:space:]](.*[^[:space:]])?$

To test the constraint, the following insert statement were used.

   insert into test values ('hello');
   insert into test values ('hel lo');
   -- Not allowed:
   --   starting with a space
   insert into test values (' hello');
   --   ending with a space
   insert into test values ('hello ');
   --   just a space
   insert into test values (' ');
   --   multiple spaces
   insert into test values ('   ');
   --   Tab
   insert into test values (chr(9));
   --   Line feed
   insert into test values (chr(10));
   --   Carrige Return
   insert into test values (chr(13));

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use my all time favourite demo data: the EMP table.

SQL> select ename
  2        ,sal
  3    from emp
  4   order by sal desc
  5  /

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
Widlake          1250
ADAMS            1100
JAMES             950
SMITH             800

14 rows selected.

As you can tell from the output above, KING has the highest salary and FORD and SCOTT have the same salary which is the second highest.

If you wanted to write a Top N query before Oracle database 12c, let's say the Top 2 of most earning EMP, you would probably have written something with an inline view.

SQL> select ename
  2        ,sal
  3    from (select ename
  4                ,sal
  5            from emp
  6           order by sal desc
  7         )
  8   where rownum <= 2
  9  /

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000

This query might do the job most of the time, but the results might not be what you were looking for. In this case the requirement is "the Top 2 of most earning EMP", SCOTT and FORD should have both been in the results as they have the same salary.
To resolve this, you would have to rewrite your query using an Analytic Ranking Function in the inline view:

SQL> select ename
  2        ,sal
  3    from (select ename
  4                ,sal
  5                ,rank() over (order by sal desc) rn
  6            from emp
  7         )
  8   where rn <= 2
  9  /

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000

Using the new Top N syntax in Oracle database 12c, the query is a lot easier to understand.

SQL> select ename
  2        ,sal
  3    from emp
  4   order by sal desc
  5   fetch first 2 rows with ties
  6  /

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000

On line 4 the results are sorted based on the salary (highest on top) and line 5 instructs to get the first two rows. Because of the addition "with ties" both SCOTT and FORD are shown in the results.
To see what happens under the covers, an explain plan is created for this query.

SQL> explain plan for
  2  select ename
  3        ,sal
  4    from emp
  5   order by sal desc
  6   fetch first 2 rows with ties
  7  /

Explained.

SQL> select *
  2    from table (dbms_xplan.display())
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    14 |   644 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |      |    14 |   644 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   154 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   154 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rank"<=2)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SAL") DESC )<=2)

16 rows selected.

The output above shows that the Analytic Function RANK is used.

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):

What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If i multiply this by 42 it would amount to just 99.96. What if want to spread the .04 difference on 4 records, not just the last record. In effect i'll be having 4 records with 2.39. Right now i'm doing this via cursor. Im kinda hoping i can do this using sql or analytic functions

Let's create a table first, called T:

create table t
as
select rownum id
  from dual
 connect by level <= 42

In order to determine the number of rows, we need three pieces of information:

1. The amount that we need to divide
2. The total number of rows in the set
3. The difference between the rounded amount and the amount that we need to divide

 select id
      ,100 amount
      ,count(*) over () entries
      ,round (100 / count(*) over (), 2) rounded
    from t

To calculate how many rows will have the extra cent added or subtracted, the following formula is used.

abs (amount - (entries * rounded)) * 100

When you want the rounding done on the "first" rows, a simple CASE expression can be used to mark the rows

case 
when rownum <= abs ((amount - (entries * rounded)) * 100)
then 'x'
end as indicator

The query now looks like the following, with the first ten rows:

with amounts
as
( select id
      ,100 amount
      ,count(*) over () entries
      ,round (100 / count(*) over (), 2) rounded
    from t
)
select id
      ,amount
      ,entries
      ,rounded
      ,case 
       when rownum <= abs ((amount - (entries * rounded)) * 100)
       then 'x'
       end as indicator
 from amounts
;
 ID     AMOUNT  ENTRIES    ROUNDED F
---------- ---------- ---------- ---------- -
  1   100       42       2.38 x
  2   100       42       2.38 x
  3   100       42       2.38 x
  4   100       42       2.38 x
  5   100       42       2.38
  6   100       42       2.38
  7   100       42       2.38
  8   100       42       2.38
  9   100       42       2.38
 10   100       42       2.38

The last piece of the puzzle is to determine if we need to add or subtract the cent. Using the SIGN function is an easy way to determine this.

sign (amount - (entries * rounded)) as pos_neg

Putting everything together will give you the following query (with the first 10 rows)

with amounts
as
( select id
      ,100 amount
      ,count(*) over () entries
      ,round (100 / count(*) over (), 2) rounded
    from t
)
,indicators as 
(
select id
      ,amount
      ,entries
      ,rounded
      ,case 
       when rownum <= abs ((amount - (entries * rounded)) * 100)
       then 'x'
       end as indicator
      ,sign (amount - (entries * rounded)) as pos_neg
 from amounts
)
select id
      ,rounded +
       (nvl2 (indicator, 0.01, 0) * pos_neg) final_amount
  from indicators
;
 ID FINAL_AMOUNT
---------- ------------
  1    2.39
  2    2.39
  3    2.39
  4    2.39
  5    2.38
  6    2.38
  7    2.38
  8    2.38
  9    2.38
 10    2.38

And there it is. The rounding is divided over the first four rows.

Don't want to use the first rows, but the last rows instead? Use the following expression to set the indicator

case 
when rownum >
 case sign ((amount - (entries * rounded)))
   when -1 then entries - abs ((amount - (entries * rounded)) * 100)
   else entries - (amount - (entries * rounded)) * 100
   end
then 'x'
end as indicator

Links

• Rounding Amounts, the missing cent
• Rounding Amounts, the missing cent: with the Model clause
• ABS function
• SIGN function
• NVL2 function

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

• Oracle Docs: Temporal validity
• Temporal validity, multiple on one table.
• Temporal validity, part deux

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.
   

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

• Rounding Amounts, the missing cent

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

• Identifying the last record

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
      is
         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
      is
      begin
         $if constants_pkg.version like '1%'
         $then
            dbms_output.put_line ('string, with LIKE comparison');
         $end
         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:

Errors for PROCEDURE CONDITIONAL:

LINE/COL 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
3. DBMS_DB_VERSION

Deadlock with a Virtual Column

Update: There is already a bug filed for this issue, it is registered under number: 22591494

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  /

   BANNER
   ----------------------------------------------------------------------
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
   PL/SQL Release 11.2.0.2.0 - Production
   CORE 11.2.0.2.0 Production
   TNS for Linux: Version 11.2.0.2.0 - Production
   NLSRTL Version 11.2.0.2.0 - 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> /

   TRACEFILE
   -------------------------------------------------------------------------------------------------
   /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3791.trc
   

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.

Links

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

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:

• CUST_FUNCTION_TEMPLATE
• CUST_PROCEDURE_TEMPLATE

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:
#procname#
The name of the procedure or function.
#docarguments#
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.
#logarguments#
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:

loggerutil.reset_default_templates;
   

You can find the LoggerUtil project on Github.

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

      'ABC/FDF,RET/YRT,UYT/ERT'
   

The expected outcome would be

      ABC
      FDF
      RET
      YRT
      UYT
      ERT
   

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> 
      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  ;

STR
-------
ABC/FDF
RET/YRT
UYT/ERT
   

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   ;

   SPLIT
   ----------------------------------
   ABC
   FDF
   RET
   YRT
   UYT
   ERT

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.

Links

• Part One
• Part Two

Refresh Multiple Materialized Views in One Go: No Data Found

To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).

The procedure that I initially wrote was the following:

create or replace 
procedure refresh_mviews
is
   l_mviews dbms_utility.uncl_array;
begin
   l_mviews(1) := 'ABC_MV';
   l_mviews(2) := 'DEF_MV';
   l_mviews(3) := 'GHI_MV';   
   dbms_mview.refresh (tab => l_mviews);
end refresh_mviews;
/

On line 4 a local variable is declared on the type DBMS_UTILITY.UNCL_ARRAY. The declaration of this type is

TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

On lines 6 through 8 the array is filled with the names of the Materialized Views that I want to refresh.
The actual refresh is done on line 9.

When executing the code above, the following exception is raised:

Error report -
ORA-01403: Geen gegevens gevonden.
ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 2809
ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 3025
ORA-06512: in "ALEX.REFRESH_MVIEWS", regel 13
ORA-06512: in regel 2
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Strange...

After some googling I found some old documentation (from Oracle 9i) describing the functionality of the REFRESH procedure in the DBMS_MVIEW pacakge:

If the table contains the names of n materialized views, then the first materialized view should be in position 1 and the n + 1 position should be set to NULL.

This explains the exception that is being raised.

Adding line 9 in the code below fixes this problem:

create or replace 
procedure refresh_mviews
is
   l_mviews dbms_utility.uncl_array;
begin
   l_mviews(1) := 'ABC_MV';
   l_mviews(2) := 'DEF_MV';
   l_mviews(3) := 'GHI_MV';   
   l_mviews(4) := null;
   dbms_mview.refresh (tab => l_mviews);
end refresh_mviews;
/

Documentation

1. Oracle 9i Documentation
2. Oracle 12c Documentation