Analytics in APEX Charts - Moving Average

Consider a chart with a trend that might be quite jagged across data points (blue line).

What if you would like a smoother version of that line - a moving average, if you will (red line). This stabilises the results, like looking at climate vs weather.

Oracle APEX Line Chart - 2 series

It's fairly easy from a SQL point of view - in this case it's another column in the original chart query

SELECT null lnk
  ,diff   label
  ,count(*) AS qty
  ,round(avg(count(*)) over (order by diff rows 
                             between 3 preceding and 3 following)
        ,2) AS moving_avg
...

The third column uses an analytical function to calculate the average of the 6 surrounding counts at any given point on the x-axis. The "rows between" syntax is often left to the default, which is everything up to the current value (see post comments), based on the order provided. In this case it explicitly specifies the 'window' of rows to average to be only nearby data points.

Note this report looks a lot cleaner with display setting 'Marker' using 'None', as opposed to 'Circle'.

Nice, simple way of providing the user information that can be easier to read & interpret.

About CSS Selectors

Recently I saw a simple, accepted answer in the forums that tempted me to provide a small extension to the provided answer. This has since spawned two blog post ideas, here is the first (here is the second).

Background

The following question asked how to hide the spinner from a particular page full of small reports refreshed on a timer.
https://community.oracle.com/thread/3908020
The answer was some basic CSS, which could be placed in a variety of locations depending on desired scope.

.u-Processing 
{ 
display:none 
}

The answer is clear, but doesn't show any working. I'm sure many people out there would like to know how to arrive at that answer themselves.

About CSS

If you don't really know what the above code really means, let's start with the basics. CSS allows you to identify an element on a web page, then change one of it's attributes. The selector identifies the component, and the attributes are listed within the brackets. The described example identifies the processing spinner and hides it, but how do we build the relevant syntax ourselves?

I think CSS selectors can be likened to queries against the database. You want to identify a specific component, then change it.

You can invoke the spinner in order to identify it. A function is described in the Oracle APEX API Reference, which can be invoked on demand in your own applications.

You can try this on the login page to apex.oracle.com by opening the browser console window (usually F12) and typing
apex.util.showSpinner()

Invoke the spinner on any APEX page from the browser console

The spinner will display and you will also see output relating to the component itself. If you right click on the spinner and Inspect Element (Chrome et al) you can see more details about the component and what properties it currently has.

You can modify these properties directly and immediately see their impact on the page. You can un-check attributes to disable them, or change their value, often from a discrete list of option. Sizes can be increased/decreased with the arrow keys. I do this all the time to test sizes.

A good reference for these attributes can be found here
https://developer.mozilla.org/en-US/docs/Web/CSS/Reference

Using Selectors

You can identify the spinner using a variety of selector expressions, just like you could find a particular record in the database using a variety of where clauses. Bear in mind some will work faster than others.

Consider the provided solution using .u-Processing. The period prefix means it's looking for a component on the page with the class u-Processing, as per in the definition of the spinner.
<span class="u-Processing" ...

Note I'm referring to the parent span of the .u-Processing-spinner that's highlighted in the image. Setting this to display none will only hide the spinning icon, not the surrounding shaded circle.

If the component had an id, you could use the # prefix to reference the ID, much like using an unique* index.
(*in the web world, the an ID is not guaranteed unique, but should be in best practice)

The element tab can help in determining the required selector. The strip at the bottom shows the path, a form of which can also be retrieved by right-clicking the parent span in the HTML code and selecting 'Copy CSS path' (or Copy Selector, depending on browser version), which may provide a more 'specific' selector, but not necessarily what you need or what.

The spinner may be in different locations on the page depending on context. Performance is another issue, but that's for the next post.
Specificity is important since any given component on the web page could have attributes from a variety of sources, so there is a precedence called CSS Specificity.

Getting what you need

The selector doesn't need to be complicated, just specific. Try it out by either searching for the selector in the code window, or seeing what it returns in a jQuery command entered in the console.
eg:
$('.u-Processing')

Look for a class or ID on the web component you want to manipulate, test out it's uniqueness (so you don't hide anything you shouldn't).

.u-Processing

Then add the attributes you want to set within brackets, multiple attributes are separated by semi-colon.

.u-Processing { display : none; }

This code could then be placed anywhere within APEX that accepts Inline CSS, such as the page attribute. Or it could live in a .css file and associated with your applications.

