OIC - Padding leading zeros to a number field using xslt format-number()

In many payment-related integrations, credit card numbers often arrive as plain numeric strings. For security and compliance—and to meet target schema requirements—we need to standardize their format (e.g., always output 16‑digit strings, padding with leading zeros when necessary). In this post, we’ll explore a simple XSLT/Expression Builder approach in Oracle Integration Cloud to achieve consistent credit card number formatting.

---

🎯 Use Case

• Source System: A front-end or legacy application that sends credit‑card numbers without leading zeros (e.g., “123456789012345”).
• Target System: A downstream ERP or card‑processing API requiring exactly 16 digits (e.g., “0123456789012345”).
• Challenge: Ensure every card number string is left‑padded with zeros up to 16 characters, without manually writing complex code.

---

🚀 Solution Steps

1. Locate the Target Element
   In your OIC mapping or XSLT, identify the node where the formatted card number should go (e.g., <ns24:ExternalCardNumber>).

2. Use the fn:format-number() Function
   Leverage the built‑in XPath function in OIC’s Expression Builder to pad the numeric string. The format mask for 16 digits is simply 16 zeros: "0000000000000000".

3. Write the Expression
   In the Expression Builder for ns24:ExternalCardNumber, enter:

   fn:format-number(ns22:CreditcardNumber, "0000000000000000")
   

   • Argument 1: ns22:CreditcardNumber – the incoming numeric string
   • Argument 2: "0000000000000000" – the format mask specifying 16-digit width, padded with leading zeros

4. Test with Sample Payloads

   • Input: <CreditcardNumber>123456789012345</CreditcardNumber> → Output: <ExternalCardNumber>0123456789012345</ExternalCardNumber>
   • Input: <CreditcardNumber>987654321</CreditcardNumber> → Output: <ExternalCardNumber>0000000987654321</ExternalCardNumber>

5. Deploy and Validate

• Activate your integration and send real messages.
• Verify target payloads always carry exactly 16 digits, meeting downstream requirements.

OIC - How to Add Blank Spaces When Creating Fixed-Length Files using javascript

Use Case:

When building integrations in Oracle Integration Cloud (OIC), you might need to generate fixed-length flat files. In such files, each field must occupy a specific number of characters—even if the data is shorter or sometimes missing.

Let’s say a field requires 10 characters, but the actual value is only 6 characters. In this case, you need to add 4 blank spaces to maintain the correct file structure.

To solve this, you can use a custom JavaScript function to generate blank spaces and pad the fields properly.

Solution: How to Add Blank Spaces (Padding) in Fixed-Length Output

You can write a small JavaScript function that returns a string of spaces based on a given length.

Here’s a sample function:

function clp_getSpaces(length) {
    var spaces = ' '.repeat(length);
    return spaces;
}

What This Does:

• Takes a number (length) as input.
• Uses the .repeat() method to return that many blank spaces.

Steps to Use in Integration:

Step 1: Create the JavaScript Function

Use the function above in your utility library or directly in your integration if scripting is allowed.

Step 2: Use It While Constructing the Flat File Line

For example:

var employeeName = 'John';
var paddedName = employeeName + clp_getSpaces(10 - string-length(employeeName));

This ensures that even if employeeName is short, the total field length is still 10 characters.

Step 3: Write to File Using Stage Activity

Use this padded string as input to your stage file write operation. This will ensure correct alignment of all fields in your fixed-length file.

Why This Is Important:

Fixed-length files are strict in formatting. If the alignment is off, downstream systems might reject the file or misread the data. Padding with spaces ensures compliance with the required structure.

[OIC Error Fix] Fixed length File Translation Failure While Writing File: 'Element CreationDate Not Expected'

Fixing Fixed-Length File Translation Error in OIC: "Element 'CreationDate' Not Expected"

Use Case:

You’re building an integration in Oracle Integration Cloud (OIC) that writes data to a fixed-length flat file using the Stage File action. The NXSD schema includes a field called CreationDate. However, during runtime, OIC throws this error:

Translation Failure: Element 'CreationDate' not expected. The data does not conform to the NXSD schema.

Root Cause (Simple Explanation):

In fixed-length files, every field must appear in the exact order and position as defined in the NXSD schema.

If any field before CreationDate is:

• Not mapped
• Mapped incorrectly
• Mapped with the wrong length

… then the data will shift in the output file. As a result, CreationDate shows up at the wrong spot — making OIC think it’s unexpected.

Solution Steps:

Step 1: Review NXSD Schema

• Open the NXSD file used in the Stage File activity.
• Check the order of the fields and their lengths.
• Confirm that CreationDate is present and has the right definition.

Step 2: Check Field Mappings

• Go to the mapping just before the Stage File action.
• Make sure all fields before CreationDate are mapped.
• If a field is optional and not present in the source:
  • Map it to a blank value with the correct length (e.g., ' ' for a 5-character field).

Step 3: Test the Output

• Use the Test button or enable tracing to check the actual output string.
• Verify if the data aligns with the fixed-length format.

Step 4: Regenerate NXSD (if needed)

• If you recently changed the schema, re-import or reconfigure the Stage File activity to ensure it uses the latest NXSD version.

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

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) and assign the **serviceDeveloper** role to developers who need edit access.
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. We can also share integrtaions from COMMON project and then we can copy them into another project.
5. We can also invoke shared integrtaions as local integration call from other projects.

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
• Assign access edit, view and monitor role as expected.

2. Add Connections in Common Project:

• Open the CommonResources project
• Go to Connections
• Click “+ Create”, configure REST, SOAP, HCM, ERP, etc.
• Share with other projects.
• 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

we can also copy one integration to another project as below:

Share the integration:

Copy to the project as required.

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