OIC - Managing Integration Limits in Oracle Integration Cloud (OIC) Using Projects and Shared Connections

Working...

Use Case:

In Oracle Integration Cloud (OIC), you may encounter limits like:

• 100 Integrations per project
• 50 Connections per project

As your environment grows, hitting these limits can cause issues in maintenance, deployment, and governance.

So, how do you scale your OIC environment effectively?

Solution Overview:

To handle this, follow a modular design using multiple projects:

Best Practice Strategy:

1. Create multiple domain-specific projects (e.g., HR, Finance, Procurement).
2. Create a common project (e.g., CommonResources) that holds shared connections like HCM, ERP, REST, FTP.
3. Use shared connections from the common project in other integration projects.
4. Assign the **ServiceDeveloper** role to developers who need edit access.

Step-by-Step Guide

1. Create a New Project in OIC:

• Go to OIC Home > Projects
• Click “+ Create Project”
• Give a name like FinanceProject, HRProject, or CommonResources
• Add description and tags (optional)
• Click Create

2. Add Connections in Common Project:

• Open the CommonResources project
• Go to Connections
• Click “+ Create”, configure REST, SOAP, HCM, ERP, etc.
• These will now be available as shared connections

3. Share Connection with Other Projects:

• Go to the destination project (e.g., HRProject)
• Click Connections > + Add Shared Connection
• Select from the list of connections created in CommonResources
• Click Add

Now, that connection is usable in your integrations in that project, without recreating it.

4. Assign ServiceDeveloper Role:

• Go to OIC Console > Identity & Access
• Click Users > Assign Roles
• Select user
• Assign OIC_ServiceDeveloper role
• This gives them full edit access to integrations, connections, and lookups in that project.

Benefits of This Approach:

• Avoid hitting integration/connection limits
• Easier access control and role assignment
• Better modularization of logic by domain
• Improved governance and maintainability

OIC - How to Exclude Header Lines in NXSD (Oracle Integration) Using nxsd:hasHeader

Use Case:

You're working on a file-based integration in Oracle Integration Cloud (OIC). The incoming file is a flat file (CSV or fixed-length) that includes multiple lines of headers or metadata at the top — for example:

# File generated on: 2025-04-01
# Owner: EnergyEnrollmentSystem
# Do not modify the content manually
AgentID, CardholderName, CreditCardNumber, ...
001, John Smith, 1234567890123456, ...
...

You want to exclude the first three lines (comments or metadata) and start processing from the actual header/data row.

Solution:

Oracle provides NXSD (Native Format Builder Schema) attributes to handle such cases. You can use:

• nxsd:hasHeader="true"
• nxsd:headerLines="3"
• nxsd:headerLinesTerminatedBy="${eol}" (to mark end of line)

This configuration tells OIC to skip the first 3 lines when parsing the data.

Steps:

1. Open your NXSD schema in source mode.
2. Modify the schema header section like this:

<nxsd:hasHeader="true"
 nxsd:headerLines="3"
 nxsd:headerLinesTerminatedBy="${eol}" />

3. Your full schema header will look like this:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd"
            xmlns:tns="http://example.com/YourService"
            targetNamespace="http://example.com/YourService"
            elementFormDefault="qualified"
            attributeFormDefault="unqualified"
            nxsd:version="NXSD"
            nxsd:stream="chars"
            nxsd:encoding="US-ASCII"
            nxsd:hasHeader="true"
            nxsd:headerLines="3"
            nxsd:headerLinesTerminatedBy="${eol}">

4. Save the schema and re-test your file reading. OIC will now skip the first 3 lines automatically.

NXSD full code:

<?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://DSB.com/EnrollmentService"

            targetNamespace="http://DSB.com/EnrollmentService"

            elementFormDefault="qualified"

            attributeFormDefault="unqualified"

            nxsd:version="NXSD"

            nxsd:stream="chars"

            nxsd:encoding="US-ASCII"

            nxsd:hasHeader="true"

            nxsd:headerLines="1"

            nxsd:headerLinesTerminatedBy="${eol}">

            

  <xsd:element name="Enrollment">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="EnrollRecord" minOccurs="1" maxOccurs="unbounded" nxsd:style="array" nxsd:cellSeparatedBy="${eol}">

          <xsd:complexType>

            <xsd:sequence>

              <xsd:element name="AgentID" type="xsd:string" nxsd:style="fixedLength" nxsd:length="3"/>

              <xsd:element name="CardholderName" type="xsd:string" nxsd:style="fixedLength" nxsd:length="40"/>

              <xsd:element name="CreditCardNumber" type="xsd:string" nxsd:style="fixedLength" nxsd:length="16"/>

              <xsd:element name="ExpiryDate" type="xsd:int" nxsd:style="fixedLength" nxsd:length="4"/>

              <xsd:element name="ElectricityAccountNo" type="xsd:string" nxsd:style="fixedLength" nxsd:length="11"/>

              <xsd:element name="ElectricityAccountRegisteredName" type="xsd:string" nxsd:style="fixedLength" nxsd:length="40"/>

              <xsd:element name="MerchantName" type="xsd:int" nxsd:style="fixedLength" nxsd:length="40"/>

              <xsd:element name="ApplicationType" type="xsd:int" nxsd:style="fixedLength" nxsd:length="3"/>

              <xsd:element name="ElectricityActRegisteredTelephoneNo" type="xsd:string" nxsd:style="fixedLength" nxsd:length="10"/>

              <xsd:element name="Address" type="xsd:string" nxsd:style="fixedLength" nxsd:length="20"/>

              <xsd:element name="Filler" type="xsd:string" nxsd:style="fixedLength" nxsd:length="50"/>

              <xsd:element name="Filler1" type="xsd:string" nxsd:style="fixedLength" nxsd:length="1"/>

              <xsd:element name="Filler2" type="xsd:string" nxsd:style="fixedLength" nxsd:length="1"/>

              <xsd:element name="EnrolledOrDeclinedDate" type="xsd:string" nxsd:style="fixedLength" nxsd:length="10"/>

              <xsd:element name="Remarks" type="xsd:string" nxsd:style="fixedLength" nxsd:length="80"/>

            </xsd:sequence>

          </xsd:complexType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

OIC - Date Conversion: Convert dd-MMM-yy Format to MM-dd-yyyy using javascript

Use Case:

In many enterprise applications and data integrations (like Oracle ERP, SAP, or banking systems), date fields often come in legacy formats like 05-Apr-23. These formats are not always compatible with APIs, databases, or modern UI components, which typically expect standardized formats like MM-dd-yyyy.

This utility function helps developers normalize date strings quickly and reliably, especially when working with data migration, report generation, or middleware transformations (e.g., Oracle Integration Cloud or Node.js-based services).

Example Scenario: You're processing HR data extracts that include hire dates in dd-MMM-yy format. Before loading this data into a cloud service or a database, you need to convert all dates to MM-dd-yyyy format to match the target system's expectations.

js code:

function convertDate(inputDate) {

  const months = {

    Jan: '01', Feb: '02', Mar: '03', Apr: '04',

    May: '05', Jun: '06', Jul: '07', Aug: '08',

    Sep: '09', Oct: '10', Nov: '11', Dec: '12'

  };

  const parts = inputDate.split('-'); // dd-MMM-yy

  const day = parts[0].padStart(2, '0');

  const month = months[parts[1]];

  const shortYear = parts[2];

  // Create a temporary date to extract century

  const tempDate = new Date();

  const fullYearPrefix = tempDate.getFullYear().toString().substring(0, 2); // e.g., "20"

  const year = fullYearPrefix + shortYear;

var result = month + '-' + day + '-' + year;

  //return `${month}-${day}-${year}`;

return result;

}

OIC - Convert dd-MMM-yy to MM-dd-yyyy date format using Call Template in Oracle Integration Cloud (OIC)

Convert dd-MMM-yy to MM-dd-yyyy Using Call Template in Oracle Integration Cloud (OIC)

Use Case:

In Oracle Integration Cloud (OIC), you often need to convert date formats between systems. For example, converting 17-Apr-25 (dd-MMM-yy) to 04-17-2025 (MM-dd-yyyy) for downstream applications like ERP or third-party APIs.

OIC's default XSLT functions do not directly support converting custom date formats like dd-MMM-yy to MM-dd-yyyy. You could use JavaScript, but that may not be reusable across integrations. Instead, Call Templates provide a centralized, reusable solution that improves maintainability and avoids logic duplication across flows.

Codes:

Caller template:

<xsl:template match="/">

  <ns0:response-wrapper>

    <ns0:output>

      <xsl:variable name="NewDate">

        <xsl:call-template name="formatDateTime">

          <xsl:with-param name="DateTime" select="/ns0:request-wrapper/ns0:Input"/>

        </xsl:call-template>

      </xsl:variable>

      <xsl:value-of select="$NewDate"/>

    </ns0:output>

  </ns0:response-wrapper>

</xsl:template>

Reusable called template:

<xsl:template name="formatDateTime">

  <xsl:param name="DateTime"/>

  <xsl:choose>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Jan'"><xsl:text>01</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Feb'"><xsl:text>02</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Mar'"><xsl:text>03</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Apr'"><xsl:text>04</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='May'"><xsl:text>05</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Jun'"><xsl:text>06</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Jul'"><xsl:text>07</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Aug'"><xsl:text>08</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Sep'"><xsl:text>09</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Oct'"><xsl:text>10</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Nov'"><xsl:text>11</xsl:text></xsl:when>

    <xsl:when test="substring-before(substring-after($DateTime,'-'),'-')='Dec'"><xsl:text>12</xsl:text></xsl:when>

  </xsl:choose>

  <xsl:text>-</xsl:text>

  <xsl:choose>

    <xsl:when test="string-length(substring-before($DateTime,'-')) = 1">

      <xsl:value-of select="concat('0',substring-before($DateTime,'-'))"/>

    </xsl:when>

    <xsl:otherwise>

      <xsl:value-of select="substring-before($DateTime,'-')"/>

    </xsl:otherwise>

  </xsl:choose>

  <xsl:text>-</xsl:text>

  <xsl:value-of select="concat('20',substring-after(substring($DateTime,oraext:last-index-within-string($DateTime,'-')),'-'))"/>

</xsl:template>

Screenshots:

Testing:

Reference:

https://forums.oracle.com/ords/apexds/post/transform-date-from-dd-mon-yy-to-yyyy-mm-dd-4201

OIC - Fixing WSDL Import and Element Errors While Configuring External Partner Adapters in Oracle Integration (OIC)

Fixing WSDL Import and Element Errors While Configuring External Partner Adapters in Oracle Integration (OIC)

Use Case:

An external partner has provided a WSDL for integration. While trying to create a connection in Oracle Integration Cloud (OIC) using the SOAP adapter and uploading the provided WSDL, errors such as missing imports or undefined elements occur. These issues prevent successful adapter configuration and integration setup.

Problem Statement:

During SOAP adapter configuration in OIC, uploading the partner-provided WSDL throws errors like:

• "Failed to import WSDL: Could not resolve import..."
• "Element XYZ not defined in the schema..."

This is typically due to:

• Missing referenced XSD or WSDL files.
• Incorrect or inaccessible schema locations.
• Unresolvable namespace dependencies.

Solution Steps:

Step 1: Collect All WSDL and XSD Dependencies

• Request the external partner to share a complete WSDL bundle including all dependent XSD and WSDL files.
• Alternatively, download all externally referenced schema files from the URLs defined in the WSDL (if accessible).

Step 2: Update Import References (If Needed)

• Edit the main WSDL to correct any broken <xsd:import> or <wsdl:import> paths.
• Use Notepad++ or XML editors to verify and fix namespaces and schema locations.

Step 3: Validate WSDL Using a WSDL Parser or SoapUI

• Open the WSDL in tools like SoapUI or Altova XMLSpy to ensure it parses successfully before uploading to OIC.

Step 4: Create Connection in OIC

• Go to Connections > Create a new SOAP connection.
• Use the hosted WSDL URL (with all dependencies accessible).
• Test the connection to ensure it's configured without error.

Step 5: Build Integration

• Create a new integration and use the configured connection.
• Configure the SOAP adapter and validate that all operations and messages are correctly displayed.
• Proceed with integration mapping and testing.

Detailed screenshots:

Few of the error examples with solutions

Issue1: one message part was missing

Solution1: Added the missing message part.

Issue2: unused import element with no actual import elements.