Want More?

If you want more examples of what you can do with CSS and jQuery in APEX, you may consider my book, jQuery with Oracle APEX. </shameless_plug>

SQL Analytics in every day APEX

Looking for way to apply analytical functions to your APEX applications?

I had a classic report where I wanted to dynamically source the column headers from counts in the database (values in brackets).

The ability to do this has been a feature of APEX for a while, but this was the first time I did it in APEX 5.0.

Customise headers via Region attributes

With Connor's recent spate of analytics videos,  I thought I'd mention this use case where LISTAGG() was perfectly apt.

A basic query like this will return a grouped count.

select count(*), catgy
from some_categories
group by catgy

  COUNT(*) CATGY_CODE
---------- ----------
         5 CATGY1    
         5 CATGY3    
         1 CATGY5    
         7 CATGY2    
         1 CATGY4    
         1 CATGY6    

 6 rows selected 

But the string needs to look like
Rep:Catgy1 (2):Catgy2 (4):...

So I need to transpose those rows into a colon delimited string. Here's how you can do it with SQL analytics.

declare
  lc_hdr  varchar2(512);
begin  
  select 'Rep:'||listagg(initcap(catgy)||' ('||count(*)||')' -- just build a fancy string
                        ,':') within group (order by catgy) -- concatenated by ':', listed in order of catgy
  into lc_hdr
  from some_categories
  group by catgy;

  return lc_hdr;
end anon;

An alternative may be bulk collecting the results into a PL/SQL array, then using apex_util.table_to_string(), but not when the problem can be solved with simple SQL ;p

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, not multiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations

SELECT 'SCOTT' login_id 
  ,'X' alpha, 'Y' beta
  ,10 catgy1
  ,20 catgy2
  ,30 catgy3
  ,40 catgy4
  ,50 catgy5
  ,60 catgy6
FROM dual
/

LOGIN A B     CATGY1     CATGY2     CATGY3     CATGY4     CATGY5     CATGY6
----- - - ---------- ---------- ---------- ---------- ---------- ----------
SCOTT X Y         10         20         30         40         50         60

 1 row selected

Trouble is, I needed the categories described as rows, not columns. So I wrapped the original query within an unpivot, commenting how the syntax represents the translation.

select login_id, alpha, beta, catgy, quota -- new columns
from ( -- existing query
  select 'SCOTT' login_id 
      ,'X' alpha, 'Y' beta      
      ,10 catgy1
      ,20 catgy2
      ,30 catgy3
      ,40 catgy4
      ,50 catgy5
      ,60 catgy6
  from dual
) -- end existing query
unpivot
(
quota -- new column: value
  for catgy in -- new column translating previously separate columns to discrete data
    ( -- and the column -> data translation listed here
     catgy1 as 'CATGY1' 
    ,catgy2 as 'CATGY2'
    ,catgy3 as 'CATGY3'
    ,catgy4 as 'CATGY4'
    ,catgy5 as 'CATGY5' 
    ,catgy6 as 'CATGY6'
    )
)
/

LOGIN A B CATGY       QUOTA
----- - - ------ ----------
SCOTT X Y CATGY1         10
SCOTT X Y CATGY2         20
SCOTT X Y CATGY3         30
SCOTT X Y CATGY4         40
SCOTT X Y CATGY5         50
SCOTT X Y CATGY6         60

 6 rows selected 

Relatively easy! Hope it helps one day.

You can see the results from these statements at livesql.oracle.com

Talking SQL Analytics with Connor

In about 22 hours I'll be talking with Oracle Developer Advocate (or the southern/eastern hemisphere AskTom adjunct) Connor McDonald about one of my favourite part of Oracle, being SQL Analytics.

If you haven't seen any of his YouTube videos recently, this will be a good chance to catch up and see how easy and practical SQL analytics are, and how you end up with simpler SQL.

I like to think of them like Excel calculations you can put in your result set.

Webinar registration details available here. It starts Tue, Mar 1, 2016 6:00 AM - 7:00 AM CST, or 6am for us Perth people.

Yes, my line has been tested and as long as you only hear my voice it should be cool. Connor apparently lives in a neighborhood more favourable to technology.

2015 Blog Review

Time for my look back on 2015 and what I might aim for in the coming orbit around the sun. The highlighted word is dedicated to the #flatearth people I've been conversing with on Twitter recently. Wow.

Like many people, part of the reason for doing these sorts of things really what every person should do semi-regularly -> write down your aspirations. I remember being taught goal setting from a young age and I think it's a life skill that many leave behind.

Same topics as last year, with a different order by, but they're all tightly coupled anyway ;p

Tools

Ahh, so many things to play with, so little time.

APEX

There will be a lot going on for APEX again this year. No doubt you've already read Joel's summary of 2016. Oh to live in the US/Europe (or have fast, cheap, efficient modes of transport).

Content for APEX at Kscope16 looks amazing, it's a shame it's a touch early to see stuff about OracleJET and the Interactive Grid in 5.1. That's OK, I'm still learning the full impact of life in 5.0.

I'll continue to work on Universal Theme projects, which pleases me. Plenty still to learn and absorb, adjusting to our evolving development framework. Thanks again to the development team for the Page Designer, it has vastly improved productivity.

I would like to see Share Components integrated into the Page Designer, certainly for viewing but I'm sure even editing could be cleverly integrated with modal dialog pages. I often operate with two browsers running the builder, Chrome for Page Designer; Firefox for Shared Components.

I'd like to hear more about how the Universal Theme will change, what sort of life cycle it will have, and how information about change is relayed. I know it's changed since inception, and no doubt within patches. I wonder if any tweaks fix the little issues we've been finding. On my list is to copy an app, verify the them subscription, and try it out. Maybe export the themes and do a diff?

Anyway, I've found CSS use has cut greatly since running UT, I've cut plenty from converted applications. I've even modified the balance I'm looking for between Dynamic Actions and jQuery.

I truly could rattle on for ages. My three developer buddies at work will testify to that.

ORDS

Personally I would  like to see more on ORDS. I feel related documentation is still maturing and there only seem to be a small handful of experts sharing their experiences & techniques.

As someone with a prominent development hat, I'm thankful to have a colleague that understands that middle tier better than I. Unfortunately we're a small team and setting up JMeter for load testing is still on the list.

I saw a bit of chatter online regarding mixed content issues on version 3.0.3, and performance issues after 3.0.1, both of which we experienced empirically. The ORDS team must feel what Android software authors face were there's a zillion combinations of infrastructure.

Even though ORDS is sprouting it's own wings, I think the APEX and ORDS team need to stay tightly coupled. At this point I'd guess most ORDS deployments at least serve APEX. I've already overheard the observation that most people see it as a just that, and clients don't split hairs when something goes wrong.

"APEX is down", and nothing worse than middle tier experts saying "not us" when it's certainly outside the database layer. We can't wreck this together unless the arm supporting ORDS gets more support.

That's my plea for the (fantastic) Oracle developer advocate group in 2016 - more aid for ORDS. Maybe a hashtag would help? ;p #ORDShelp

12c

Our current client upgraded to 12c over the festive period, so no doubt I'll have a few development experiences to share with this newer environment. Probably on topics related to JSON and SQL syntax. Look out for #db12c

Text Editor

For a long time I've used Textpad for my text editing needs, but I'm tinkering on the edge of leaping to either Sublime or Atom. This needs to happen this year. Eddie, get me on it!

Community

Last year I said to Juergen that I'd help out with apex.world. So far I've written the intro post for Slack, but I still haven't honoured the other task he sent my way. I could blame the book, tendinitis, the holiday period, but I know there's procrastination in there, too. Sorry mate, soon.

I doubt I'll get to any international conferences this year, but I'm going to start paying more attention to the Asia-Pacific region. I'm on the lookout for communities I'm yet to discover. That sounds like a tautology, something self evident, but let's call it a known unknown.

I'll certainly hang around on Twitter and Slack, and I might get back onto the PL/SQL Challenge. I gave it a break for a while, but I need some 12c tips. I asked Tom Connor a question recently, I might spend some time perusing the headline questions, too.

Blogging

I intend to spend a bit of time finishing all sorts of semi-drafted posts, technical and otherwise. I'm not longer writing a book and this yearn to write has to go somewhere. I still have plenty of notes from my experience at Kscope15, which was amazing. I look forward to the time where I can travel more.

I have fun when analysing the statistics from the previous year. I think many of us unknowingly love assessing numbers and statistics, which is my hypothesis as to why cricket and baseball are so popular.

Readership growth has tapered, which I find interesting. Does that mean I've hit most people out there, looking for stuff, or have I just filled up my current channels?

