OIC: Extract File Name from File Path Using replace() and Regular Expression

Introduction

In Oracle Integration Cloud (OIC), there are situations where an API returns a list of files along with their complete directory paths.

For example:

/archive/inbound/file1.csv,

/archive/inbound/file2.csv,

/archive/outbound/file3.csv

If we only need the file names and want to remove the directory path, we can use the OIC replace() function with a regular expression.

The expression used in this use case is:

replace( oraext:create-delimited-string($Var_FinalFileList/nsmpr2:executeResponse/ns29:response-wrapper/ns29:objects/ns29:name, "," ),"[^,]*/","")

Use Case

Suppose an OIC REST/API response returns multiple file names with their complete paths.

Input

/archive/inbound/File_001.csv,

/archive/inbound/File_002.csv,

/archive/outbound/File_003.csv

We want to remove the directory path and get:

File_001.csv,File_002.csv,File_003.csv

This can be achieved using replace().

Solution

The main expression is:

replace(

  oraext:create-delimited-string(

    $Var_FinalFileList/nsmpr2:executeResponse/ns29:response-wrapper/ns29:objects/ns29:name,

    ","

  ),

  "[^,]*/",

  ""

)

Let's break it into two parts.

Step 1: Create a Comma-Separated String

First, we use:

oraext:create-delimited-string($Var_FinalFileList/nsmpr2:executeResponse/ns29:response-wrapper/ns29:objects/ns29:name, ",")

The XPath:

$Var_FinalFileList/nsmpr2:executeResponse/ns29:response-wrapper/ns29:objects/ns29:name

retrieves the file names/paths from the response.

oraext:create-delimited-string() combines multiple values into a single string using the specified delimiter.

Here the delimiter is: ,

Example

If the response contains:

/archive/inbound/File_001.csv

/archive/inbound/File_002.csv

/archive/outbound/File_003.csv

the function creates:

/archive/inbound/File_001.csv,/archive/inbound/File_002.csv,/archive/outbound/File_003.csv

Step 2: Use replace() to Remove the Path

Now we apply:

replace(

   <comma-separated-string>,

   "[^,]*/",

   ""

)

The important part is the regular expression:

[^,]*/

What does it mean?

[^,] :Any character except comma

*: Zero or more occurrences

/: Forward slash

Therefore:

[^,]*/

matches the directory/path portion before the file name, while stopping at the comma separating the next value.

Example

Input:

/archive/inbound/File_001.csv

The matched portion is:

/archive/inbound/

After replacing it with an empty string:

File_001.csv

Complete Example

Input

/archive/inbound/File_001.csv,

/archive/inbound/File_002.csv,

/archive/outbound/File_003.csv

Output

File_001.csv,File_002.csv,File_003.csv

In simple terms

First convert all file-path values into one comma-separated string, then use replace() with a regular expression to remove everything up to the last / for each comma-separated value, leaving only the file names.

This is a simple and useful OIC XPath/XSLT expression technique when working with API responses containing multiple file paths.

OIC SFTP: Generate SSH Private/Public Key Pair for WinSCP Access

Introduction

When we need to access an OIC SFTP endpoint from WinSCP using SSH key-based authentication, we need to generate an SSH key pair.

The key pair consists of:

Private Key – stored securely on the client/WinSCP machine.

Public Key – provided/configured on the SFTP side for the respective user.

WinSCP – uses the private key during SFTP authentication.

OIC SFTP – uses the corresponding public key to authenticate the client.

There are two commonly used approaches to generate the SSH key pair:

1. Windows Command Prompt using ssh-keygen
2. PuTTYgen

1. Architecture / High-Level Flow

Flow

             KEY GENERATION

                   |

        +----------+----------+

        |                     |

   ssh-keygen              PuTTYgen

   Command Prompt          Windows GUI

        |                     |

        +----------+----------+

                   |

             SSH Key Pair

          +--------+--------+

          |                 |

     Private Key        Public Key

          |                 |

          |                 +----> Configure on

          |                       SFTP/OIC side

          |

          +----> Configure in WinSCP

                         |

                         |

                  SFTP over SSH

                         |

                         v

                  OIC SFTP Endpoint

Important: The private key must be kept secure and should never be shared with the SFTP server or other unauthorized users.

Option 1 – Generate SSH Key Using ssh-keygen

Windows provides the OpenSSH ssh-keygen utility, which can be used from Command Prompt.

Step 1: Open Command Prompt

Open Command Prompt and execute:

ssh-keygen -t rsa -b 2048 -m pem

What does the command mean?

ssh-keygen

   |

   +-- -t rsa       → RSA key type

   |

   +-- -b 2048      → 2048-bit key

   |

   +-- -m pem       → PEM output format

Step 2: Specify the Key File Name

The command will prompt:

Enter file in which to save the key

(C:\Users\<username>/.ssh/id_rsa):

Provide the required location and filename.

For example:

C:\Users\<username>\OneDrive - abc\John\TRN_John_OICSFTP_Key

If the file already exists, you may see:

... already exists.

Overwrite (y/n)?

Enter: y

only if you intentionally want to replace the existing key.

3. Enter the Passphrase

Next, the command prompts:

Enter passphrase (empty for no passphrase):

and: Enter same passphrase again:

You can configure a passphrase for additional protection.

However, before using the key with an automated integration/client, verify whether the target OIC SFTP/WinSCP configuration supports the selected private-key/passphrase setup.

4. Generated Files

After successful execution, two files are generated.

For example:

TRN_John_OICSFTP_Key

TRN_John_OICSFTP_Key.pub

Private Key

TRN_John_OICSFTP_Key

This is the private key.

It should be stored securely.

Public Key

TRN_John_OICSFTP_Key.pub

This is the public key that can be provided to the SFTP server/OIC-side user configuration.

Option 2 – Generate Key Using PuTTYgen

Another option is to use PuTTY Key Generator (PuTTYgen).

This is particularly useful when the Windows environment already uses PuTTY/WinSCP.

Step 1: Open PuTTYgen

Launch:

PuTTY Key Generator

Step 2: Select RSA

Under the key type, select:

RSA

Set the key size to:

2048 bits

Step 3: Generate

Click:

Generate

Move the mouse around the blank area until the key is generated.

Step4: Save the Private Key

Click:

Save private key

Save the file securely.

For example:

TRN_John_OICSFTP_Key.ppk

The .ppk file is the PuTTY/WinSCP private-key format.

Step5: Obtain the Public Key

PuTTYgen displays the public key in the section:

Public key for pasting into OpenSSH authorized_keys file

Copy the complete public-key text.

This public key is then configured/provided on the SFTP side according to the server/OIC SFTP configuration.

Step6: Using the Key in WinSCP

Once the key pair is generated, configure WinSCP.

WinSCP Configuration

Go to:

WinSCP

   ↓

New Site

   ↓

File Protocol: SFTP

   ↓

Host Name

   ↓

Port: 22

   ↓

User Name

Then go to:

Advanced

   ↓

SSH

   ↓

Authentication

Select the corresponding private key.

For PuTTYgen-generated keys, this will normally be the:

.ppk

file.

For OpenSSH keys, use the appropriate private-key format supported by your WinSCP version.

Step7: Authentication Flow

During the connection, WinSCP uses the private key for authentication.

Conceptually:

                 WinSCP

                   |

                   | Private Key

                   |

                   v

             SSH Authentication

                   |

                   v

             OIC SFTP Endpoint

                   |

                   | Validates against

                   | Public Key

                   v

             Authentication

                Successful

                   |

                   v

              SFTP Session

Conclusion

For OIC SFTP access through WinSCP, we can generate the SSH key pair either through Windows ssh-keygen or PuTTYgen.

Recommended practice: Keep the private key protected and share only the public key with the SFTP/OIC administrator.

OIC - Split Semicolon-Separated Values Using tokenize() in XSLT

Introduction

While developing integrations in Oracle Integration Cloud (OIC), we may receive a field containing multiple values separated by a delimiter such as a semicolon (;).

For example:

"ImpactedAccountID": [

  "5354181000",

  "7789253000;4889253000",

  "6823624000",

  "9235902000",

  "5091223000",

  "2279450000;3457280000;7236081000;7131391000",

  "7131391000",

  "7131391000",

  "3457280000",

  "5200100000"

]

In this scenario, simply looping through ImpactedAccountID will not split values such as:

7789253000;4889253000

into separate account IDs.

OIC XSLT provides the tokenize() function, which can be used to split a string based on a delimiter.

Use Case

We receive an incident payload containing an ImpactedAccountID array.

Some array elements contain a single account ID, while others contain multiple account IDs separated by semicolon (;).

Input

{

  "VoltageDipIncidentDate": "09-07-2026",

  "VoltageDipIncidentTime": "15:14",

  "IncidentID": "INC 1350000262",

  "ImpactedAccountID": [

    "5354181000",

    "7789253000;4889253000",

    "6823624000",

    "9235902000",

    "5091223000",

    "2279450000;3457280000;7236081000;7131391000",

    "7131391000",

    "7131391000",

    "3457280000",

    "5200100000",

    "5173284000",

    "4523013000",

    "3506184000",

    "4136450000",

    "3264642000"

  ]

}

Our requirement is to transform this into multiple individual accountId elements.

Solution

We can use the XSLT tokenize() function.

The basic syntax is:

tokenize(string, delimiter)

For our requirement:

tokenize(string(.), ';')

This tells XSLT: Take the current value and split it wherever a semicolon (;) is found.

XSLT Code

The following is the same logic used in the provided XSLT:

<xsl:for-each

    select="/nssrcmpr:execute/ns15:request-wrapper/ns15:ImpactedAccountID">

    <xsl:for-each select="tokenize(string(.), ';')">

        <ns23:accounts>

            <ns23:accountId>

                <xsl:value-of select="normalize-space(.)"/>

            </ns23:accountId>

        </ns23:accounts>

    </xsl:for-each>

</xsl:for-each>

How the Logic Works

There are two xsl:for-each loops.

1. Outer for-each

<xsl:for-each

    select="/nssrcmpr:execute/ns15:request-wrapper/ns15:ImpactedAccountID">

The outer loop iterates through every element of the ImpactedAccountID array.

For example, it receives values one by one:

5354181000

7789253000;4889253000

6823624000

9235902000

...

2. tokenize() splits the current value

Inside the outer loop:

<xsl:for-each select="tokenize(string(.), ';')">

Here: string(.) -- gets the current value.

The second parameter: ';' --defines the delimiter.

For example:

7789253000;4889253000

becomes:

7789253000

4889253000

Similarly:

2279450000;3457280000;7236081000;7131391000

becomes four separate values:

2279450000

3457280000

7236081000

7131391000

3. Create the Target Element

For every token, we create:

<ns23:accounts>

    <ns23:accountId>

        <xsl:value-of select="normalize-space(.)"/>

    </ns23:accountId>

</ns23:accounts>

The current token is represented by: .

Therefore:

<xsl:value-of select="normalize-space(.)"/>

writes the current account ID into accountId.

normalize-space() is useful to remove unnecessary leading/trailing whitespace.

Example

Suppose the input contains:

"ImpactedAccountID": [

    "5354181000",

    "7789253000;4889253000",

    "6823624000"

]

Processing

First iteration:

5354181000

tokenize() returns:

5354181000

Second iteration:

7789253000;4889253000

tokenize() returns:

7789253000

4889253000

Third iteration:

6823624000

returns:

6823624000

Output

The resulting XML will contain individual accountId values:

<ns23:accounts>    <ns23:accountId>5354181000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>   <ns23:accountId>7789253000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>    <ns23:accountId>4889253000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>   <ns23:accountId>6823624000</ns23:accountId>

</ns23:accounts>

Output for the Provided Sample

For the provided input, values such as:

7789253000;4889253000

are converted to:

<ns23:accounts>   <ns23:accountId>7789253000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>   <ns23:accountId>4889253000</ns23:accountId>

</ns23:accounts>

And:

2279450000;3457280000;7236081000;7131391000

becomes:

<ns23:accounts>  <ns23:accountId>2279450000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>   <ns23:accountId>3457280000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>   <ns23:accountId>7236081000</ns23:accountId>

</ns23:accounts>

<ns23:accounts>  <ns23:accountId>7131391000</ns23:accountId>

</ns23:accounts>

Complete Logic in Simple Terms

The complete processing can be understood as:

ImpactedAccountID array

        ↓

Outer for-each

        ↓

Pick one value

        ↓

Check/Split using tokenize()

        ↓

Delimiter = ;

        ↓

Create accountId for every token

        ↓

normalize-space()

        ↓

Final XML

Key XSLT statement

tokenize(string(.), ';')

means: Split the current string wherever ; occurs and process each resulting value separately.

Conclusion

The tokenize() function is very useful in OIC when an incoming field contains delimiter-separated values.

Instead of manually manipulating the string, we can use:

<xsl:for-each select="tokenize(string(.), ';')">

This approach allows OIC to handle both:

5354181000

and:

7789253000;4889253000

using the same XSLT logic.

Important: The outer for-each handles the original ImpactedAccountID array, while the inner for-each handles the individual values produced by tokenize().

OIC Utility service - Project & Connection Status Monitoring Using Factory APIs

OIC Project & Connection Status Monitoring Using Factory APIs

Overview

This solution automates OIC project and connection health monitoring using OIC Factory APIs.

The integration outlines:

1. Retrieves all projects configured for monitoring.
2. Retrieves connections for each project.
3. Checks the status of every connection.
4. Generates a consolidated CSV report.
5. Calculates Success/Failure counts.
6. Sends an email with the summary in the email body and the detailed report as an attachment.

High-Level Flow

Scheduler

    |

MAIN Integration

    |

Get Project List

    |

For Each Project

    |

Get Connections

    |

For Each Connection

    |

Get Connection Status

    |

Append Result to Report

    |

Calculate Success/Failure Count

    |

Send Email

   / \

Body  CSV Attachment

Detailed flow:

Scheduler Integration

Integration: SCH_Project_Connection_Monitor

The Scheduler integration triggers the Main integration based on the configured schedule.

SCH--> Invoke --> MAIN

Main Integration

Integration: MAIN_Project_Connection_Monitor

The Main integration performs the complete monitoring process.

Step 1 – Get Projects

Project information can be maintained/configured through an OIC Lookup.

MAIN >> Read Project Lookup >> Get Project List

The project list is then processed one by one.

Step 2 – Get Connections

For each project, invoke the OIC Factory API to retrieve its connections.

For Each Project >> Get Connections

Note: Here. We have have used hasmore looping concept as it can have huge nunber of projects.

Step 3 – Get Connection Status

For every connection, invoke the Factory API and capture the status. >> write into a file >> read the content >> apend each connection status 

Example:

Project       Connection             Status

------------------------------------------------

HCM_PROJECT   HCM_REST_CONNECTION    SUCCESS

HCM_PROJECT   UCM_CONNECTION         FAILURE

ERP_PROJECT   ERP_REST_CONNECTION    SUCCESS

For scope fault:

Step 4 – Generate Report

Append each connection result to a CSV file using the OIC Stage File action.

Project Name,Connection Name,Type,Status

HCM_PROJECT,HCM_REST_CONNECTION,REST,SUCCESS

HCM_PROJECT,UCM_CONNECTION,REST,FAILURE

ERP_PROJECT,ERP_REST_CONNECTION,REST,SUCCESS

Step 5 – Calculate Counts

Maintain counters during processing:

Total Projects

Total Connections

Success Count

Failure Count

Logic:

IF Status = SUCCESS

    SuccessCount++

ELSE

    FailureCount++

The processing continues even if an individual connection fails.

5. Email Notification

After all projects and connections are processed, send an email.

Email Body

OIC Connection Health Report

Execution Date    : 05-Sep-2026

Total Projects    : 5

Total Connections : 32

Successful        : 29

Failed            : 3

Overall Status    : FAILURE

Please find the detailed report attached.

The generated CSV report is attached to the email.

Example:

OIC_Connection_Status_20260905.csv

EmailBody:

<tr style="background-color:#D9EAF7;">

    <th>Project Identifier</th>

    <th>Success Count</th>

    <th>Failure Count</th>

</tr>

fn:concat($EmailBody,

'<tr>',

'<td>',$fO_project/ns22:project/ns22:ProjectIdentifier,

'</td>',

'<td style="color:green;font-weight:bold;text-align:center;">',

$fO_project/ns22:project/ns22:Success,

'</td>',

'<td style="color:red;font-weight:bold;text-align:center;">',

$fO_project/ns22:project/ns22:Failure,

'</td>',

'</tr>'

)

Benefits

This provides a single automated health report for the OIC environment without manually checking projects and connections. It can easily be extended to include failed connection details, error messages, environment name, project-wise counts, HTML email tables, and Teams/Slack notifications.

OIC - Calculate Difference Between Two Dates in Minutes

 In Oracle Integration Cloud (OIC), we may need to calculate the time difference between two date/time values and return the result in minutes.

Input Format

The Start Time and End Time are received in:

yyyy-MM-dd HH:mm:ss

Example:

Starttime: 2026-09-02 10:15:00

Endtime:   2026-09-02 11:45:00

XSLT Expression

Use the following expression in the OIC mapper:

xsd:integer(

  (

    xsd:dateTime(

      concat(

        replace(/nstrgmpr:execute/ns18:request-wrapper/ns18:endtime, " ", "T"),

        ":00"

      )

    )

    -

    xsd:dateTime(

      concat(

        replace(/nstrgmpr:execute/ns18:request-wrapper/ns18:starttime, " ", "T"),

        ":00"

      )

    )

  )

  div xsd:dayTimeDuration("PT1M")

)

How it works

• replace() changes the space between date and time to T.
• xsd:dateTime() converts the value into a date/time.
• End Time is subtracted from Start Time.
• xsd:dayTimeDuration("PT1M") represents 1 minute.
• div converts the duration into the number of minutes.
• xsd:integer() returns the final result as an integer.

Example

Start Time: 2026-09-02 10:15:00

End Time: 2026-09-02 11:45:00

Result: 90 minutes

This is useful when an OIC integration needs to calculate processing time, elapsed time, SLA duration, or execution duration between two timestamps.

OIC - Removing Base64 Padding (=) for JWT Generation in Oracle Integration Cloud (OIC)

When generating a JWT in Oracle Integration Cloud (OIC), the header and payload must be Base64URL encoded before creating the signature. Standard Base64 encoding adds = characters as padding to make the encoded string a multiple of four characters. However, the JWT specification does not allow these padding characters.

To remove the padding, use the following expression in an Assign action:

replace(encodeReferenceToBase64(FileReference), '=', '')

How It Works

encodeReferenceToBase64(FileReference) encodes the input into a standard Base64 string.

replace(..., '=', '') removes all = padding characters from the encoded value.

The resulting string is then used as the JWT header or payload before generating the signature.

Why Is This Required?

JWT uses Base64URL encoding, which differs from standard Base64 by:

Removing the = padding characters.

Using URL-safe characters (- and _) instead of + and / (if applicable).

Removing the padding ensures that the generated JWT follows the standard specification and can be successfully validated by external applications and APIs.

Using this simple expression helps generate JWT-compliant header and payload values directly within OIC, without requiring any additional custom code.

OIC - Generate SHA-256 Checksum Using JavaScript in OIC

Oracle Integration Cloud (OIC) provides a built-in checksum function to generate a SHA-256 hash without using any external library. This is useful when creating request signatures or validating message integrity.

JavaScript Action (SHA256Generator.js)

function checksum_sha256(inputStr) {
    var sha256_result = oic.checksum.sha256(inputStr, "sha-256");
    return sha256_result;
}

Use Cases

Generate SHA-256 checksum for REST requests.

Create secure hash values for authentication.

Verify message integrity before sending data.

This reusable JavaScript action can be called from any OIC integration whenever a SHA-256 checksum is required.

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/integrations-user/import-library-file.html#GUID-D9638CD4-ADCE-4C8A-B5B3-1969086E642E