Introduction
In the previous post in the series, I showed you how APEX and the database can fetch data from other systems.
In this post, I will talk about receiving data (or requests for action) from other systems.
📢
Oracle REST Data Services (ORDS) will take center stage in this post.
Types of Inbound Integration
In this post, I will discuss three major types of inbound integration:
Webhooks & Callbacks
Mobile Apps
Inbound Data Integration
Webhooks & Callbacks
I am grouping Webhooks and callbacks despite a subtle difference between the two, which can be described as follows:
A webhook is a piece of code in a software application that notifies a web service you create that something has happened in that piece of software.
- For Example, In Jira, you can specify a Webhook (a web service) that should be called when a ticket is created or updated.
A callback is similar to a webhook, but the trigger can be external to the application.
- For Example, in Oracle Cloud Fusion Applications, when you run a scheduled job via a web service call, Fusion will call a Web Service you identify once the job has been completed.
| Example Webhook | Example Callback |
| A Webhook is configured in Jira to call an ORDS REST API whenever a new ticket is created. The ORDS REST API then calls code in the Oracle Database to perform the appropriate actions. | Code in the Oracle Database calls an Oracle Fusion ERP Cloud web service to run an Oracle Enterprise Scheduler (ESS) job. When calling the Fusion Web Service, we also pass the URL of the ORDS web service that we want Fusion to call when the ESS job has been completed. When the ESS job is completed, Fusion calls back to the ORDS API to let us know it has been completed. The appropriate action can then be performed in the Oracle database. |
Other examples of Webhooks and callbacks include:
SendGrid allows you to establish a REST API that should be called when certain events occur during the email delivery process. This allows you to send emails to users from an Oracle Database and then receive web service calls when the email is delivered/bounced, opened, etc.
Microsoft Teams allows you to configure Webhooks that are called when a message is posted to a Teams chat. This is a great way to create a Simple Chatbot with ORDS & MS Teams.
OCI Events allow you to configure a REST API to call whenever certain events occur in OCI. For example, you can have OCI call an ORDS REST API whenever a file is uploaded to an Object Storage bucket.
Native Mobile Apps
ORDS allows you to build secure, scalable REST services on top of your Oracle Database. This is perfect for building REST APIs that iOS and Android mobile applications can consume.
The diagram above illustrates native mobile applications interacting with the Oracle Database via ORDS REST APIs. This approach allows the mobile applications to leverage data and logic (views, PL/SQL APIs, etc.) that you already have in your Oracle database.
I was involved in a successful project that allowed a manufacturing company to deploy native iOS applications to more than thirty plants throughout the US and Canada. These iOS applications were used in manufacturing and posted data back to Oracle e-Business Suite (EBS) via ORDS REST APIs.
💡
You do not always need to resort to building native mobile applications. APEX allows you to create responsive mobile applications with many native mobile features, including many Progressive Web Application (PWA) features.
Inbound Integrations
You can also create ORDS REST APIs that can be called from other applications to perform actions in your database and or load data. This securely opens up the functionality of your applications and your data to the outside world.
One area where I have been extensively involved in inbound integrations with ORDS is integrating third-party systems with Oracle e-Business Suite (EBS). ORDS offers a secure and easy way to build REST APIs with Oracle EBS functionality. This allows third-party applications to interact with your EBS Database. Examples I have been involved with include:
Create Accounts Payable Invoices in EBS from Onbase.
Create Inventory Items and Bills of Materials from Autodesk Vault.
Create Sales Orders in EBS from a Web Store.
The above example depicts an integration where AutoDesk Vault sends new and updated items and Bills of Material (BOMs) to an ORDS REST API. The ORDS REST API calls PL/SQL code, which establishes an EBS session and creates or updates the items and BOMs in EBS.
Catalogs
Providing clear and comprehensive documentation is important when exposing REST APIs to other systems. Luckily, ORDS has your back and automatically creates Swagger documentation for your ORDS modules.
For example, the APEX Developer Blogs App has APIs to provide JSON and Atom feeds of APEX-related Blog Posts. ORDS generates the Open API JSON via this link: https://apps.cloudnueva.com/ords/api/open-api-catalog/apex_developer_blogs/
If you import this URL into https://editor-next.swagger.io/, you get the following:
💡
Read
this post for more on hosting Open AI REST documents using ORDS.
Security
Obviously, whenever you allow other systems to access your Oracle database, you must be aware of security. This is especially true when allowing access to external systems. In this section, I will discuss several approaches to ensuring the security of your ORDS REST APIs.
Locate your ORDS Server on a separate physical server from your database server.
As with any server, make sure you apply patches on a regular basis.
Make sure you stay current on the latest version of ORDS.
Put your ORDS server behind a firewall, adding firewall rules that only allow access to the ORDS server and port.
- Further limit access by limiting the URL paths that external systems can access. For example, if you have an ORDS module called items, only allow access to the path https://www.example.com/ords/items*. This prevents any possibility of external systems accessing other ORDS modules (or even APEX).
Secure your ORDS Services using OAuth2 Client Credentials.
Implement access logs and check them for unauthorized access attempts.
Create your ORDS modules in a separate schema from the schema where your APEX Applications are located. For the ORDS schema, only grant access to the tables, views, and PL/SQL Packages that are necessary.
Examples
The following list represents examples of Integrations I have built where the APEX Instance Receives Data from other systems:
Conclusion
As APEX Developers, ORDS gives us superpowers that most other developers do not have. ORDS allows us to easily create REST APIs that other systems can use to interact with the Oracle database in which our APEX applications reside. This allows us to easily re-use the SQL and PL/SQL logic we developed for our APEX Applications.
Other Posts in this Series
