Advanced Error handling and Scheduling Best Practices - Oracle Integration Cloud

August 31, 2020 | 9 minute read
Shreenidhi Raghuram
Consulting Solutions Architect
Text Size 100%:

Introduction

In my earlier blog - OIC Error Handling Guide, we read about essentials of Oracle Integration Cloud(OIC) Error handling. We also saw that by using additional components like Database or FTP server along with OIC it is possible to build robust error handling solutions to suit different custom requirements.

In this blog, I would like to illustrate a simple practical example of using the parking lot pattern with OIC. The example will use ATP database for a parking lot table. 

In the process of building this integration solution, we will also see a bunch of design and implementation best practices, which were learnt on recent customer engagements.

Specifically we will touch upon the following concepts

  1. Parking lot pattern for advanced error handling using an ATP table along with OIC
  2. Implementing automated error resubmissions with payload corrections using the parking lot pattern
  3. Scheduled Integrations - best practices and what not to do in Scheduled flows
  4. Use of Asynchronous hand-off pattern for highly scalable and high throughput processing
  5. Use of Parallel For-each to execute iterations concurrently
  6. What to watch out for when using Parallel For-Each
  7. Implementing Request throttling using the parking lot pattern
  8. Use of Scheduled parameters to dynamically control throttling
  9. Use of opaque schema and Stage operations for persisting json payload

First, let us dive into what we are trying to build..

Use case, Design and OIC Implementation

Use Case Description

The use case involves online requests for creation of entities on Oracle Financials cloud using the REST APIs. These could be creation of Expense Reports, Bank Accounts, Banks or other entities using the Oracle Financials Cloud REST API. For simplicity, in this blog we'll just refer to as Account Creation, but in reality it could be any business object exposed by Financials Cloud REST API.

Checkout the REST API documentation here

The requests must be received by an OIC REST Endpoint. We want to be able to dynamically throttle the requests hitting Financials Cloud. We also should be able to track status of the requests and resubmit any failed requests. Just to make it more interesting, it would also be nice to be able to correct the request payload during resubmission if the cause of failure is incorrect data!

High Level Design

Figure below shows the high level design of solution using OIC flows and ATP DB for parking lot table. The OIC solution use Persister, Dispatcher and Processor flows.

Let us now dig deeper into the solution components.

Solution Components

The Parking Lot pattern here uses an ATP table to store request metadata and status information. The OIC Flows in the design are

  • Request Persister
  • Scheduled Dispathcer
  • Async Processor Let us see some details of these individual components

ATP Database

The ATP database here holds the Parking Lot table where all received requests are parked before processing For the purpose of this blog I have chosen a simple table to persist the payload and track the request status and any error information.

Figure below shows the table definition. 

Here the Account creation json request payload is entirely stored in the parking lot table as a string. But there may be use cases to store as a CLOB or an encoded string where it is not desirable to have a visible payload in the table. However, storing the payload as json, provides an opportunity to change the payload during error resubmissions, as we can see in the later section.

Request Persister

The Request Persister OIC flow exposes a REST trigger endpoint which can be called by upstream application (client) to POST the Account Creation requests. This persister flow loads up Account creation requests into ATP immediately on receipt from Client applications and acknowledges a receipt with HTTP 202/Accepted. The Account Id and the entire payload are persisted into the Parking lot table for subsequent processing.

How do we store the entire json request in ATP table?

This is done using 2 stage operations. First - a Stage 'Write' using the request payload's json sample for creating schema file. Second - a Stage 'Read Entire File' operation using opaque schema. This will provide the base64 encoded value of the json payload string. Then the inbuilt function decodebase64(opaqueElement) can be used in mapper(or assign) to get the json string value ! The opaque schema xsd file that is used during stage read is available in the artifacts section of this Blog. This is quite a useful technique and comes handy for many other use cases. Read this Oracle blog for more usages of the opaque schema !

Scheduled Dispatcher

The second OIC flow in our design is the Scheduled dispatcher. This OIC component is a Scheduled Integration, which can be scheduled to run at the required frequency. In every run it fetches a configured number of requests and loops through them dispatching each request to an Async Processor flow for the real processing. Here is how the Scheduled Dispatcher OIC implementation looks like 

Request throttling

The number of requests to fetch is configured as a scheduled parameter. This will allow to 'throttle' or 'accelerate' the request processing, and also lets users dynamically change the value. Check out here for, how to dynamically change scheduled parameters

The requests from parking lot table are fetched based on status of requests. NEW and ERROR_RETRY status requests are fetched and passed on for processing. We'll see later the use of ERROR_RETRY in resubmitting failed requests.

This Dispatcher then loops through the fetched number of requests and hands off each request to the Async processor for account creation. The most important aspect to note here is the Async handoff. Make sure that the Scheduler (parent) flow calls a one way Asynchronous Integration (child) flow. The Async processor does not return any response, so the scheduler thread is freed to go back and loop through the rest of the requests and dispatch them. This ensures that scheduler threads which are meant for the special use case of scheduling are not really held up in long term processing. the business logic of request processing itself is handled by asynchronous processing resources available in OIC.