Solution2: Removed the import element

Issue3: Restriction element was using same namespace what import element using

Solution 3: removed that restriction part.

OIC - Automated Notification for Zero-Size Files in OIC SFTP Polling

Automated Notification for Zero-Size Files in OIC SFTP Polling

Usecase: 

In many integrations, files are regularly polled from an SFTP server and processed further. However, sometimes empty (zero-byte) files may be placed due to upstream issues or accidental drops. Processing such files can cause failures or incomplete data flow. To handle this proactively, we need to automatically detect zero-size files during polling and send immediate notifications to relevant teams, enabling quick corrective action.

Solution Steps:

1. List Files from SFTP
   Use the FTP Adapter in List operation to fetch all available files.

2. Loop Through File List
   Use a For-Each action to iterate through each file entry.

3. Check File Size
   Access the FileSize attribute. If size is 0, it's an empty file.

4. Send Email Notification
   Use the Email Adapter to notify recipients that a zero-size file was detected.

5. Optional: Log or Archive
   Store the file details or move the empty file to a separate folder for tracking.

In this integration, we list files from the SFTP location, check the file size using a conditional check, and if the size is zero, we use the 'throw new fault' action to raise an error. This triggers the Global Fault Handler, which then sends an email notification indicating that a zero-size file was received.

OIC Gen3 - How To Extend Oracle Integration Cloud Gen3 Accelaretor Project

Oracle Integration 3 is a fully managed, preconfigured environment that gives you the power to integrate your cloud and on-premises applications also known as OIC.

In OIC Gen3, you can extend (customize) an integration in an accelerator project by adding and configuring an extension group. An extension group enables you to extend your integration by adding the following actions to the integrations in your accelerator project.

1. Invoke connections; 
2. Data stitch, 
3. for-each, 
4. switch, 
5. map
6. integration actions; and global variables 

More details on PDF:

https://drive.google.com/file/d/10MiKZx6l1Afjdb8hz7CWdJhaKVx9lda1/view?usp=drivesdk

Reference:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=222723139129443&parent=EXTERNAL_SEARCH&sourceId=REFERENCE&id=3017378.1&_afrWindowMode=0&_adf.ctrl-state=ml7w8ezmi_4

OIC - How to List Files from Object Storage in OIC Without Using Wildcard Patterns

Use Case:

In OIC, you need to list files from Object Storage. However, the Object Storage adapter does not support wildcard patterns (e.g., *.csv, *.txt) in the file name field. You still need to retrieve and process a group of related files dynamically.

---

Solution Steps:

1. Get Prefix from Lookup: Store and retrieve the required fixed prefix from a Lookup table (e.g., invoice_, report_2025-04-), making the integration dynamic and configurable.
2. List Files Using Prefix: Configure the Object Storage adapter to list files using this fixed prefix. This will return all files that start with the given prefix.
3. Pass Each File to Main Integration: Use a For-Each loop to iterate through the list of files, and call the main integration for each file with the filename as a parameter.
4. Optional Filtering in Logic: If needed, apply additional filtering inside the loop based on extension, date, or naming pattern using simple conditions.

Detailed screenshots:

Integration flow

Configure object storage invoke adapter and add prefix query parameter.

Map the prefix, fetched from lookup or a combination of lookup and dynamic data.

OIC - How to Send Email Using Twilio SendGrid API in Oracle Integration Cloud (OIC)

How to Send Email Using Twilio SendGrid API in Oracle Integration Cloud (OIC)

Use Case

You want to send transactional or notification emails from an OIC integration using SendGrid, a cloud-based email service by Twilio. This method is REST-based, reliable, and gives full control over the email content and delivery.

Why we should opt for Twilio sendgrid over traditional OIC built-in SMTP mail.

• Rich HTML & Attachments: SendGrid supports advanced HTML emails, attachments, and templates; OIC SMTP is basic.
• Email Tracking: SendGrid gives open/click stats; OIC SMTP has no tracking.
• High Deliverability: SendGrid ensures better inbox delivery; SMTP can be throttled or blocked.
• Secure Auth: SendGrid uses API keys; SMTP often uses plain credentials.
• Scalability: SendGrid handles bulk emails; SMTP suits low volume.
• Use SMTP for simple, internal alerts.
• Use SendGrid for professional, scalable, and trackable emails.

