Last week I wrote about how to setup your Web Source and how to create report from it as source. Lastly, I explored the external filters and order by functions and how to use them. This time, I’m going to talk about caching of the web source and why not to use the external filters and order by.
When we created emp web source module in last article, we selected the type to be Oracle REST Data Services. For the sake of speeding the article, I went without explaining types, which are available, but let’s see at least the two basic types there are to choose from:
- Simple HTTP – Select this option for a simple HTTP data feed. Application Express assumes that all data is returned with the first request and that the server does not support server-side filtering, ordering or other advanced REST service feature. All invocation details and parameters must be configured manually.
- Oracle REST Data Services – Select this option for Oracle REST Data Services (ORDS) REST Services. These REST services follow the Oracle REST standard, which means that GET, POST, PUT or DELETE operations are standardized. Server-side filtering and ordering are supported. Application Express can leverage these features by delegating report
Reading the documentation entries like the ones above would save me several hours of figuring things out, but more about that later. The main difference between the Simple HTTP and Oracle REST Data Services is with the external filtering and order by we introduced last time (thus the reason I could not choose the Simple HTTP as web source module type).
Finally, we can get to the topic of the day – caching. If you have poor response from a server, data are not necessarily needed in real-time, or the data you are querying aren’t changing often, I’d advice to turn caching on for these kind of requests. In the example below I took the last article emp web source module and for its GET method allowed cache.
The caching options are:
- Disabled: Content is not cached and fetched from the web source for each request.
- For all users: Content is cached and utilized by all users.
- By User: Content is cached specifically for each user.
- By Session: Content is cached specifically for each session.
I’ll be treating the list of the employees as a list of values (LOV) which would be used in the application regardless of the user accessing the application. For that reason I chose to cache it for all users. Right below the caching option, you can see the box which allows you to specify under which condition to invalidate the cache (provide either the amount of minutes or a “DBMS_SCHEDULER Calendaring String”) – in the example above, it’ll be invalidated every day at midnight.
Now let’s create our employees LOV in Shared Components – List of Values. Follow the wizard, creating it from scratch and then selecting the data source as web source and our emp module.
If you want simple LOV, you won’t need external filters, or post processing. You are almost good to go after you create the LOV with the wizard. The only thing which is important to change Default Sort - Use Order By from Query. If you select any column to sort by, it’ll automatically send the External Order By statement to the server and it’ll prevent the web source to be cached, same as if you’d send the External Filter.
If you really need some filtering or ordering done on the LOV, you can use the post processing type option as shown in the example above. You have option to choose either from:
- Where Clause – simple where condition to apply some filtering
- SQL Query – you can run some complex query on top of the returned data, also you can add sorting here
- Function returning SQL – same as SQL Query above, but not written directly in here, but rather in the function, which can be then reused
In my example, I wanted to show in the LOV only employees that have letter K in their name. I could use the where clause but for the sake of testing the functionality I went with full SQL Query.
SELECT job
, mgr
, sal
, comm
, empno
, ename
, deptno
, hiredate
FROM #APEX$SOURCE_DATA#
WHERE ename LIKE '%K%'
This way you are effectively filtering the web source response data, but locally in your own database enabling the remote data to be cached.
Now, when we reached the end of the article, I have to admit one thing. I only worked with the small data size. It wasn’t paginated and I’m not sure, how this will behave if you have e.g. two set of 25 results to be return back from the remote server. I need to experiment with this more, so you might get even more articles on Oracle APEX Web Sources.