I must say it was fascinating at Kscope to hear about people who've read my blog or heard I was there and sought me out. Rambling on the keyboard to a faceless audience is weird sometimes, so I was thankful for the positive feedback. People compliment on my writing style, and I humbly accept and strive to improve.

Back to the stats, the most telling of which is browser usage

• Chrome up 16%
• Firefox down 11%
• IE down 23%
• Safari up 27%
• Opera down 18%

Big differences, no real surprises, is there?

Mostly read on desktop, but mobile up 64%, but that's relative, not absolute. 3k mobile readers vs 53k on the desktop. Similar relative growth for social media vs organic searches. Twitter certainly is a growing factor in disseminating (and finding) technical content.

Plenty of interest in APEX 5, 1500 visits alone from Patrick's meta post aggregating APEX 5 articles
http://www.inside-oracle-apex.com/oracle-apex-5-0-articles/

I find this curious every time but only two articles from 2015 made it into top 10 most visited. One was Sample community applications, which makes me want to progress this idea further with the gang at apex.world. Second was my reference to Carsten's listagg clob.

To add perspective, the reigning top page represents 4.7% of visits. If I interpret this correctly, the top 10 pages represent over a quarter of the year's page visits, which is quite the heuristic.



Strangest search term?
apex 3.x dynamic actions

Books

My first book is complete. What a journey that was, with Kscope and APEX 5 thrown in between. A little shame about chapter 9, more on that to come, likely in some form of errata. I'll hopefully learn from that lesson.

Anyone who has read it, thank you and I hope I can get the updated chapter to you soon. I hope any reviewers hold out until they've had a chance to read through ;p

I bought a few myself with the Cyber Monday sales that I need to start reading. A have a couple on node.js but I look forward to learning heaps in the revised Expert Oracle APEX.

I need to fit a novel or two in as well. We're have a short holiday in Bali soon, I no doubt have a good sci-fi ready to go.

Videos

As I suggested in Monty's post about my APEX game, I've considered recording little videos showing I build the game, in addition to the presentation I did for the local user group. Nothing fancy, certainly not like Connor and Tim's videos, just little tutorials that watch me work. Even little things like rebuilding my sample application in the Universal Theme.

I've also got a lot of video watching I want to do. I have a few set to go from ODTUG already, such as webinars I've missed because they've been on at 1am. I've barely seen many of Connor & Tim's yet, I just don't watch many videos, I prefer to read my content. So I've got to schedule some video watching time, certainly before Kscope16 videos flood in. They're my best alternative to being there, that and participating in the Twitter stream during the conference.

Science

As with any year, 2016 has plenty of interesting science to look out for, and I love it. I will continue to also tweet science stuff I find interesting, in addition to the usual #orclapex #db12c #jQuery hashtags.

I've been planning to post a list of my favourite podcasts for a while. For those interested I have plenty to recommend. They make great listening during transit to work. Exercise that brain.

Health

Having a toddler has introduced a lot of viruses and some bacterial infections to the house. This made exercise a little difficult, but I have managed to swim regularly with a small group of guys at work. We do a steady 1km at lunch, aiming for once twice a week.

That's awesome, but I have equipment at home I need to use more often in the morning, particularly with the amount of chocolate I eat, which has to come down!

But first, I have to wait for what's apparently tendinitis in my right hand to calm down. I've done a decent job at resting it, but maybe that leaves room to help improve mental health.

Take a walk, enjoy the sunset. Rest your eyes and let your brain do some awesome stuff in the background while you're admiring the world.

On that note, enjoy your fresh start!

Calling PL/SQL from JavaScript in APEX

When I first wrote Chapter 9 of my book, Choosing Processing Options, I knew I still had a lot of research and experimentation to do, particularly regarding the async parameter. Below is a summary of the outcome of this work and drove a full revision of my original chapter.

Last week I received a printed copy of my book and noticed that unfortunately the first submission of chapter 9 made it into print. Apress are looking into this for me, but please consider the concepts in this post refactored into what you read. It all relates to the following message you may have noticed in your browser console.

The following post is slightly modified from an email conversation considering workflow from a browser based system. There was a more detailed discussion in a forum post that I'd need to dig up.

Imagine a button on a screen to add a record that may already exist. How do you interact with the user?

--***
On 8 October 2015 at 09:05, Scott Wesley wrote:

Hi gang,

I've played with this, thought about it, and I think we're already on a good wicket with another pattern.

