Sunday 26 June 2016

Have fun #KScope16

Full and utter credit goes to the gang at CommitStrip, you should check it out.

I thought a few choice replacements would help Joel Kallman and team rip into what I'm sure will be another stellar set of Sunday Symposium in Chicago.

Credit to CommitStrip, text modified from original

We'll be listening around the world, keep your tweet on.

Tuesday 21 June 2016

Hide nulls in Value Attribute Pairs report

If you have one record where you want to display multiple columns of information, the 'Value Attribute Pairs - column' report template is pretty nifty.

Some of the packaged applications use this within the breadcrumb bar, above a region display selector, and it looks really tidy.

Nulls shown with tilde

Note, I've modified region attribute setting 'Show null values as' to a tilde (~).

But what if I wanted to hide those null values for Mgr & Comm, similar to the 'show nulls' option within single row view of Interactive Reports?

Create an 'after refresh' dynamic action on the region. Conveniently, this is the default when doing so via the Page Designer.
Then create a JavaScript action with the following code.
// for each value cell found on the page, determine if contents = ~, then hide row if true
$('dd.t-AVPList-value').each(function() {
  if ($(this).text().indexOf('~') > 0)
Save, then refresh your page. Done.

Nulls hidden
And if you have another event that refreshes the region, the JavaScript will re-apply and hide any nulls (represented as a tilde - something to find and hide).

The dd.t-AVPList-value is a selector for the Universal Theme. It uses a different class in other themes, so you would have to investigate using Inspect Element browser tool to check.

Right-Click -> Inspect Element

That's thinking with my jQuery hat. I have a new post in the works that uses a SQL solution - no post-render tinkering.

Wednesday 15 June 2016

Charting Predictions, al a AskTom

The 'grand algorithm' favoured this particular tweet from Connor McDonald in my 'highlights'.
I found this intriguing considering a side project I've been tinkering on. The solution ended up looking much simpler than a model clause, though I'm going to need to let it digest for a while before I fully understand how it works. Maybe read the relevant documentation.

Trouble is, the final result isn't the sort of thing you want to print out on your dot matrix printer to show the big cheese, right? You want to graph that sucker.

So I create a page in Oracle APEX 5.1 ( and created a line chart.

I split the results into two series using Connor's query within an inline view, in part because that was the fastest way I could think to get the graph plotted
select dte
 ,sz as actual
from (
with reg as (
    select regr_slope(sz, to_number(to_char(dte,'j'))) slope,
           regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt,
           max(dte) nxt
  from t
  select t.*, 'actual' tag
  from   t
  union all
  select nxt+r dte,
  from reg,
       ( select rownum r
         from   dual
         connect by level <= 30 )
  order by 1
where tag ='actual'
The result was compelling.

A picture paints a thousand words

I also like the fact the declarative series name honoured, rather than a double quoted "Column Alias".

It will be interesting to see how this plots more varied data, considering the data variance issues Connor described.

I added a to_char(dte, 'DD Mon YYYY') to get a prettier tooltip, but interestingly, it changed the angle of the line.

TO_CHAR() added

Then I spotted this field here, I could use the query as supplied by Connor without having to think about pivoting the result to get it into the right format the engine requires.

A new declarative option

However the result didn't come out quite right. I think that's more an issue with the communication between APEX and OracleJET that anything else, ie: a bug.

Not quite right

It appears the declarative charting options have been engineered in 5.1 to reduce the amount of hoop jumping with the result set. Charting in general appears more declarative, easier to navigate and work out what's going on.

Nice work APEX team.

Tuesday 7 June 2016

Synchronous Dynamic Actions in APEX 5.1

If you've ever used a PL/SQL dynamic action with the default 'wait for result', you would have seen the following warning if you have the browser console open.

Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.

Consider this scenario of dynamic actions on change of P42_ITEM:

Synchronous vs Asynchronous server calls
First JavaScript takes value of P42_ITEM, concatenates a letter and places result in another field. Let's say I entered "X":
$s('P42_NEW2', $v('P42_ITEM')+'a');
The value in P42_NEW2 would be "Xa"

The PL/SQL code concatenates another letter, passing current value of P42_NEW in and out of session state via page items to submit/return:
:P42_NEW2 := :P42_NEW2||'b';
The value will now be "Xab"

The second JavaScript concatenates another letter:
$s('P42_NEW2', $v('P42_NEW2')+'c');

On page load the value of P42_NEW2 would be "ac", since both JavaScript actions default to 'Fire on page load'.

If 'Wait for Result' on the PL/SQL action is 'yes' (default), then the communication will be synchronous and the JavaScript will only execute once the PL/SQL is finished. The final value would be "Xabc"

If the PL/SQL action 'Wait for Result' is set to 'No', then the subsequent JavaScript will execute immediately, not, um, waiting for a result. The value of P42_NEW2 would briefly be "Xa", then just "c" until the PL/SQL returns with "Xab".

Asynchronous DA result

Think about that. This impacts how you structure dynamic actions, pose questions to the user, and steer application workflow. Regression testing will be required when updating compatibility of your existing applications to 5.1.

In a tweet by John Snyders, who has posted some amazingly detailed posts on the inner workings of APEX, he suggested we can soon forget there was any other way.

I understood this to infer that synchronous actions would disappear. That screenshot is from 5.1 EA1, so it seems they're hanging around for now. It could be considered a backwards compatability thing, though it's still the default option. I'd say many applications would not work as expected should this behaviour disappear.

Synchronous activity on a website is usually non-preferrable for good UX, I understand that's inciting the change in the nature of the inherent browser behaviour (ie: the warning above). As far as I understand it, the way the APEX team has mitigated this warning is by modifying the JavaScript call to the PL/SQL callback as follows:
  ("CB_AJAX" // name of AJAX callback
  ,{x01       : $v('P42_ITEM')}
  ,{async: false // deprecated synchronous option
   ,success: function(pData) {
     // deprecated wait for result
     $s('P42_NEW2', pData + 'c');
).done(function(pData) {
  // 5.1 wait for result
  // ...
Well, this is the jQuery method for deferred callbacks. In future this will be done natively as promises. I've previously detailed some sample code on these variations.

So the end result to the developer appears to be very little, though I'm sure there were plenty of tweaks in the engine to make this happen. Though it does protect applications from when browsers do decide to no longer support the async parameter.

Edit: The end result for the user, as per John's comment, is the browser will not be locked up whilst waiting for the result. This behaviour probably will result in some behaviour differences in some applications, time will tell. Be on the lookout for it.

Actions are no longer synchronous, but you can still have JavaScript execute only after the PL/SQL finished.

Friday 3 June 2016

APEX Survey Results: Which browser for development?

Next question in my 2015 survey, how many different ways can we all type "chrome"?

Q4: Which browser do you use for development?

The first free text question, with a long variety of unique results. I haven't analysed the complete list to get a truer result, but the pattern is clear. 3 of the top 5 are Chrome. APEX developers build in Chrome, the application builder is recommended for Chrome, especially during early adopter season. I find the Chrome Developer Tools so easy to use.

Safari had a few mentions, but I guess all the Apple geeks also use Chrome.
I also wonder if Edge will emerge in future and shake off the weight of IE?

I'm currently at a client where we're lucky enough to only need to test for Chrome as we're building enterprise applications in APEX, run internally to the business.

Attempting to code across browsers is best illustrated with this GIF.