Solution Steps

1. Get Your SendGrid API Key

• Log in to SendGrid Dashboard.
• Go to Settings > API Keys > Create API Key.
• Choose “Full Access” or customize scopes as needed.
• Copy and save the API key securely.

2. Create a REST Connection in OIC for SendGrid

1. Go to Connections > Create in OIC.
2. Choose REST Adapter.
3. Enter a name like SendGrid_REST_API.
4. Set Connection Type: Trigger and Invoke.
5. In Connection Properties:
   • Configure URL: https://api.sendgrid.com
6. Under Security:
   • Security Policy: API key based authentication
   • Provide api key
7. Test and save the connection.

3. Use the Connection in Your Integration

• Create an App-Driven or Scheduled Integration.
• Drag the SendGrid_REST_API as an Invoke.
• Choose operation: POST
• Resource Path: /v3/mail/send
• Request Media Type: application/json or text/html

4. Map Your Email Payload

Sample JSON:

{
  "personalizations": [
    {
      "to": [
        {
          "email": "test@test1.com,test1@test2.com",
          "name": "Test"
        }
      ],
      "cc": [
        {
          "email": "test@test.com",
          "name": "test"
        }
      ]
    }
  ],
  "from": {
    "email": "test@test.com",
    "name": "Example Order Confirmation"
  },
  "subject": "Your Example Order Confirmation",
  "content": [
    {
      "type": "text/html",
      "value": "

<p>Hello from Twilio SendGrid!</p>

<p>Sending with the email service trusted by developers and marketers for

 <strong>time-savings</strong>, 

<strong>scalability</strong>, 

and <strong>delivery expertise</strong>.

</p><p>%open-track%</p>"
    }
  ],
  "attachments": [
    {
      "content": "Base64 content",
      "filename": "index.html",
      "type": "text/html",
      "disposition": "attachment"
    }
  ],
  "categories": ["cake", "pie", "baking"]
}

Map values dynamically if needed using XSLT mapper.

5. Test the Integration

• Activate and run the integration.
• Check your SendGrid dashboard for delivery status.

Detailed screenshots:

Create sendgrid rest Connection:

Create an app driven common integration:

Send grid rest api adapter configuration:

Configure trigger. We can use same above payload and customize as our need:

This common SendGrid email service can then be reused across other integrations wherever there's a requirement to send error or notification emails.

In our integration, if no files are found while listing from the SFTP location, an error is thrown and caught by the global fault handler. From there, we invoke a common SendGrid email service to send the error details via email.

Mapping:

We can keep all the information in a lookup and fetch them during mapping.

Reference:

https://www.twilio.com/docs/sendgrid/api-reference/mail-send/mail-send

OIC - Converting standard DateTime to Unix Time in OIC Using XSLT functions

Use Case

In Oracle Integration Cloud (OIC), there are scenarios where we need to convert a standard DateTime value into Unix time (Epoch time), which represents the number of seconds elapsed since January 1, 1970, UTC. This is commonly required when integrating with APIs that accept timestamps in Unix format.

Solution Steps

To achieve this conversion, we use XPath expressions within OIC, leveraging xsd:dateTime, xp20:format-dateTime, fn:current-dateTime(), and xsd:dayTimeDuration.

Expression Used

floor (((xsd:dateTime (xp20:format-dateTime (fn:current-dateTime(), 

"[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z" ) ) 
- xsd:dateTime ("1970-01-01T00:00:00Z" )) 

div xsd:dayTimeDuration ("PT1S") ))

Breakdown of the Expression

1. fn:current-dateTime() → Retrieves the current DateTime in the integration.
2. xp20:format-dateTime(..., "[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z") → Formats it to a standard UTC DateTime format.
3. xsd:dateTime(...) - xsd:dateTime("1970-01-01T00:00:00Z") → Calculates the difference between the given DateTime and the Unix epoch (January 1, 1970).
4. div xsd:dayTimeDuration ("PT1S") → Converts the duration to seconds.
5. floor(...) → Ensures the result is an integer by rounding down.

Example Output

If the current DateTime is 2025-04-04T12:30:45Z, the XPath expression will return the Unix timestamp 1743772245.