09 January 2018

JSON_TABLE and the Top 2000

Oracle database 12c provides native JSON parsing. It is relatively easy to go from data in a JSON format to a relational representation.
In this example I will use data from the Top 2000. Every year in The Netherlands a playlist is produced by popular vote and fully broadcast between Christmas and the last day of the year, with the number one being played just before the New Year. You can see the complete list of 2017 on the official NPO website.
The Top 2000 list that I'll use for this example is the one from 2017.

The data from the JSON file looks like this:

Only the first part of the JSON file is shown, with the first two songs.
At the highest level there are three name-value pairs ("success", "message" and "messages") and an array named "data".
The "data" array contains another array with JSON objects containing information about the songs.
Each of these JSON objects contain name-value pairs, with very short none descriptive names, like "s" and "a". You might guess what these names would mean.
Even though the "data" attribute is a nested array, at the top level it is only one level deep.
The complete JSON-file can be downloaded by following this link.

At first I want to get the JSON file into the database, before I extract the values that I need.
First create the table and a check constraint to make sure that is JSON.

create table temp2000 
(complete_list clob);

alter table temp2000
add constraint list_is_json check (complete_list is json)
This table will hold the complete JSON file containing the Top2000. The check constraint on the column will verify that only correctly formatted JSON is allowed.
Now add the JSON-file to the table, the easiest way to do this is by adding a row using SQL Developer and copy-paste the complete JSON.

What I want to extract from the complete JSON file is the information about the artist, songtitle, release year, current position in the list, as well as the position in the list from last year.
My guess would be that the "a", "s", "yr", "pos", "prv" are the attributes that I need.
With a CTAS (Create Table as SELECT) and the JSON_TABLE operator I can transform the JSON to relational representation.