Option A)

On click btn ->
PL/SQL: try insert, set result to page item (eg: P0_SIGNAL)
Dynamic action on P0_SIGNAL ->
Notification: Display message based on item.

We need to shake it up a little to provide different colour message - success/failure etc.

Tom Petrus suggested a more elegant alternative (with one less step and/or didn't involve a page item?), but it was in transit time at kscope and it was a hard topic to explore without code. In revising this post, I think perhaps he set the message to some JavaScript container, which in turn is sent to the database using APP_AJAX_X01.

Option B)

On click -> test javascript condition, which actually calls a synchronous function to test existence in database
True action - PL/SQL to insert
False action - alert user

This is what we were attempting this afternoon, and sounded good in my head but there are a few things wrong.

1. We're calling the db twice, when we just need to do it once. We're in an optimistic world now: try it, work it out once it's done.
2. The web isn't built for it, and the specifications are making it harder for us to do so. A common sentiment on from MDN.

Below are the examples we attempted for the JavaScript to return true or false, hence triggering the appropriate true/false dynamic actions. Function b() represents better practice. CB_AJAX is an AJAX callback (PL/SQL) defined on the page and returns a number as a text string.

This works, is completely synchronous, but will only work in browsers for a finite time thanks to the highlighted row (async:false).

function a() {
console.log('a');
apex.server.process
  ("CB_AJAX"
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"
   ,async:false
   ,success:function(pData) {
       console.log('Result a:'+pData);
       console.log('success:'+ (result.length));
       result = pData;
     }
   }
);
return result.length == 2;
}

The next example uses 'deferred objects', which is jQuery's updated method for handling "success", but we can't access the callback values outside that scope, so we can't return true or false. The 'callback' is the done() section.

"javascript return result from callback" is an obscenely googled phrase, with a fair response of: "This is impossible as you cannot use an asynchronous call inside a synchronous method."
If that hurts your head a little, welcome to JavaScript mechanics.

function b() {
console.log('b');
apex.server.process
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"}
).done(function(pData) {
   console.log('Result b:'+pData);
   result = pData;
   console.log('success:'+ (result.length));
   return result.length == 2;
});
// anything here will run before plsql finished
}

Eddie suggested using "promises", which sounded promising. ha.
But they are just the native implementation of deferred objects. And extremely new, unsupported by even IE11.
I also think this quote is telling: "This is extremely useful for async success/failure, because you're less interested in the exact time something became available, and more interested in reacting to the outcome."

function c() {
  console.log('c');
  return new Promise(function(resolve, reject) {
    var jqxhr = apex.server.process
 ("CB_AJAX" // this is just => htp.p('X');
 ,{pageItems : '#P9_EMPNO'}
 ,{dataType:"text"}
).done(resolve);
  });
}

c().then(function(result) {
  // code depending on result
  console.log('.then');
  console.log(result);
  return result.length == 2;
}).catch(function() {
  // an error occurred
  console.log('error');
});

Then I tried letting functions accept callbacks, which really defines all logical sense I learnt in programming 101. If this doesn't screw your brain, google "doSynchronousLoop.js". I'm sure it's 21st century spaghetti code, with the added dimension of time.

function myCallback(pData) {
   console.log('Result b:'+pData);
   result = pData;
   console.log('success:'+ (result.length));
   return result.length == 2;
}
function d(callback) { // d
console.log('d');
apex.server.process
  ("CB_AJAX" // this is just => htp.p('X');
  ,{pageItems : '#P9_EMPNO'}
  ,{dataType:"text"}
).done(callback);
// anything here will run before plsql finished
}
d(myCallback);

So I think the conclusion to be drawn out of all this is that Tom Kyte is right, and always has been. Run code, if it raises an exception, let out propagate, politely let the user know; otherwise success and move on.

Anything else and you are going against the grain.

APEX provides the ability to do this declaratively with dynamic actions, stick with option A, and we'll improve the mechanism in future to incorporate a library to set/read P0_SIGNAL with a JSON string that will get converted into signal type/message and invoke Alertify as appropriate, as opposed to setting it with a single value as currently done in <redacted>.

Edit: If you've ready this post, you should also about async wait by by Vincent.

References

• http://stackoverflow.com/questions/6847697/how-to-return-value-from-an-asynchronous-callback-function
• http://www.html5rocks.com/en/tutorials/es6/promises/
• http://caniuse.com/#search=promises