OIC - Upload attachment in REST API Oracle Integration Cloud

UseCase: We will upload a CSV file as attachment to REST API and then parse the CSV file and send back the contents as json back to the consumer.

Highlevel steps:

• Configure a rest trigger with POST verb and select request as multipart with payload options and response as a json payload
• Take a stage and write the file into stage using opaque schema and encodeReferenceToBase64() function.
• Take another stage and read the file using sample deilimited doc.
• Map the read contents to rest response.

Json response payload:

[{

"FirstName":"",

"LastName":"",

"Designation":""

}]

For opaque schema:

https://soalicious.blogspot.com/2022/02/xsd-opaque-schema.html

Detailed implementation steps: 

Integration flow:

Steps:

OIC - Advantages using Oracle Integration Publish and Subscribe model

This pattern involves the publisher and the subscriber relying on a message broker, AES, that relays messages from the publisher to the subscribers. The host (publisher) publishes messages (events) with a set schema (event type) to a channel that subscribers can then sign up to (subscribe).

There are several advantages to using OIC Pub/Sub:

1. It allows messages to move between different integrations without integrations being aware of each other. This means you can simplify a complicated synchronous integration to smaller asynchronous integrations. In other words, you can have loose coupling between your integrations to make them more maintainable.
2. It provides fast and scalable expansion. You can easily add additional subscribers to an existing Pub/Sub pattern, without any concerns about interrupting the existing event flow from publishers to subscribers.
3. It allows for fast and real-time integrations of dispersed systems. Events are made available to multiple integrations for consumption, allowing applications across your organization to simultaneously receive relevant payload.

OIC - How to change From address for email Notification

If we use a "from" address in the email address in the email notification action, it will not be reflected. We can configure a from address from settings. If no email address is configured, a default from address will be used.

To change a from address we have to perform following 2 steps:

1. Register the from address in Settings >> integrations >> Notification
2. Configure SPF and DKIM on the sender domain. For example, if you want from address as no-reply@oraclecloud.com, then you have to configure the sender domain i.e, oraclecloud.com

Go to Settings >> integrations >> Notification >> you will see default from address.

For more details, follow the reference:

https://blogs.oracle.com/integration/post/an-advanced-guide-to-oic-notification-via-emails

OIC - Why Scheduled Integrations are not executing on Time.

Scheduled integrations may be queued and not execute on time as scheduled for the following reasons:

• There is a running instance of the same scheduled integration. No new instance of the same integration is executed until the running instance completes.
• The throtting limit for scheduled integrations has been reached. No new scheduled integrations ate executed until the number of scheduled integrstions being executed falls below the throtting limit.

More, a scheduled integration is considered stuck when the total execution time exceeds six hours.

OIC - Maximum duration for integration flows | time out time for OIC services | Service limit for Scheduled orchestration or Async or Sync service

For Async integrations: Six hours (the instance is marked as aborted due to deadline timeout)

For Scheduled integrations: Six hours (the instance is marked as aborted due to deadline timeout)

For Synchronous integrarions: Five minutes (An HTTP 502 occurs).

OIC - Read Multiple records are of different types CSV file in Oracle Integration where 1st column does not have default values

UseCase: Read multiple records are of different types CSV file in Oracle Integration where 1st column does not have default values.

If we have requirement to read a "multiple records are of different types" CSV file in Oracle Integration where 1st column have default values. Then follow my below blog:

https://soalicious.blogspot.com/2023/06/oic-read-multi-records-of-different.html

In this blog, we have the following file to read:

Source file to be read:

Header1, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Header11, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Header111, Header2

Line1,Line2,Line3,Line4

Line11,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Line111,Line2,Line3,Line4

Important notes:

If you notice the file format, you will see the following:

One set is repeating mutilple times where

Header record 1 time

Line records 4 times fixed count

Implementation steps:

First create an XSD based on the CSV file using Jdeveloper File adpater configuration.

For the steps, Follow my previous blog :

https://soalicious.blogspot.com/2023/06/oic-read-multi-records-of-different.html

Then modify the xsd a little so that it can read this file properly.

Changes done:

1. Replace the choice element with sequence and make maxOccurs as unbounded.
2. Added a SET element as a pair element for repeating header and lines.
3. Remove the conditionValues element as we dont have default values as 1st column.

Modified XSD:

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

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:sequence maxOccurs="unbounded" >

<xsd:element name="SET">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="HEADER">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="header1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="header2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="LINE" minOccurs="4" maxOccurs="4">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="line1" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="line2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="line3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="line4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

References:

https://technology.amis.nl/amis/oracle-11g-soa-suite-read-multi-record-data-from-csv-file-with-the-file-adapter-master-detail/

OIC - Read Multi records of different types CSV file | How to create a multi records of different types XSD to read CSV file

Use Case: We have multi Records are of Different types CSV file. How we can create a schema for the same and read via oracle integrarion.

File to be read: InputSourceData.txt

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Trailer, Trailer1

Notes: This case, we have following  3 types of records and 1st column values are fixed which would be our conditionValue while generating the xsd. >> 

1. Invoice (Header)
2. Invoice Line(Line)
3. Invoice Trailer(Trailer)

Implementation steps:

Step1: Create XSD from Jdeveloper tool

Create a SOA project and open composite.xml canvas and drag and drop a File adapter:

XSD is generated but while you tested the C1 or column 1 value for each record is missing due to have conditionValue attribute. Here we modify the code little to add the column 1 fixed or default values:

For each record add the below line and it works:

<xsd:element name="C1" type="xsd:string" default="Invoice"/>

The Modified XSD:

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

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:choice minOccurs="1" maxOccurs="unbounded" nxsd:choiceCondition="terminated" nxsd:terminatedBy=",">

<xsd:element name="RECORD1" nxsd:conditionValue="Invoice">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD2" nxsd:conditionValue="Invoice Line">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Line"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C6" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C8" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C9" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD3" nxsd:conditionValue="Invoice Trailer">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Trailer "/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:choice>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Step2: Create a scheduled orchestration integration and drag and drop a FTP adpater connection and read the file using this created schema.

Activate and test.