Application Express 18.1 Early Adopter 2 contains an important feature of the new REST Service support: The ability to specify Post Processing SQL.
The previous blog postings Application Express Early Adopter: REST Services! and Application Express Early Adopter: More than one REST Service! describe how to create a Web Source Module referencing an external REST service and how to use it in APEX components like reports, charts or the calendar.
This posting will describe how to leverage the new Post Processing section for a Web Source Module in Page Designer. First, create the Github Repositories Web Source Module, as described in the Application Express Early Adopter: REST Services! posting.
Now create a new page within your application - choose a Blank Page for now. Open Page Designer and drag a new Classic Report region onto your page.
Now comes the interesting bit: On the property pane (right side), look up the Post Processing section, then pick SQL Query.
Application Express will pre-populate the SQL Query text area with a query selecting all web source module columns. Note the special placeholder #APEX$SOURCE_DATA#, which represents the data coming from the web source module. You can now change that SQL query as you wish ... first, we'll use it just to reduce columns ...
Run your page - and you'll only see the subset of columns being displayed in your report.
Using column aliases, we'll be able to use one of the predefined Report Templates provided by Application Express.
select apex_string.get_initials(name) as card_initials, name as card_title, to_char(created_at, 'YYYY') as card_subtitle, language as card_text, description as card_subtext from #APEX$SOURCE_DATA#
After that, choose the Cards report template and configure Template Options.
The resulting page will look similar to the following.
Have a look into the That's a Classic Report? Really? blog posting, to learn more about the report templates Application Express provides out of the box, and how to configure them. The Post Processing SQL query allows you to use all these templates for REST services as well.
We can use SQL functions as well. The following example determines the lifetime of a repository in hours, restricts to repositories based on the Java language and uses a CASE expression to determine an icon, based on the last update on the repository.
select apex_string.get_initials(name) as card_initials, name as card_title, extract(HOUR from (systimestamp - created_at)) + extract(DAY from (systimestamp - created_at)) * 24 || ' hours ago' as card_subtitle, language as card_text, case when updated_at > sysdate - 30 then 'fa-pencil-square-o' else 'fa-square-o' end as card_icon, description as card_subtext from #APEX$SOURCE_DATA# where upper(language) = 'JAVA'
Then navigate to the Template Options of the Cards report template and change the Icons option from Display Initials to Display Icons. The result will look as follows:
And you can do even more. In the post processing SQL query you might want to join to another table, which is possible, of course. The Sample REST Services packaged application contains an example for this.
Keep in mind, that for REST services returning their results in pages (like ORDS services do), APEX needs to fetch all pages when a post processing query is used. This is because the post processing query might contain ORDER BY clauses, aggregate functions or analytic (windowing) functions. In order to process these, Application Express must be able to access all rows from the REST service.
So, use post processing only for REST services returning a reasonable amount of rows - for a service returning thousands of rows, performance will suffer. Consider buffering those services to local tables using the new APEX_EXEC PL/SQL package. However, this will also become unfeasible beyond larger result set size.
The Post Processing feature for an APEX component based on a REST service lets you convert REST service data into any format you'll need for display. That provides great flexibility when it's about using external REST services within an APEX application.
Back to blogs.oracle.com/apex