create table top2000
select songtitle
  from temp2000 t
      ,json_table (t.complete_list format json, '$.data[0][*]'
         columns (
           songtitle     varchar2(150) path '$.s'
          ,artist        varchar2(150) path '$.a'
          ,release_year  number path '$.yr'
          ,curr_position number path '$.pos'
          ,prev_position number path '$.prv'
Because the song information is contained in the "data" array, and only in the nested array, I need to address that array as follows:
Zero (in square brackets) representing the first array in the "data" attribute, and a wildcard (the asterisk) to address the other objects in the nested array.

To help with the discovery of the structure (and the paths to the values) of the JSON, Oracle Database 12c Release 2 introduced the JSON_DATAGUIDE function. Unfortunatelly I don't have Release 2 at my disposal right now, so I leave that for a later time.


10 October 2017

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


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

01 June 2017

Wanted New Speakers

Yesterday there was an email coming from the UK Oracle User Group (UKOUG) about their upcoming Tech conference at the end of the year.
There was a call to action to get more new speakers, but with a twist. This email wasn't directed at everybody, but specifically to people who have presented before at a UKOUG event.
Why would you sent an email to look for new speaker and only address the people that "have done it before"?
The call to action was basically: "reach out to new speaker by offering to do a joint presentation".

Why and How?

Now where did that idea come from?
A while ago I read a blog about the APEX Connect 2017 conference, it was posted anonymously and in German (Thank you Google Translate). Some parts of the blog were very positive, but there was also a critical note about the number of new speakers at the conference.
How the agenda was put together for the APEX Connect 2017 conference is up to the conference committee, there is only so much you can do about that - like providing feedback.
But that blog did get me thinking... I know that a lot of user groups really want to have new speakers, I also know that there are a lot of people out there that have a story to tell.
When I talk to people, at conferences, on the work-floor, anywhere and ask them about doing a presentation a lot of the same answers can be heard: "What I do is very simple" "I wouldn't know how to write an abstract" "I've got nothing to say" "so and so is an expert in that field, I've got nothing to add to it" "Nobody will show up" etc. etc. etc.
After a brainstorming session with Chris Saxon (while lunching at the RigaDevDays conference) and Jan Karremans (while waiting at the airport) I came up with the idea of a joint-presentation. Get a new speaker hooked up with a more experienced speaker and let the magic happen.
What will you get out of it as a first time speaker;

  • You don't have to talk the whole slot, you only have to do half of the talk...
  • Did you get a question that you don't know the answer to? There is someone else on stage that might know the answer.
  • Get pointers for writing an abstract
  • Get pointers for putting together a slide deck
  • Get pointers for telling the story
  • ...
The more experienced speaker can help with the above, and perhaps more.

Act now

Doing a joint presentation is a lot of work, it takes serious effort to put it all together. When you've done a presentation before you know this, and because of this I was pleasantly surprised to see so many speakers reaching out.
Alright, so if you've ever considered doing a (joint) presentation about SQL, PL/SQL and/or APEX - get in touch.

28 March 2017

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

1 row created.

insert into tasks

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.


20 March 2017

Generate Rows based on a Column Value

My requirement is the following:

In a database table the activity, start time and the number of repetitions is stored, but for the report this needs to be expanded. The number of repetitions dictates the number of rows per activity, each incremented by five minutes from the start time.
To get things going a simplified table is created, as well as some sample data.
create table activities
(activity      varchar2(10)
,activity_date date 
,no_of_reps    number

insert into activities values ('X', to_date ('20-03-2017 12:00', 'dd-mm-yyyy hh24:mi'), 2);
insert into activities values ('Y', to_date ('20-03-2017 12:30', 'dd-mm-yyyy hh24:mi'), 4);
insert into activities values ('Z', to_date ('20-03-2017 13:00', 'dd-mm-yyyy hh24:mi'), 3);


The sample data looks like the following output; Activity X was started at 12:00 o'clock and repeated twice; Activity Y was started at 12:30 and repeated four times; Activity Z was started at 13:00 and repeated three times.

select a.activity
      ,to_char (a.activity_date, 'dd-mm-yyyy hh24:mi') start_time
  from activities a

---------- ---------------- ----------
X          20-03-2017 12:00          2
Y          20-03-2017 12:30          4
Z          20-03-2017 13:00          3

New in Oracle 12c is the LATERAL join making it very easy to generate the required number of rows for each row in the table.

select a.activity
      ,to_char (a.activity_date, 'dd-mm-yyyy hh24:mi') start_time
  from activities a
      ,lateral (select 1 from dual connect by level <= a.no_of_reps)
---------- ----------------
X          20-03-2017 12:00
X          20-03-2017 12:00
Y          20-03-2017 12:30
Y          20-03-2017 12:30
Y          20-03-2017 12:30
Y          20-03-2017 12:30
Z          20-03-2017 13:00
Z          20-03-2017 13:00
Z          20-03-2017 13:00

 9 rows selected 
In the example above the LATERAL join with the "Connect By Level" trick is used to generate rows.

For the last part of the requirement, increment the time with five minutes for each repetition, the Analytic Function ROW_NUMBER is used.
The ROW_NUMBER (minus one) is multiplied by a five minute interval which is added to the original start time of the activity.

select a.activity
      ,to_char (a.activity_date, 'dd-mm-yyyy hh24:mi') start_time
      ,to_char (a.activity_date 
                 + (row_number() over (partition by a.activity_date
                                           order by null
                                      ) - 1)
                 * to_dsinterval ('0 00:05:00')
               ,'dd-mm-yyyy hh24:mi') as calculated_time
  from activities a
      ,lateral (select 1 from dual connect by level <= a.no_of_reps)

---------- ---------------- ----------------
X          20-03-2017 12:00 20-03-2017 12:00
X          20-03-2017 12:00 20-03-2017 12:05
Y          20-03-2017 12:30 20-03-2017 12:30
Y          20-03-2017 12:30 20-03-2017 12:35
Y          20-03-2017 12:30 20-03-2017 12:40
Y          20-03-2017 12:30 20-03-2017 12:45
Z          20-03-2017 13:00 20-03-2017 13:00
Z          20-03-2017 13:00 20-03-2017 13:05
Z          20-03-2017 13:00 20-03-2017 13:10

 9 rows selected 


03 January 2017

APEX: Display the Page Alias on every page - without modifying the Template

Having a Page Alias shown on the page can make communication with your end users a little bit easier. Instead of directing them to the URL and asking them for the second value shown after the "f?p", it is a little bit simpler to direct them to the location of the Page Alias, e.g. bottom left corner of your screen.
For my projects I tend to use the Page Alias as a link between my APEX front-end and my database code back-end.
Until now I always used an Application Item, a Computation and a change to the Page Template to display the Page Alias. Using this method would require changes to the Page Template, which was not a big deal.
In order to make changes to the Page Template you would have to copy the template or make changes to the Master Template. I haven't tried the latter, but there is a risk with copying the Page Template:
Unsubscribed templates are editable and will not get overwritten by a theme refresh.

Going through the Oracle APEX documentation, I stumbled upon a Substitution String that I haven't seen before, APP_PAGE_ALIAS. I don't know when this was introduced, but it makes it a bit easier to include the Page Alias on the page. My first thought was that I could use this in the Page Template, but that would still mean that I would have to copy the Page Template and unsubsribe it from the Master Template.
Placing the APP_PAGE_ALIAS Substitution String in the Version Attribute (at Application Level, under Edit Application Definition) would overcome this. It will display the Page Alias on every page, right next to the release number of the application.
No changes to the Page Template, no Application Item, no Computation... easy peasy.


Like stated before: I didn't know when this Substitution String was introduced, but Peter Raganitsch does.


11 October 2016

OTN Appreciation Day: Analytic Functions

This is my contribution to the OTN Appreciation Day, which was initiated by Tim Hall.

One of my favorite features of the Oracle Database are Analytic Functions. They were introduced with Oracle Database 8.1.6 Enterprise Edition, and have been in the Standard Edition since version 9.

With analytic functions you can add inter-row calculations, aggegrate over multiple dimensions, rank assignments based on values. All this without a GROUP BY clause.
The syntax might take some getting used to, but you'll find that it's not that hard.

While I was still working at AMIS, I followed their 7UP training (all new features starting from Oracle 7 onwards) and that's when I first learned about Analytic Functions.
A little bit after that training, while doing consulting work for T-Mobile, I was triggered by a colleague to come up with an analytic function solution to the problem at hand. That was the moment that I really fell in love with Analytic Functions. It provided a very elegant and extremely performant solution.
The blog I wrote back then is still available at the AMIS-site: Analytic Power