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

OIC - Best Practice - Run large number of scheduled integrations at the same time | avoid creating too many scheduled integrations

When there are too many schedule integrations configured, instances can become backlogged waiting for resources to become available or previous integration runs to complete. This can cause processing delays where some instances are in a waiting state longer than they must be and schedules may not start at the scheduled time.

Best practice:

A. Do not create too many schedule integrations that are scheduled to run at the same time. Where possible:

• Use an asynchronous REST Adapter trigger instead of a scheduled trigger when an active schedule is not absolutely required.
• Do not create any long-running schedule integrations (a schedule integration that takes longer than one hour, for example, to complete). This blocks scheduler resources impacting other scheduled runs.
• Spread schedules out over time to avoid schedule clusters.

B. We can convert a schedule integration to a REST Adapter trigger-based application integration.

If you absolutely require a large number of schedule integrations, and you encounter the previously described issue, the following design changes as a solution are recommended:

1. For each schedule integration, convert it to a REST Adapter trigger-based application integration.
2. Create a new schedule integration that only performs an asynchronous invoke of the application integration you converted in step 1 above.

   This solution enables the schedule integration to start at the scheduled time, invoke the REST Adapter trigger-based child integration asynchronously, and complete within milliseconds. This approach reduces the backlog and contention surrounding scheduler resources.

   If you have a large number of schedule integrations to convert, a staged approach starting with the following integrations is recommended.

   • Longest-running schedule integrations.
   • Schedule integrations configured with the shortest frequency (for example, integrations that run every 10 minutes or less).

   Design any new schedule integrations with the design practices described above.

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/integrations-user/common-integration-style-pitfalls-and-design-best-practices.html

OIC - Gen3 - Parallel action

Parallel action:

OIC 3 brings new features like Parallel flow, Cut -paste and publish events etc. 

Using Parallel flow one can define branches to run two or more actions in parallel. Parallel flows enable to perform multiple tasks at the same time, It is specially useful when we must perform several time consuming and independent tasks.

Below screeshot: Where from a scheduled integration, we are running 3 rest services in parallel and in same time.

One more example:

Suppose we have to subscribe Purchase order events or employee events from ERP and then we need to send this information to multiple downstream applications that we can acheive using parallel action which will save a lot of processing time.

OIC - what is the maximum concurrent / parallel OIC scheduled flows limit?

• The maximum concurrent / parallel OIC Scheduled flows limit is 2 per managed server. 
• In case of a 2 node cluster, 4 scheduled flows can run in parallel.

Jobs of scheduled integration flows are controlled by IcsFlowIncompatibility property.

If user chooses to run the integration flow using ad hoc submit Now option, tgen IcsFlowIncompaProp of thr schedule job will be like:

<FlowCode>:<MajorVersion>:RunNow

If user defines a schedule for the integration flow and starts it, then IcsFlowIncompatProp of the scheduled job will be:

<FlowCode>:<MajorVersion>:SCHEDULENAME

Job incompatibility will restrict jobs with same IcsFlowIncompatprop value from running in parallel.

Hence,

• 2 submit runs of the same flow can not run in parallel.
• 1 submit run and 1 scheduled run can run in parallel.

WSDL - Asyn Callback service | Asyn BPEL WSDL vs Sync BPEL WSDL

Asyn callback users Wsdl file:

<?xml version="1.0" encoding="UTF-8"?>

<wsdl:definitions name="UserWSDL" targetNamespace="http://xmlns.oracle.com/Application1/Project1/UserWSDL" xmlns:tns="http://xmlns.oracle.com/Application1/Project1/UserWSDL"  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:inp1="http://www.example.org">

<wsdl:types>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:emp="http://www.example.org" targetNamespace="http://www.example.org" elementFormDefault="qualified">

<xsd:element name="Users">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="User" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="id" type="xsd:integer"/>

<xsd:element name="firstName" type="xsd:string"/>

<xsd:element name="lastName" type="xsd:string"/>

<xsd:element name="email" type="xsd:string"/>

<xsd:element name="country" type="xsd:string"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="CallBackStatus">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Status" type ="xsd:string"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

</wsdl:types>

<wsdl:message name="requestMessgae">

