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

Notepad - Improve Productivity in Notepad++ by Managing Multiple Open Tabs

Introduction

When numerous files are open in Notepad++, not all tabs are visible in a single row. Enabling multi-line tabs allows Notepad++ to display open tabs across multiple lines, making file navigation quicker and more convenient.

Solution

To display all the tabs in multi lines:

1. Go to Settings → Preferences.
2. Select General.
3. Under the Tab Bar section, select multi line.

Benefits

Improved visibility of open files.

Faster navigation between documents.

Reduced chances of opening or editing the wrong file.

Better productivity when working with multiple project files.

This small configuration change can significantly improve your daily development experience, especially when working with large OIC projects containing many similar file names.

OIC - Oracle Integration Cloud (OIC): Automatically Create Folder Structures in OCI Object Storage | use of ends-with() function

 Introduction

When working with OCI Object Storage, we often need to create multiple folders and subfolders before uploading files. Although this can be done manually through the OCI Console, repeatedly logging in and creating folders becomes time-consuming, especially during project setup or migration activities.

In this blog, we'll build a reusable Oracle Integration Cloud (OIC) service that automatically creates the required folder hierarchy in an OCI Object Storage bucket.

Business Requirement

Create an OIC REST service that accepts:

• Object Storage Namespace
• Bucket Name
• An array of folder and subfolder paths

The integration will iterate through each folder path and create an empty placeholder object so that the folder structure is visible in Object Storage.

This eliminates the need to manually log in to OCI and create folders one by one.

Sample Request

{
  "namespace": "my_namespace",
  "bucketName": "integration-bucket",
  "folders": [
    "Inbound",
    "Outbound",
    "Archive",
    "Archive/Success",
    "Archive/Error",
    "Logs",
    "Reports/Daily",
    "Reports/Monthly"
  ]
}

Solution Design

The integration performs the following steps:

1. Expose a REST endpoint.
2. Read the Namespace, Bucket Name, and folder array.
3. Use a For-Each action to iterate through every folder path.
4. For each folder:
   • Append a trailing / if required.
   • Create a dummy empty object (0-byte file) using the OCI Object Storage Adapter.
5. Return a success response once all folders have been created.

Integration Flow

REST Trigger
      │
      ▼
Read Request Payload
      │
      ▼
For Each Folder
      │
      ▼
Create Empty Object in OCI Object Storage
      │
      ▼
Next Folder
      │
      ▼
Return Success Response

Why Create an Empty File?

OCI Object Storage does not actually store folders. Instead, folders are represented by object names containing /.

By creating an empty object (0-byte placeholder) for each folder path, the OCI Console displays the folder hierarchy, making it easier for users and integrations to organize files.

Benefits

• No manual login to the OCI Console.
• Quickly create complete folder hierarchies.
• Reusable across multiple projects and environments.
• Accepts any number of folders through a single request.
• Ideal for deployment automation and project onboarding.
• Reduces manual effort and configuration errors.

Sample Response

{
  "status": "SUCCESS",
  "message": "Folder structure created successfully."
}

Use Cases

• Initial Object Storage bucket setup.
• Environment provisioning.
• Automated deployment pipelines.
• Project onboarding.
• Creating standard folder templates across multiple buckets.

Conclusion

This reusable OIC service simplifies the creation of folder structures in OCI Object Storage by accepting a bucket name, namespace, and list of folder paths. Instead of manually navigating the OCI Console, users can create an entire folder hierarchy with a single API request, saving time and ensuring consistency across environments.

This lightweight utility is particularly useful for automation, CI/CD deployments, and projects that frequently provision new Object Storage buckets.