soalicious: 2025

Wednesday, April 23, 2025

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

Locate the Target Element
In your OIC mapping or XSLT, identify the node where the formatted card number should go (e.g., <ns24:ExternalCardNumber>).
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".
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
Test with Sample Payloads
- Input: <CreditcardNumber>123456789012345</CreditcardNumber> → Output: <ExternalCardNumber>0123456789012345</ExternalCardNumber>
- Input: <CreditcardNumber>987654321</CreditcardNumber> → Output: <ExternalCardNumber>0000000987654321</ExternalCardNumber>
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.

Monday, April 21, 2025

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:

Create multiple domain-specific projects (e.g., HR, Finance, Procurement).
Create a common project (e.g., CommonResources) that holds shared connections like HCM, ERP, REST, FTP.
Use shared connections from the common project in other integration projects.
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:

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

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

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}">

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>

Thursday, April 17, 2025

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

Wednesday, April 16, 2025

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.

Friday, April 11, 2025

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:

List Files from SFTP
Use the FTP Adapter in List operation to fetch all available files.
Loop Through File List
Use a For-Each action to iterate through each file entry.
Check File Size
Access the FileSize attribute. If size is 0, it's an empty file.
Send Email Notification
Use the Email Adapter to notify recipients that a zero-size file was detected.
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.

Tuesday, April 8, 2025

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.

Invoke connections;
Data stitch,
for-each,
switch,
map
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:

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.
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.
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.
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.

Monday, April 7, 2025

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

Go to Connections > Create in OIC.
Choose REST Adapter.
Enter a name like SendGrid_REST_API.
Set Connection Type: Trigger and Invoke.
In Connection Properties:
- Configure URL: https://api.sendgrid.com
Under Security:
- Security Policy: API key based authentication
- Provide api key
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

Friday, April 4, 2025

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

fn:current-dateTime() → Retrieves the current DateTime in the integration.
xp20:format-dateTime(..., "[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z") → Formats it to a standard UTC DateTime format.
xsd:dateTime(...) - xsd:dateTime("1970-01-01T00:00:00Z") → Calculates the difference between the given DateTime and the Unix epoch (January 1, 1970).
div xsd:dayTimeDuration ("PT1S") → Converts the duration to seconds.
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.

Friday, March 28, 2025

Types of encryption techniques

There are several types of encryption techniques used to secure data, each with its own use case and strengths:

1. Symmetric Encryption

Uses a single key for both encryption and decryption.

Examples: AES (Advanced Encryption Standard), DES (Data Encryption Standard), 3DES (Triple DES), Blowfish, Twofish

2. Asymmetric Encryption

Uses a pair of public and private keys for encryption and decryption.

Examples: RSA (Rivest-Shamir-Adleman), ECC (Elliptic Curve Cryptography), Diffie-Hellman, ElGamal

3. Hashing

Transforms data into a fixed-length hash that cannot be reversed.

Examples: SHA (Secure Hash Algorithm), MD5 (Message Digest Algorithm), bcrypt, Argon2

4. End-to-End Encryption (E2EE)

Ensures data remains encrypted throughout transmission and can only be decrypted by the intended recipient.

Examples: Signal Protocol, PGP (Pretty Good Privacy), TLS (Transport Layer Security)

5. Homomorphic Encryption

Allows computation on encrypted data without decrypting it first.

Examples: Paillier Cryptosystem, BGV (Brakerski-Gentry-Vaikuntanathan), CKKS (Cheon-Kim-Kim-Song)

6. Quantum Encryption

Leverages quantum mechanics for ultra-secure encryption.

Examples: Quantum Key Distribution (QKD), BB84 Protocol

Each encryption type serves a specific purpose, from securing online communication to protecting stored data.

OIC - AES encryption and decryption in javascript using CryptoJS

Use Case

A source application sends an AES-encrypted and RSA-signed request to an OIC REST service, including a cipher key, encrypted message, and IV. OIC retrieves the AES key from Vault secrets, decrypts the data using JavaScript, processes it, then encrypts and signs the response before sending it back.

We use JavaScript (CryptoJS) to encrypt and decrypt messages with IV and fetch the AES key from Vault. For signing and verification, we use the Vault RSA sign key.

In today's digital world, encryption is essential for data security. This blog explains how to use CryptoJS for AES-256 encryption/decryption and Vault for secure key management and RSA signing.

Steps to follow:

Download the cryptojs file from https://github.com/ihsmarkitosi/CryptoJS-v3.1.2/blob/master/rollups/aes.js
Use the below mentioned encrypt and decrypt code snippet.
First place step1 cryptojs code and then add step2 code. Test it in Online tool to test javascript: https://onecompiler.com/javascript

Encrypt and decrypt code snippet:

function EncryptAESCBC(plaintext, aesKey, aesIV) {

var key = CryptoJS.enc.Utf8.parse(aesKey);

var iv = CryptoJS.enc.Utf8.parse(aesIV);

// Encrypt the plaintext

var cipherText = CryptoJS.AES.encrypt(plaintext, key, {

iv: iv,

mode: CryptoJS.mode.CBC

//padding: CryptoJS.pad.NoPadding

});

var encryptedString = cipherText.toString();

return encryptedString;

}

function DecryptAESCBC(cipherText, aesKey, aesIV) {

// IV is a base64 string

var key = CryptoJS.enc.Utf8.parse(aesKey);

var iv = CryptoJS.enc.Utf8.parse(aesIV);

var cipherBytes = CryptoJS.enc.Base64.parse(cipherText);

var decrypted = CryptoJS.AES.decrypt({ciphertext: cipherBytes}, key, {

iv: iv,

mode: CryptoJS.mode.CBC

//padding: CryptoJS.pad.Pkcs7

});

var decryptedString = decrypted.toString(CryptoJS.enc.Utf8);

return decryptedString;

}

//console.log(DecryptAESCBC(EncryptAESCBC('YWFhYWFhYWFhYWFhYWFhYQ', 'h3Hv332dw8JYJcdx', 'aDNIdjMzMmR3OEpZSmNkeA=='), 'h3Hv332dw8JYJcdx', 'aDNIdjMzMmR3OEpZSmNkeA=='));

Screenshot: