OIC - Save Integration request payload XML/JSON into database

Usecase: 

Here, We have a project requirement that we have to save the integration source data XML or Json into a database table so that the support person can track the payload if required. In addition, we need to save the metadata details like instance id, name, identifier etc.

Though Oracle integration allows us to track the complete complete using activity stream if debug or audit tracing is enabled. Saving the payload in the activity may have a security rick as the payload may contain sensitive data. 

But having a payload handy is essential for the operations or support team to debug the issues. Instead of storing the payload in the activity stream, we can save it into a persistent store like a database.

Note: here, we will save a Request Json data to database table, the same you can do using xml data.

Logic steps:

1. Create a database table where payload column is as clob type.
2. Create a database connection and a rest trigger connection.
3. Create an appdriven integration and configure Rest to pass the json data as request paylaod.
4. Write the request content into a stage location using the XML or JSON structure which ever required.
5. Configure the database to insert
6. Map the staged payload reference to database paylaod column using decodebase64(encodeReferenceToBase64(FileReference) functions.

Table create:

CREATE TABLE OIC_TRACKING(

Id INTEGER PRIMARY KEY,

Payload CLOB,

Integration_Name VARCHAR2(100),

Integration_Identifier VARCHAR2(100));

Detailed steps (with screenshots):

Integration flow:

Configure rest trigger to send request

Assign some integration metadata specific details like instance id, name, identifier.

Write the JSON file into stage location

Configure the database to insert data

Map the stage payload to database payload column.

Test:

OIC - Gen3 - Connectivity agent basic authentication not supported | Support only OAuth 2.0 for Connectivity agent

New changes for connectivity agent:

• While using connectivity agent, basic authentication is not supported in generation 3. 
• To install and use connectivity agent, we need to use OAUTH 2.0 token based authentication in installerprofile.cfg file.
• Create an agent group and download the config which will have all the details auto generated.
•  Just then download the connectivity agent, unzip and replace the installerprofile.cfg file.

Navigation>> Go to Design >> agents

Download the agent

We can see client id and secret and scope added.

Download the config for the agent group to be used.

OIC - Split comma separated string values to Array | Use of create-nodeset-from-delimited-string() function in XSLT

UseCase: Here, we will show you how to split an input, received as comma separated string values( here, emails) into array of values using create-nodeset-from-delimited-string() function.

Logic steps:

• Create a rest trigger connection
• Create an app driven integration and configure using request and response jsons
• Map the request to response using create-nodeset-from-delimited-string() function.

Input json:

{

"Emails":""

}

output json:

{

"Emails":[{

"Email":"",

},{

"Email":""

}]

}

XSLT code:

<nstrgmpr:executeResponse>

<nstrgdf1:response-wrapper>

<nstrgdf1:Emails>

<xsl:for-each select="oraext:create-nodeset-from-delimited-string('{http://test.com/sample/xml}value',/nstrgmpr:execute/nstrgdf1:request-wrapper/nstrgdf1:Emails,',')">

<nstrgdf1:Email>

<xsl:value-of select="."/>

</nstrgdf1:Email>

</xsl:for-each>

</nstrgdf1:Emails>

</nstrgdf1:response-wrapper>

</nstrgmpr:executeresponse>

Details steps with screenshots:

Rest configure to input comma separated strings and get back response as array.

Map the rest request which is the comma separated strings to array using create-nodeset-from-delimited-string() function.

Test:

Note: we can also use oraext:create-delimited-string() XSLT function to convert nodes to delimited string.

Reference:

https://blog.deciphermiddleware.in/2020/12/create-delimited-string-or-xml-nodes.html

OIC - Gen 3 - How to Regulate access to a project | RBAC | Resource based access control

Oracle Integration Cloud Gen3: how to regulate access to a project?

A project can either be accessible to all users, depending on their service role (administrator, developer, monitoring rights, etc.), or restricted to a defined list of users.

• By managing access to projects by users or groups of users via the role-based access control (RBAC) system, it is possible to isolate a project and make it accessible only to a specific group of users. In this way, multiple users working on different projects can work on the same Oracle Integration Cloud instance. 
• This role system gives groups of users access and the right to view, edit and monitor only the resources of a project assigned to them.
• Service roles (ServiceDeveloper, ServiceMonitor, ServiceInvoker etc.) assigned to each instance user have priority over permissions delegated within a project. So, if a user only has the right to monitor the instance, he or she cannot be given the ability to edit project resources. 
• In terms of limitations, a maximum of 5 users or user groups can be defined for each project role (viewing, editing and monitoring).

Example of compartmentalizing an OIC Gen3 instance into projects

Note: 

• The rights defined on a project are not migrated at the same time as the project to another environment (test or production). 
• The user who imported the project to another environment is by default the owner of this project. It’s up to him to assign rights to project participants once again.

This permissions system enables parent integrations of a project to call child integrations:

• for the same project;
• another project, but with access defined as public
• globally accessible, i.e. not part of a project.

It is also possible to convert a package into a project.

Reference:

https://www.sqorus.com/en/new-features-oracle-integration-cloud-gen3/#:~:text=Oracle%20Integration%20Cloud%20Gen3%3A%20a%20fresh%2C%20new%20design&text=The%20navigation%20panel%20has%20also,Creating%20and%20manipulating%20an%20integration

OIC - Read latest file from SFTP server | Poll the file last time modified from a SFTP server

Usecase: Client has given a requirement that the file polling folder may have multiple files and we only need to read or poll latest file or the file last time modified.

Logic flow:

1. Create a sftp and rest connections.
2. Create an appdriven integration and configure rest to read the latest file and contents as response.
3. List the files using sftp adapter.
4. Read the latest file using sftp adapter. The tweaking, we have to do in the mapping file itself. 
1. For each file, we have to sort as descending order based on lastModifiedTine
2.  and if position =1 map the file name and ditectory
5. Then map the read file contents  and file name to rest response.

Used json:

{

"FileName":"",

"Employees":{

"Employee":[

{"Empid":1,"Fname":"","Lname":"","Dept":"","Age":"","Country":"","Skill":""},

"Empid":1,"Fname":"","Lname":"","Dept":"","Age":"","Country":"","Skill":""}

]

}

}