<wsdl:part name="payload" element="inp1:Users"/>

</wsdl:message>

<wsdl:message name="callbackMessgae">

<wsdl:part name="payload" element="inp1:CallBackStatus"/>

</wsdl:message>

<wsdl:portType name="UserServicePort">

<wsdl:operation name="UserGetOP">

<wsdl:input message="tns:requestMessage"/>

</wsdl:operation>

</wsdl:portType>

<wsdl:portType name="callbackstatus_ptt">

<wsdl:operation name="callbackstatus">

<wsdl:input message="tns:callbackMessage"/>

</wsdl:operation>

</wsdl:portType>

</wsdl:definitions>

Async BPEL WSDL vs Sync BPEL WSDL:

Sync BPEL:

In SOA if our bpel is of synchronous type then we can make only synchronous calls and for which we will get the response immediately, So this case BPEL wsdl contains single port having two operations (request, response) in it.

Asyn BPEL:

If our BPEL is of asynchronous type then we can make sync and async calls. This case BPEL wsdl contains two ports having separate operations request, response. Here we need to observe one thing is if the response/callback takes long time then we should use separate port.

OIC - PLSQL - How to feed comma separated fields as input and fetch table of records output using stored Procedure

Usecase:  We have a requirement in oracle integration where we need to pass comma separated ids as input and fetch the employee names along with the ids as a table of records from a database table using stored procedure.

PLSQL CODE:

Create object type:

CREATE OR REPLACE TYPE employee_row_type AS OBJECT(

id number,

firstname varchar2(200)

);

Create a table of rows types:

CREATE OR REPLACE TYPE employee_table_type AS TABLE OF employee_row_type;

Create the stored procedure:

CREATE OR REPLACE PROCEDURE fetch_employee_data(p_employee_ids IN VARCHAR2,p_employee_data OUT employee_table_type)

IS

BEGIN

SELECT employee_row_type(id,firstname)

BULK COLLECT INTO p_employee_data FROM employees_sd WHERE id IN (SELECT TO_NUMBER(REGEXP_SUBSTR(p_employee_ids, '[^,]+',1,LEVEL)) FROM DUAL CONNECT BY REGEXP_SUBSTR(p_employee_ids, '[^,]+',1,LEVEL) IS NOT NULL);

END fetch_employee_data;

/

Test code:

DECLARE 

v_emp_ids varchar2(100) := '1,2,3';

v_emp_data employee_table_type;

BEGIN

fetch_employee_data(v_emp_ids,v_emp_data);

FOR i IN 1..v_emp_data.COUNT LOOP

dbms_output.put_line('Empno = ' || v_emp_data(i).id || ', Name: ' || v_emp_data(i).firstname);

END LOOP;

END;

/

OIC screenshots:

Test:

OIC - Different types of Translation failure in the mapper using XSD structure of the file contents

UseCase: While mapping the elements in the mapper using XSD structure of the file contents, getting below 2 types of translation errors:

Error1:

ORABPEL - 11101 

Translation Failure

Translation to native failed. Element not completed: 'UserLabel1'.

The data does not conform to the NXSD schema. Please correct the problem.

Solution 1:

Map all the elements of the complex type element UserLabel1.

Error2 :

ORABPEL - 11101 

Translation Failure

Translation to native failed. Element 'UsernameLabel1' not expected..

The data does not conform to the NXSD schema. Please correct the problem.

Solution 2:

this is because few of elements before the element UsernameLabel1 are not mapped. Map all the elements.

For your reference following XSD used:

OIC - PLSQL - How to feed table of record types and fetch table of records output using Package and Procedure

Usecase:  We have a requirement in oracle integration where we need to pass a table of employee ids and fetch the employee names along with the ids from a database table using stored procedure.

PLSQL code:

Package definition:

CREATE OR REPLACE PACKAGE PKG_DUMMY AS

TYPE rcd_type IS RECORD

(

Id NUMBER,

Fname VARCHAR2(100)

);

TYPE rcd_tbl IS TABLE OF rcd_type;

PROCEDURE GET_FNAME(

tbl_io IN OUT rcd_tbl,

sqlcode_io IN OUT NUMBER,

sqlerrm_io IN OUT VHARCHAR2

);

