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.