Map part for reading latest file:

<nstrgmpr:SyncReadFile>

<ns27:FileReadRequest>

<xsl:for-each select="$listFiles/nsmpr2:ListFileResponse/ns24:ListResponse/ns24:FileList/ns24:File">

<xsl:sort select="ns24:lastModifiedTime" order="descending"/>

<xsl:if test="position()=1">

<ns27:filename>

<xsl:value-of select="ns24:filename"/>

</ns27:filename>

<ns27:directory>

<xsl:value-of select="ns24:directory"/>

</ns27:directory>

</xsl:if>

</xsl:for-each>

</ns27:FileReadRequest>

</nstrgmpr:SyncReadFile>

Detailed screenshots:

Integration flow:

Configure rest trigger to read the latest file name and contents as response.

List the files:

Read the latest file:

Add tracking, save, activate and test

OIC - Gen3 - How to publish and subscribe events | What is oracle integration events | what is publish event action

Oracle has recently introduced a new feature called Event in Oracle integration generation-3 which is equivalent to the Publisher and Subscriber model of Oracle integration generation-2.

What is event?| what is oracle integration events? | Restrictions?

• Events are raised when something happens in the system such as employee onboarded, Order created etc. Once the events are published, multiple applications can subscribe to the events
• Starting OIC version 23.06 introduced this event concept.
• Support only JSON payload.
• The publish and subscribe feature enable us to decouple producers and subscribers.
• A maximum of 20 subacribers can subscribe to events per service instance.

Steps to follow:

1. Define event and Payload (Only JSON payload supported)
2. Create Publish Integrations
3. Create Subscribe integrations

Detailed Steps with screenshots:

Create event:

Publish event from publisher integrations:

Create subscriber events

OIC - How many different scheduled integrations can run in parallel in Oracle Integration | Decoupled scheduler and Business Logic pattern.

Though OIC now provides dynamic threading model for scheduled orchestration integrations but it still has thread limitation. Thread count is internal to Oracle and may vary based on instance / subscription type.

Scheduled integration interfaces are widely used in batch integrations. Oracle Integration cloud provides feature to build scheduled integration flows, that can execute at defined frequency. However sometimes users complain about inconsistent behavior of scheduled flows like schedules are getting delayed by few minutes to few hours.

In this blog, We will discuss a common design issue that causes such incidents and solution for it.

What problem we faced and observed:

If we have a large of number of scheduled integrations running in parallel, then we will observe that few are running and others kept in queued and the backlog getting increased which delayed the processing of the backlog scheduled integrations.

For instance:

 I have created 6 scheduled integrations and run them in parallel and observed:

• 4 scheduled integrations are running in parallel. 
• whereas 2 integrations are in queued. 
• Once running ones completed, then the queued one started running. 

So what I understand for my OIC instance it has 4 scheduler threads limitation.

• o it can be made as re-usable component.

Solution:

We can decouple scheduler and integration logic into 2 different flows and let scheduled integration just invoke the child integration using Fire & Forget pattern.

1. For decoupled design, instead of creating many scheduled inegrations, we can create 1 or few  parent (Scheduled integrations) and others are Child (non-scheduled) application integration flows, simply exposed as REST endpoint.
2. Child flow exposes REST endpoint with "POST" verb and not returning any response hence making it a "One Way" flow. So it can be invoked as Fire & Forget .
3. Each Parent flow invokes its respective child flow. However since child is "one way" flow its not returning anything. So parent flow won't wait for the response after invoking child flow. It will either terminate and release the thread or move to next activity (if any).
4. Child integrations will use worker thread for processing. So this time, when one or few scheduled integration triggered at the same time, they were terminated within few seconds and there were 6 or more child integrations in "running" state, Running in parallel.

Reference:

https://www.ateam-oracle.com/post/oic-scheduler-decoupled-scheduler-and-business-logic-pattern