X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

  • April 12, 2018

APEX 18.1 Early Adopter 2: Caching for REST and Remote SQL

Carsten Czarski
Consulting Member of technical Staff

When using a REST Enabled SQL service or an external REST service to drive APEX components like reports, charts or calendars, each page view will lead to an HTTP request being made from the APEX database to the external service.

This provides the developer great flexibility to incorporate external data sources into their applications - however, the HTTP requests will, of course, slow down page response times. How much that is, depends on network latency, bandwidth and availability of the remote service.

It's often not required to actually fetch latest data from the remote service for each page view; in practice it would be fine to cache the data for a certain amount of time. For that reason, Application Express provides a Caching facility for Web Source Modules and REST Enabled SQL Services.

For Web Source Modules, Caching is being specified in the module definition within Shared Components. The following sections will illustrate how to enable and to use caching for the Github Repositories example described in the Application Express Early Adopter: REST Services! blog posting.

Navigate to the Operations tab within the Web Source Module definition. The Github Repositories module will only contain one GET operation, for which we'll now enable caching. Caching can actually only be enabled for GET operations, because only these are defined as idempotent. All other HTTP methods (PUT, POST, DELETE, PATCH) may change data on the remote server, thus such requests cannot be cached.

Click on the GET operation in order to see the details for this operation; then look up the Caching tab.

By default, Caching is disabled. It can be enabled on the User or Session level and globally (for all users). The Invalidate When attribute determines when cached content has to be treated as stale and fresh data is to be fetched. A numeric value for the Invalidate When attribute will be interpreted as Minutes - so the cache will be invalidated X minutes after it has been fetched.

In addition to that, a DBMS_SCHEDULER Calendaring Expression can be used for the Invalidate When attribute. That syntax is described in the Documentation for the DBMS_SCHEDULER package and allows more flexible definitions for the time when the cached content is to be considered as stale. Examples:

  • FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0
    Cached content will be invalidated at midnight.
  • FREQ=HOURLY;BYMINUTE=0;BYSECOND=0
    Cached content will be invalidated at the top of the hour.

A caching configuration might look as follows:

Now create a page with a classic report using this Web Source module. In the region attributes, add #TIMING# to the Region Footer, which will print the elapsed time for this very region into the region footer. Note that #TIMING# does not work for components which use asynchronous requests to render (JET charts, Interactive Grid). 

When the page is rendered the first time, the result might look as follows:

In this case, it took almost 2 seconds to render the time. Reviewing the debug log will reveal that almost all time was spent for the HTTP request. Then refresh the page - and you'll see the same output, but it should be much faster.

In the second case, no HTTP request has been performed at all. Application Express checked for a valid cache record and found one. APEX caches the response data received from the REST service, not parsed content. So the cache will contain JSON documents returned by the REST service.

To manually invalidate the cache, use the APEX_EXEC package.

begin
    apex_exec.purge_web_source_cache(
        p_module_static_id     => 'Github_Repositories',
        p_current_session_only => false ); 
end;

The developer can use APEX_EXEC.PURGE_WEB_SOURCE_CACHE, for instance, to add a button to the page. Clicking the button will execute the above code as a PL/SQL page process. When the page is rendered again afterwards, no cached data is available, so APEX will fetch fresh data from the REST service.

The ability to enable caching for Web Source modules allows to reduce the dependency of the APEX application to the external REST service. Once the content is cached, no HTTP requests will be performed, rendering application pages significantly faster. But this is not the only use case: some REST services impose rate limits; invoking them for every page view might consume existing capacity way to quick. Once caching is enabled, requests will only happen when it's really required.

Back to blogs.oracle.com/apex