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
First, let us dive into what we are trying to build..
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!
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.
The Parking Lot pattern here uses an ATP table to store request metadata and status information. The OIC Flows in the design are
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.
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.
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 !
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
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.
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.
This Best practice of loosely coupling Scheduling logic and Business Logic is explained in this ateam blog
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.
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.
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 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 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.
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 !
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 !
Checkout the artifacts section below, where the OIC code and necessary schema are provided.
The sample OIC code and ATP DDL scripts are published and now available at Oracle official github repository.