TIP : Scheduled Orchestration Best Practice !!!

Always decouple Scheduling logic with Business logic using an Async Handoff from Scheduled Integration Flows ! This will ensure that the scheduler threads are not used for account creation. This is inline with the best practice of decoupling Scheduling with Business logic.

  • Scheduled Orchestrations are meant to serve particular requirements of scheduling flows, and freeing them up using Async-handoff makes the solution scalable and performant when processing large number of requests.
  • Scheduled orchestrations should not be used as a substitute for App Driven orchestrations.

This Best practice of loosely coupling Scheduling logic and Business Logic is explained in this ateam blog

Note on Parallelism in For-Each:

Note that "Process Items in Parallel" can be selected for some For-Each loops. This will ensure that the activities within the for-each loop will be batched up by OIC and executed in parallel. Note that Parallelism is on a best effort basis and there are certain conditions where OIC will ignore the parallelism. In such cases, the degree of parallelism will be set to 1 to avoid concurrency issues. Refer to below For-Each documentation.

  • For-Each documentation here

Async Processor

The async processor exposes a REST interface. It is important that this integration is modeled as a one-way Asynchronous flow. This will facilitate an Async handoff from scheduled integration as we saw in previous section. In order to achieve that, note the 2 below important implementation details for Async Processor flow.

  1. Ensure that the REST trigger exposes a POST method
  2. Make sure the REST flow does not return a response to the client

With the above configuration, we have an Async processor flow in our hands.

Since this flow does the actual account creation, it will be responsible to update the request status in the parking lot table. After a successful account creation in ERP, the STATUS column in parking lot table is updated to "PROCESSED". A scope level error handler handles any faults during account creation and updates the status to "ERRORED". The reason and error details are also updated in parking lot table. This will be useful for determining if the request can be resubmitted at a later time. This is a simple implementation. We could also have error notification emails sent out to integration administrators.

Resubmission

Resubmission of failed requests can be controlled from the parking lot table. The Fault Handlers set failed requests to ERRORED status in the parking lot table. These requests can be updated in the table to ERROR_RETRY status and they will be picked in the next schedule for reprocessing due to the selection criteria of the Scheduled Dispatcher's ATP DB invoke.

There are various options to trigger such resubmissions -

  • The update of ERRORED requests to ERROR_RETRY can be performed by an administrator on the database
  • We can have a Resubmission integration flow that runs daily (or any desired frequency) and updates all ERRORED records to ERROR_RETRY
  • The Async Processor's Fault Handler could set the status to ERROR_RETRY directly, so every failure gets resubmitted automatically in the next schedule
  • Specific fault handlers can differentiate between retry-able and non retry-able faults and set the STATUS accordingly in the parking lot table.

The implementation depends really on the use case, the different types of errors we want to resubmit, and the operational procedure desired to handle request errors.

Payload correction

Storing the Account creation payload in the parking lot table has given us a way to correct the payload of data errors prior to resubmission. Update the payload and set status column to ERROR_RETRY to resubmit a request with corrected payload !

Conclusion

In this blog we have seen use case, design and implementation for an Advanced Error Handling Solution using OIC and ATP Database.

In the process we have also seen the following useful concepts !

  1. Parking lot pattern for advanced error handling using an ATP table along with OIC
  2. Implementing automated error resubmissions with payload corrections using the parking lot pattern
  3. Scheduled Integrations - best practices and what not to do in Scheduled flows
  4. Use of Asynchronous hand-off pattern for highly scalable and high throughput processing
  5. Use of Parallel For-each to execute iterations concurrently
  6. What to watch out for when using Parallel For-Each
  7. Implementing Request throttling using the parking lot pattern
  8. Use of Scheduled parameters to dynamically control throttling
  9. Use of opaque schema and Stage operations for persisting json payload

Checkout the artifacts section below, where the OIC code and necessary schema are provided.

Artifacts:

The sample OIC code and ATP DDL scripts are published and now available at Oracle official github repository.

https://github.com/oracle/cloud-asset-integration-oic-parkinglotpattern-sample

The concepts from this blog are also published as a Solution Playbook in the  Oracle Architecture Center.

Refer - Best practices for building resilient asynchronous integrations

 

References

Shreenidhi Raghuram

Consulting Solutions Architect

As a member of Cloud Solution Architects A-Team, my area of work involves SaaS, PaaS4SaaS and IaaS. My work focuses on Solution architecture involving Fusion Supply Chain Management (SCM), Integration, Extensions, High Availability, Security, Infrastructure, Hybrid Cloud and Networking aspects of Oracle cloud solutions.


Previous Post

Comparison of data prep and cleansing for NLP with pandas, dask and spark

Michael Shanley | 8 min read

Next Post


Reduce object workflow maintenance with Dynamic Email templates

Tim Bennett | 2 min read