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;