The upcoming release of APEX 18.1 introduces three important new features: REST Web Source Modules, REST Enabled SQL and Social Login. We think these features will help APEX earn its rightful place as a first-class citizen in Oracle's line up of PaaS solutions. In this post I will discuss what these features are and why we think they are important.
Improved REST Consumption (REST Web Source Modules)
Setting the Scene
For as long as I can remember, it has been possible to call external web services from PL/SQL and APEX. The way we do this has evolved from low level APIs like UTL_HTTP to the much easier to use APEX_WEB_SERVCE API. In our work extending Oracle EBS and ERP Cloud, we often utilize APEX_WEB_SERVCE to fetch data from other systems and present it in ERP Cloud or EBS via an APEX page. Even with APEX_WEB_SERVCE, however, there is still quite a lot of work you have to do. You have to get to know the structure of the service response, call the web service, parse the response and either populate a collection or a table in the local database, before presenting the data to your users.
What are REST Web Source Modules?
REST Web Source modules provide the ability to access data from REST web services through declarative components in APEX. You declare a REST Web Source Module once and then use it from standard components in APEX like Classic Reports, Interactive Reports, CSS Calendar, JET Charts etc.
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:
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)
Why are REST Web Source Modules Important?
Let's take an example where a customer wants to display employee data from WorkDay, embedded in an Oracle ERP Cloud page. The Exadata Express PaaS service can be used to build an APEX page which you can embed in ERP Cloud. See our previous post Oracle Cloud ERP and APEX PaaS Mashup.
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.
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
Setting the Scene
In my 20+ years of working with Oracle technology, I have had to use database links a number of times. Database links as a technology never did sit right with me. Hard wiring a connection between two systems this way just seemed wrong. Having said that, database links were pretty much the only way you could connect two databases in real time. Fast forward to today and we have Oracle REST Data Services (ORDS), now in its 3rd major version. We can now connect Oracle databases using REST services, which are technology and platform agnostic.
What is REST Enabled SQL?
REST enabled SQL is an ORDS capability which allows you to securely send SQL (SELECT, UPDATE, DELETE or even execute PL/SQL Procedures) to an Oracle database (over REST) and have the remote database run that SQL. If applicable, you can get the response back via a JSON document. There is no need to create a REST service for every eventuality that you may have, just send the SQL you need to the database and it will obey. Obviously, this comes with a word of caution (in just the same way database links did). You are exposing your database to the outside world and literally enabling SQL injection. To mitigate any risk, ORDS provides OAuth security capabilities so security should not be an issue as long as you implement with caution.
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:
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.
Why is REST Enabled SQL Important?
To describe why this technology is important, let’s consider another example. A company is moving from Oracle EBS to Oracle ERP Cloud. During this transition, the company has decided to move their core financials modules (GL, AP and AR) to ERP Cloud whilst keeping Purchasing in EBS. The requirement is for the AP department to be able to view Purchase Order information from EBS within ERP Cloud without having to login to EBS separately.
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.
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.
Social Login
Setting the Scene
One of the most challenging aspects of building custom applications is how to authenticate users. Taking on the responsibility of storing and maintaining user identity (and passwords) is not a task to be taken lightly. It seems that every day we hear about another company whose security has been compromised and user account information has been stolen. Because of this, my first instinct is to look elsewhere for a source of user authentication.
What is Social Login?
Social Login is a new APEX Authentication scheme that makes it easy to authenticate users using credentials from providers such as Google, Facebook or OpenID. Many web sites do this today, I am sure you can think of examples where you use your Google or Facebook login to access other web sites. In fact, because Social Login supports OAuth2 you should be able to authenticate a user using credentials from any OAuth2 provider.
Why is Social Login Important?
Having your users authenticate using a 3rd party, not only saves time, but is in all likelihood, much more secure. Let’s face it, Google’s entire reputation is hinged on your password being secure and they have hundreds of engineers dedicated to making sure it stays that way. The chances are, your company does not have these kinds of resources so why would you want to take on that responsibility? Not having to do authentication allows you to focus on other aspects of your application. Finally, depending on your application, it may be more convenient for your users to use their Google or Facebook login. That way they don’t have to create and maintain one more account.
Conclusion
APEX 18.1 may not have some of the UI focused marquee features that APEX 5.1. introduced (e.g. Interactive Grid). For developers that deal with data and users that live outside of the Oracle database, APEX 18.1 has made life a whole lot easier and more secure.
RSS Feed