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. This is the default when doing so via the Page Designer.
// 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)
    $(this).hide().prev().hide();
});
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

Thinking with my jQuery hat.

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 (apexea.oracle.com) 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,
         trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
         ,'predicted'
  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.

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:
apex.server.process
  ("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. I thought lots of APEX developers were Apple fans?
I also wonder if Edge will 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.

Coding across browsers is best illustrated with this GIF.

Thursday, 26 May 2016

APEX Survey Results: What development resources?

The next question in my 2015 survey was a high level look at what resources developers use to get through your day.

On a slightly side note, there was an interesting discussion on the science of preferred vs effective learning styles in this podcast. It reminded me of my scuba diving course where we learnt the content using 5 different methods, which was a great way to ensure everyone understood how to survive in a pressure environment (pun intended).

Q3: What resources do you use to aid development



Personally I find the OTN forums great for most things APEX or database related. StackOverflow is great for JavaScript/jQuery/CSS questions, and most have already been asked for you. The forum format there is superb. Don't forget the revitalised AskTom.

It's great that some people have the opportunity to learn from colleagues. I've had some great mentors in the past that really helped my career. Blogs can be a moderate second, with APEX content aggregated at www.odtug.com/apex.

Documentation is a great source of truth, and I recommend four places. Sense a trend?


Some of the "Other" responses included Twitter, which is a great tool for picking up information coming out of conferences you can't attend, tech information in general, and amusing parody accounts. Slack is also worth a go.

One interesting response was "Package Specs, Views, Application Code". I often find the package specifications of Oracle Supplied Packages full of useful information not necessarily found in the documentation. I recommend you start with APEX_APPLICATION and APEX_APPLICATION_GLOBAL.
As for views, I've learned a thing or two an how APEX ticks by looking at the SQL for dictionary views, which also explains why many don't return any records when you aren't within an APEX application.
I'm curious what was meant by application code, perhaps the same as another response that said "Packaged Applications". These applications improve every release and are a great demonstrator of APEX capability. Check them out for application design ideas, and if you want to know how something was done, install (and unlock) the application and look under the hood. This is where I learned how to display images in interactive reports.

Webinars was another suggestion, which I guess is a subset of Videos. I recommend paying for membership at ODTUG where they have a brilliant library of webinars, of which I have many yet to catch up on.
These days videos are quite diverse, from quick and simple youtube demos like Connor McDonald's KISS series on analytics; to published screencasts like I did once.

Someone even suggested "Connor McDonald", though I think there was some bias in that one.

My final recommendation would be regardless of preferred source, diversify your content. Don't just read Oracle APEX stuff, but subscribe/buy/view content for related technologies, and why not the competition?

Wednesday, 25 May 2016

More on CSS selector performance in Oracle APEX

Last month I wrote a post about CSS performance, including some performance test results.

I recently encountered this brilliant post on Medium that describes some best practices for CSS.

While APEX does a lot of this for you, I think it's worth a read by all developers. Even applying basic naming conventions can make code easier to read and understand.

There was also a section on performance that described an issue that my previous example didn't explore, and that's 'tree walking'. Each web page could be likened to a giant tree, and your CPU will like you if you can minimise the amount of traversing necessary to verify the component being sought.

So for APEX developers, consider the selector reading right to left, and try keep it to two steps.

For instance, to identify a button within a classic report, you would define a static region ID for the report, and use the following selector
#p1_region a.t-Button

This would look for anchors with the standard Universal Theme button class, but only if it exists within the classic report region. Concise & effective.

You could use this selector to apply further CSS attributes to the buttons, perhaps via the Inline CSS page attribute.
#p1_region a.t-Button { font-weight : bold; }

Or you could use it as a jQuery selector for an on-click Dynamic Action, responding to the button press. If you add the following as link attributes to the column with the link button. It adds an attribute to the anchor with information from a column aliased as season in your SQL.
data-season="#SEASON#"
Note, to render a column link as a button, this attribute would also contain this class listing, to look like the nearby image.
class="t-Button t-Button--warning t-Button--simple t-Button--large t-Button--stretch"

You can refer to this information within a JavaScript action to set a hidden page item with the data from the season column.
$s('P1_SEASON', $(this.triggeringElement).data('season'));

A subsequent PL/SQL action could then submit this value to session state and execute something related on the database, without submitting the page on click of the report row button.

While that has side tracked from selector performance, it shows how a simple selector can be used within APEX to trigger events. A pattern I use regularly, done with minimum effort.

If you like this post, you may like my book /plug

Small tip: Align button in UT

Ever had this problem where your button is offset to page items?

Item / Button misalignment

While the Universal Theme is awesome, it hasn't quite got everything right. I look forward to trying the updated version of the theme in our existing applications.

APEX forum legend fac586 (a.k.a. Paul MacMillan) provided a simple solution here.


Add t-Form-inputContainer to the Column CSS Classes property for the button.

Little tips like this can be acquired just by occasionally checking out the recent responses in the forums, perhaps opening anything of interest that might pertain to your work.

I think this tip should be suffixed with this funny
https://pics.onsizzle.com/rob-ybu-know-css-come-quick-to-disarm-the-bomb-2466256.png

Modified from the xkcd original
https://xkcd.com/1168/