Improved REST Consumption (REST Web Source Modules)
Furthermore, when defining a REST Web Source Module, APEX will do a lot of the work I described above for you. For example, while creating the REST Web Source Module, you are presented with a preview of the web service response and you can remove unwanted fields or even add new calculated fields.
In addition to REST Web Source Modules, there are three other important improvements in the way APEX handles REST web service consumption in APEX 18.1:
- Introduction of the APEX_EXEC PL/SQL package in order to invoke REST services from within custom PL/SQL.
- Caching for REST Service results to reduce round trips to the web service. Caching can be session based, user based or for all users and sessions.
- Ability to specify a "Post Processing" SQL to post-process the REST Service response (e.g. allow joining to a local table)
To get the data out of WorkDay, you must call a WorkDay REST service. This is achieved in APEX by creating a REST Web Source Module using a wizard. In the wizard, you reference the WorkDay REST service. Once you have defined the REST Web Service Module, you can use it in an Interactive Report on the APEX page that you want to display to your ERP Cloud users. There is no need for any additional code and you don’t have to store the data locally before displaying it in the report. The data is pulled into the Interactive Report directly from the web service. This approach reduces the number of lines of code you have to write by a factor of 10 over the way we do this today.
The caching capabilities of REST Web Source Modules provide additional benefits. Results from web service calls are optionally cached locally. This prevents multiple round-trip calls to the web service when the data may not even have changed. This is also something you would have had to 2code by hand in previous versions of APEX.
More and more of our work is focused on bringing data from disparate sources together in one place. Being able to do this from a single APEX App as opposed to having to architect and implement an entire integration architecture is very powerful. Being able to do this using declarative components in APEX takes it to a whole other level.
REST Enabled SQL Support
Once you have REST Enabled SQL enabled on your target instance, you can now consume data from that database using APEX. You do this by creating a REST Enabled SQL shared component in APEX which points to your REST Enabled SQL end-point in your target instance. Once the REST Enabled SQL component has been created, you can use it in much the same way as a REST Web Source Module and reference the response in your APEX components.
Here are some additional important features of REST Enabled SQL:
- Use a REST Enabled SQL Service for the following components: Classic Reports, Interactive Reports, CSS Calendar, JET Charts, Tree Region, Toggle Column Report, and Reflow Table Report
- Execute PL/SQL page processes on a remote Oracle database
- Use the APEX_EXEC PL/SQL package to execute SQL Queries or PL/SQL blocks on a remote database, from within your custom PL/SQL code.
- Caching is available to reduce round trips to the web service. Caching can be session based, user based or for all users and sessions.
As with our earlier example, this can be achieved by building an APEX page in Exadata Express and embedding it within ERP Cloud. To be able to get Purchase Order data from their on-premise EBS instance, the company has installed ORDS and enabled REST Enabled SQL. Next, build a REST Enabled SQL Data Source in our APEX application and point it to our ORDS installation. Next, create an APEX page with an interactive report which references the REST Enabled SQL Data Source and a SQL statement to query data from the Purchasing tables in EBS. Finally, the APEX page is plumbed into ERP Cloud. At this point, our ERP Cloud end-user has full Interactive Report functionality on Purchase Order data that is coming directly from EBS. In addition, caching functionality built into REST Enabled SQL in APEX, limits the number of calls to the on-premise web service improving performance and user experience.
With the one two punch of REST Enabled SQL and REST Web Service Modules, we can now easily mix and match data from Cloud and On-Premise data sources.