END PKG_DUMMY;

Package Body:

CREATE OR REPLACE PACKAGE BODY PKG_DUMMY AS

PROCEDURE GET_FNAME(

tbl_io IN OUT rcd_tbl,

sqlcode_io IN OUT NUMBER,

sqlerrm_io IN OUT VARCHAR2

) IS

v_tbl rcd_tbl := rcd_tbl();

BEGIN

FOR idx in 1..tbl_io.count LOOP

v_tbl.extend;

v_tbl(idx).id := tbl_io(idx).id;

BEGIN

SELECT firstname into v_tbl(idx).fname from employees_sd where id := tbl_io(idx).id;

EXCEPTION

WHEN OTHERS THEN

v_tbl(idx).fname :='NA';

END;

END LOOP;

tbl_io := v_tbl;

sqlcode_io :=0;

sqlerrm_io := Null;

sqlcode_io := SQLCODE;

sqlcode_io := SQLERRM;

DBMS_OUTPUT.PUT_LINE('procname : ' || 'Others Exception. SQL: '||to_char(sqlcode_io) || ':' || substr(sqlerrm, 1 , 70)|| '.');

END GET_FNAME;

END PKG_DUMMY;

Call from OIC and Test:

Test codes from Database:

DECLARE

TBL_IO PKG_DUMMY.RCD_TBL;

SQLCODE_IO NUMBER;

SQLERRM_IO VARCHAR2(100);

BEGIN

TBL_IO := PKG_DUMMY.RCD_TBL();

TBL_IO.extend;

TBL_IO(1) := PKG_DUMMY.RCD_TYPE(1, 'a');

TBL_IO.extend;

TBL_IO(2) := PKG_DUMMY.RCD_TYPE(2, 'a');

SQLCODE_IO :=NULL;

SQLERRM_IO := NULL;

PKG_DUMMY.GET_FNAME(

TBL_IO => TBL_IO,

SQLCODE_IO => SQLCODE_IO,

SQLERRM_IO => SQLERRM_IO);

FOR i in 1..tbl_io.count LOOP

DBMS_OUTPUT.PUT_LINE(' ID : ' || tbl_io(i).id || ' and fname : ' || tbl_io(i).fname);

END LOOP;

END;

PLSQL code screenshots:

The same we can also do using package and function as below code:

Package definition:

CREATE OR REPLACE PACKAGE EMPPKG IS

TYPE EMP_REC IS RECORD(

id NUMBER,

fname VARCHAR2(100)

);

TYPE EMP_TABLE IS TABLE OF EMP_REC;

FUNCTION getEmployeeData(

p_emp_data EMP_TABLE

) RETURN EMP_TABLE;

END EMPPKG;

Package Body:

CREATE OR REPLACE PACKAGE BODY EMPPKG IS

FUNCTION getEmployeeData(

p_emp_data EMP_TABLE

) RETURN EMP_TABLE

IS

v_result  EMP_TABLE := EMP_TABLE();

BEGIN

FOR i IN 1..p_emp_data.COUNT LOOP

v_result.extend;

v_result(i).id := p_emp_data(i).id;

SELECT firstname INTO v_result(i).fname FROM Employees_sd WHERE id = p_emp_data(i).id;

END LOOP;

RETURN v_result;

END getEmployeeData;

END EMPPKG;

Test code:

DECLARE

P_EMP_DATA EMPPKG.EMP_TABLE;

v_return EMPPKG.EMP_TABLE;

BEGIN

P_EMP_DATA := EMPPKG.EMP_TABLE();

P_EMP_DATA.extend;

P_EMP_DATA(1) := EMPPKG.EMP_REC(1, 'a');

P_EMP_DATA.extend;

P_EMP_DATA(2) := EMPPKG.EMP_REC(2, 'a');

v_return := EMPPKG.GETEMPLOYEEDATA(

P_EMP_DATA => P_EMP_DATA

);

FOR i in 1..v_return.count LOOP

DBMS_OUTPUT.PUT_LINE(' ID : ' || v_return(i).id || ' and fname : ' || v_return(i).fname);

END LOOP;

END;