OIC - Converting standard DateTime to Unix Time in OIC Using XPath

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.

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:

1. Download the cryptojs file from https://github.com/ihsmarkitosi/CryptoJS-v3.1.2/blob/master/rollups/aes.js
2. Use the below mentioned encrypt and decrypt code snippet.
3. 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:

Note: you can also take below updated google cryptojs (rollups /aes.js file)

https://code.google.com/archive/p/crypto-js/downloads

Used in OIC Integrations:

Encryption common service:

Integration flow:

Configure trigger

Get AES Key from vault secret

Call javascript action

Call sign service for RSA vault

Decryption common service

Integration flow

Configure trigger

Verify service

Throw new fault if varification failed.

Get AES Key from vault secret

Call Javascript

Connection details:

OIC - Extracting File Name Before the Last Dot and Changing extension from .txt to .csv in OIC

Use Case

In Oracle Integration Cloud (OIC), files are often received with a specific naming convention, such as "abc.01287568371133.yyyymmdd.txt" However, some business processes require converting these .txt files into .csv format dynamically before further processing. Instead of hardcoding file names, we can extract and replace the file extension using XPath functions.

Solution Steps

1. Extract the File Name:

   • Use XPath to fetch the file name from the payload.
   • Example: /ns0:execute/ns3:request-wrapper/ns3:ProcessRequest/ns3:FileDetails/ns3:FileName

2. Find the Last Occurrence of a Dot (.):

   • Identify the position of the last dot (.) in the file name to locate the extension.
   • Function used: oraext:last-index-within-string()

3. Extract the Base Name:

   • Use fn:substring() to retrieve the file name without the extension.

4. Concatenate with .csv:

   • Append .csv to the extracted base name using fn:concat().

Final Expression:

fn:concat(fn:substring(ns3:FileName,1, 

oraext:last-index-within-string(ns3:FileName, '.')),
'.csv')

This converts abc.01287568371133.yyyymmdd.txt → abc.01287568371133.yyyymmdd.csv

OIC - Adjusting Dates in XML Transformations Using XSD Date Functions

Use Case:

In enterprise integrations, especially in Oracle Integration Cloud (OIC) or other XML-based transformation engines, there is often a need to manipulate date values dynamically. One such scenario is adjusting a business date by subtracting or adding a specific duration.

For instance, an XML payload may contain a business date, and a downstream system requires processing based on the previous day's date. The transformation logic should ensure the correct date adjustment while maintaining XML schema compliance.

Solution Steps:

1. Identify the Input Field:

   • The source XML contains a BusinessDate field under the request-wrapper node.
   • The XPath expression to access this field is:

     /nstrgmpr:execute/ns20:request-wrapper/ns20:BusinessDate
     

2. Apply the XSD Date Function:

   • To ensure that the extracted value is interpreted correctly as a date, we use the xsd:date() function.
   • This ensures type safety in XML transformations.
   • It supports value as YYYY-MM-DD or YYYY-MM-DDZ

3. Subtract One Day from the Business Date:

   • The xsd:dayTimeDuration("P1D") function represents a one-day duration.
   • Using the subtraction operator (-), we deduct one day from the business date.

4. Final Transformation Expression:

   • This expression dynamically computes the previous day’s date from the given BusinessDate. xsd:date(/nstrgmpr:execute/ns20:request-wrapper/ns20:BusinessDate) - xsd:dayTimeDuration("P1D")

5. Integrate the Expression in OIC or XSLT Transformations:

   • This logic can be used in XSLT mappings, Oracle Integration Cloud (OIC) expressions, or any XML transformation engine that supports XSD functions.

Screenshot:

OIC - Processing files using Multiple File Name Patterns in OIC File or FTP Adapter

Processing files using Multiple File Name Patterns in OIC FTP Adapter

Use Case

We have a requirement in Oracle Integration Cloud (OIC) to poll two different files with specific naming patterns from an FTP or file system. The files have different prefixes but share a common date pattern (YYYYMMDD). The integration should only process the files when both exist. If one of the files is missing, the integration should throw a fault and exit.

Example File Name Patterns:

• File 1: abc_YYYYMMDD*
• File 2: XYZ_YYYYMMDD*
• File Polling Pattern: abc_YYYYMMDD*|XYZ_YYYYMMDD*

Solution Approach

Step 1: Configure File Adapter to list for  Both File Patterns

• We have used a lookup and put the file name pattern as abc_YYYYMMDD*|XYZ_YYYYMMDD*
• configure the ftp adapter with list operation and map the file name pattern from the lookup. We are also replacing the YYYYMMDD with a fileProcessing date.
• This configuration ensures the adapter picks up files matching either of the patterns.

Step 2: Handle Missing Files with a throw New Fault action

• If either file1Count or file2Count is 0, use a Throw New Fault action: throw new Fault("Both files are required for processing, but one or more are missing.")
• This ensures that the integration stops if any of the required files are missing.
• Add the following logic in the Skip Condition to ensure processing only when both files are present: file1Count > 0 AND file2Count > 0.

Detailed screenshots:

FTP list mapping:

Throw New Fault

OIC - How to configure Dynamic Decryption in OIC

Configure Dynamic Decryption in OIC Based on IsEncrypted schedule Parameter

Use Case

A business receives both encrypted and plain files from an SFTP server. To process both type of files, the integration will use a schedule parameter (IsEncrypted), which will be set to:

'Y' → If the file is PGP encrypted and needs decryption.

'N' → If the file is plain text and does not need decryption.

Goal

• Download the file using the SFTP Adapter.
• Dynamically set the "Decrypt File" option in the Stage File action using the IsEncrypted parameter from the mapper.
• If IsEncrypted = 'Y', enable PGP decryption by setting "true".
• If IsEncrypted = 'N', do not decrypt the file ("false").

Step-by-Step Configuration

1. Add the SFTP Adapter to Download the File

• Drag and drop the SFTP Adapter in the integration.
• Select Download File operation.
• Configure SFTP details (host, authentication, file directory, etc.).
• Store the file in Stage File for processing.

2. Add a Stage File Action to Read the File

• Add a Stage File Action after the SFTP Adapter.
• Select Read File operation.

3. Configure Decryption Dynamically in the Mapper

• Click on Mappings in the Stage File Read action.
• Locate the "Decrypt File" field in the mapping.
• Set the value dynamically using the IsEncrypted parameter:

Expression in Mapper (If-Else Condition)

if ($IsEncrypted = 'Y') then 'true' else 'false'

• This ensures that if IsEncrypted = 'Y', decryption is enabled ("true").
• If IsEncrypted = 'N', the file is read as is ("false").

4. Select PGP Key for Decryption (If Needed)

• If decryption is enabled (true), configure:
  • PGP Private Key (Uploaded in OIC Security).
  • PGP Passphrase.

5. Process the Decrypted or Plain File

• Store the file in Object Storage, Database, or another system.
• Use OIC File Adapter, REST API, or another SFTP Adapter to move/process the file.

Screenshots: