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.

Types of Inbound Integration

In this post, I will discuss three major types of inbound integration:

1. Webhooks & Callbacks

2. Mobile Apps

3. 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.

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.

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:

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.

  • Do not use basic authentication.

  • Utilize ORDS Roles and Privileges to limit what OAuth clients can call specific services within an ORDS Module.

• 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:

• Callback & Webhook Examples:

  • Receive Updates when Data Changes in Jira

  • Receive Updates when Data Changes in QuickBooks

  • Receive New Messages from MS 365 Teams Channel

  • Utilize Oracle OCI Events to load data from files uploaded to OCI Object Storage. Check out my post, "Event-Driven Integration with OCI Events, ORDS, & APEX" for more details.

• Inbound Data Integration Examples:

  • Allow Autodesk Vault to post updated Items and Bills of Material to Oracle EBS Via an ORDS REST API.

  • Allow Onbase to post AP Invoices to Oracle EBS Via an ORDS REST API.

  • Allow Oracle Field Service Cloud to post Inventory Adjustments to Oracle EBS.

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

• Post 1 - Fetching Data from Other Systems