Welcome to the Beyond Blog

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.



The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.

Deferred Query Region Selector in Oracle APEX

Whilst working on a client project recently I created a page in APEX with a number of different regions, selectable via a Region Selector. The way this page was to be used, the user may not always want to click on every single region each time they use the page. Unfortunately the default behaviour with APEX is that all regions are rendered on the page at load time, so if some queries take a short while to run then your user is waiting for data to return that they aren't even going to use.
What I really wanted was for the code in the region to only actually run when the user chose the region from the selector. I even posted on the OTN Forums to ask if anyone had done similar in the past. Ultimately though it seemed nobody has, so I thought I'd give it a try.

To explain the concept first, this idea works by creating a page item which is checked in a query predicate. i.e.

Select *
  From my_table
 Where :p1_show_data = 'Y';

We take advantage of the fact that the optimizer can deduce that if :p1_show_data does not equal 'Y' then the query is going to return all rows.
By exploiting that, we can devise a solution where our region queries check the value of an item which is empty when originally rendered, however is populated when the region is refreshed - and we trigger a refresh of the corresponding region when the user selects a tab.

Here is how I achieved this (note - demo done in APEX 5.1.2, however should be backwards compatible across at least APEX 5.x).

First we create a hidden page item that restricts the queries. I created one called P1_SHOW_REGION. Then we modify our queries to take advantage of this.

select * from table(delay_table(5))  where :P1_SHOW_REGION is not null

My delay_table is simply a pipelined function that takes a value and waits that number of seconds to return values - it lets me test the report regions by simulating a long-running query. I'll post the code in the comments.
Next I created a before header process which resets the session state for the item - ensuring it is blank when the page loads.

Blank Item

Then I created a computation firing after regions which sets the value to "Y" - so the value is set in the session state ready to be used by our region refresh process.

Set Value

Finally I created the following JavaScript snippet and added this to the Execute When Page Loads section of the page definition.

var regionSelectorShown = new Array("Empty");

$(".apex-rds").data("onRegionChange",function(mode,activeTab) {
  if (typeof regionSelectorShown[activeTab.href] === 'undefined') {
    regionSelectorShown[activeTab.href]="Y";
    $(activeTab.href).trigger("apexrefresh");
  }
});

Execute on Page Load

So what is that doing? We are adding a callback on the onRegionChange event, which when triggered adds the name of the region (activeTab.href) to the array regionSelectorShown. This is purely so once we have shown the region within a page, we don't re-execute the query again if the user tabs out and back in again. Then we call the apexrefresh trigger passing in the region name to the jQuery selector - this causes a partial page refresh (PPR) of the region - which now sees the value of :P1_SHOW_DATA as "Y" and thus executes the query in full.
Now we see when we load the page, the region shown first fires the callback and shows initially. Then as we click through other regions, we get the processing icon (whilst waiting for the data to come back via my delay_table function) and the region shows. If we tab out and back in again, we don't re-call the refresh process as the regionSelectorShown array has a value indexed by the region ID indicating we have already shown it and so don't need to again.

Region Loading

 

Region Loaded

As always, there is always room for improvement and extension of this - if you do so then I'd really appreciate it if you could drop me a line in the comments so others can benefit. It would be nice (and I'd have thought relativly easy) if this kind of functionality was considered for inclusion in the standard APEX build as a feature in future releases.

APEX Organization/Position Chart Plugin (Google AP...
Data Visualization Version 3
 

Comments 1

Guest - Mike on Monday, 11 June 2018 22:37

Good stuff. An application I had was fairly sluggish and I was looking for a way to speed up the initial page load. This helped tremendously by not waiting on all report queries to run. I had a problem with a couple of classic report regions which were not displaying any report data, and finally realized that I did not have partial page refresh set to 'Yes'. So check that if your query is not returning the expected rows.

Good stuff. An application I had was fairly sluggish and I was looking for a way to speed up the initial page load. This helped tremendously by not waiting on all report queries to run. I had a problem with a couple of classic report regions which were not displaying any report data, and finally realized that I did not have partial page refresh set to 'Yes'. So check that if your query is not returning the expected rows.
Already Registered? Login Here
Guest
Friday, 22 June 2018

Demonstration

Request a demo of our products here

REQUEST DEMO

Contact

Beyond Systems Limited

Barnett House, 53 Fountain Street,

Manchester M2 2AN United Kingdom

 

 

Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk