OIC - How to Copy an Integration from One Project to Another in Oracle Integration Cloud (OIC)

How to Copy an Integration from One Project to Another in Oracle Integration Cloud (OIC)

Use Case: Migrating Integrations Between OIC Projects

In large-scale Oracle Integration Cloud (OIC) environments, teams often work on multiple projects simultaneously. When an integration built in one project needs to be reused or enhanced in another, copying it efficiently ensures consistency and reduces development effort.

Solution Steps to Copy an Integration Between Projects in OIC

1. Enable Cross-Project Availability: While creating the integration in one project, select the option to make it available in other projects.

2. Navigate to the Target Project: Switch to the project where you want to copy the integration.

3. Copy the Integration: Click on Copy Integration, then search for the required integration from the available list.

4. Confirm and Modify if Needed: After copying, update any necessary configurations, such as connections and mappings, to align with the new project’s requirements.

5. Activate and Test: Once all updates are done, activate the integration and perform testing to ensure it functions as expected.

Detailed Screenshots:

OIC - How to retrieve previous node value using preceding-sibling in OIC XSLT

Retrieving the Previous Node Value in OIC XSLT: Ensuring RT Records Have D1 as the Previous Record

Use Case: RT Record Validation in Oracle Integration Cloud (OIC)

In Oracle Integration Cloud (OIC), performing sequence-based validation in XML transformations is essential to maintain data integrity. One such scenario requires verifying that every RT record is immediately preceded by a D1 record.

Validation Rules:

• If a record starts with "RT", the immediately preceding record must start with "D1".
• If an "RT" record does not have a preceding "D1", a structure validation error will be thrown to ensure data correctness.

This validation helps enforce the required sequence before further processing, preventing incorrect or incomplete data from being processed in OIC.

Json used for writing a validation messages using stage:

{

    "Validation" : [{

        "Message" : "XXXXXXXXXXXX"

    }]

}

Solution steps:

1. For each record
2. Check if it is record starting with RT.  substring(ns:23FileData, 1,2) = 'RT'
3. Check the preceding node not starting with D1,  substring ($ReadRawSourceFile/nsmpr0:Read Response/ns23:FileRowSet/ns23:FileRow[(ns23:FileData = current()/ns23:FileData)]/preceding-sibling::ns23:FileRow[1.0]/ns23:FileData, 1, 2) !="D1"
4. validation error message: dvm:lookupValue("File_Validation", "key","V25","ValidarionMessage","Invalid File")

Detailed screenshots:

Similarly, to get next sibling node. We can use below xslt construct.

"following-sibling::*[1]"

OIC - How to Retrieve Email Attachments Using Microsoft Graph API: Automating Payment File Processing

Retrieving Email Attachments Using Microsoft Graph API: Automating Payment File Storage in Object Storage

Use Case:
A finance team needs to automate the extraction of payment files from vendor emails in Outlook and securely store them in an object storage solution (e.g., AWS S3, Azure Blob Storage, or Oracle Object Storage). Using Microsoft Graph API, they can programmatically fetch email attachments and upload them to object storage for secure access, processing, and archival.

HighLevel steps:

1. Create a rest connection using Microsoft Graph APIs.
2. Get Email unread Messages filtering on Subject.
3. If no message with attachment found, throw a fault.
4. For each message, 
1. check if it has the attachment.
2. Get list of attachments
3. For each attachment,
1. Upload file to Object storage for further processing.

Graph APIs used:

Connection URL: https://graph.microsoft.com/v1.0

Scope: https://graph.microsoft.com/.default

Get messages: /users/{userPrincipalName}/messages

List attachments: /users/{userPrincipalName}/mailFolders/{mailFolderId}/messages/{messageId}/attachments

Upload to object storage: /n/{namespacename}/b/{bucketname}/o/{objectname}

Detailed steps with screenshots:

Create rest connection:

Get Email  unread Messages for a matching subject.

Mapping:

If no message with attachment found:

For each message check if it has attachment

Get list of attachments:

For each attachment, upload content to Object storage

Reference:

https://learn.microsoft.com/en-us/graph/api/message-get?view=graph-rest-1.0&tabs=http

OIC - Moving a File using FTP connection now throws IO operation failed error

Error usecase:

Moving a File using FTP connection now throws IO operation failed error.

The integration has been working for weeks, with a file move operation from source directory to target directory in SFTP connection, but now has started throwing the below error.

The directory permissions were checked and file owner and all seems correct.

ERROR

The I0 operation failed.

The I0 operation failed.

The "OPER [NOOP] [UseNativeRename (S->R, T->R)]" I0 operation for "<Directory>/<Filename>" failed.

Check the error stack and fix the cause of the error.

Solution:

The file was already present in the system and was not overwritten. 

However, select the overwrite option while configuring the adapter.

OIC - How to Skip OIC Schedule During Public Holidays

Skipping OIC Schedule During Public Holidays

Use Case:

In Oracle Integration Cloud (OIC), scheduled integrations run at predefined intervals. However, organizations may need to prevent these jobs from executing on public holidays to optimize resources and avoid unnecessary processing.

Example Scenario

A company has a daily OIC integration that syncs employee attendance data from an external system to Oracle HCM. Since no employees work on public holidays, the integration should be skipped on those days.

Solution Approach: Using an OIC Lookup Table

Steps to Implement:

1. Create a Lookup Table in OIC:

   • Go to Lookups in OIC and create a new lookup table.
   • Define two columns: HolidayDate and Flag
   • Populate the lookup with public holidays for the year.

2. Modify the Scheduled Integration:

   • Retrieve the current date in the integration.
   • Check the lookup table to see if today’s date exists.
   • If today is a holiday:
     • Log a message: "Skipping execution due to public holiday."
     • Exit the integration.
   • If today is not a holiday, proceed with execution.

3. Update the Lookup Table Annually:

   • At the start of each year, update the lookup table with the new list of public holidays.

Detailed screenshots:

If there is a need to run the integration ad hoc on a public holiday in certain cases, we can introduce a schedule parameter IsReprocess. If IsReprocess is set to "Y", the integration will run regardless of the holiday. Otherwise, it will check the lookup table and skip execution if today is a public holiday.

Conclusion

By using an OIC lookup table, scheduled integrations can dynamically check for public holidays and skip execution when necessary. This approach ensures efficient resource utilization and prevents unnecessary API calls.

OIC - Delete file from OCI Object storage bucket using rest connection

Business requirement:

In Oracle Integration Cloud (OIC), there is a need to automate the deletion of files from an OCI Object Storage bucket. This is required when files are no longer needed or after successful processing, ensuring optimized storage usage and compliance with data retention policies

Rest API endpoint for deleting an object in the object storage bucket:

DELETE https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket-name>/o/<file-name>

Detailed screenshots:

Object storage rest connection:

Adapter configuration

Map bucket name, namespace and object name

OIC - Automating SFTP File Listing in OIC Based on Dynamic Date Filters and Adhoc Runs for past date files

Use Case:

In Oracle Integration Cloud (OIC), we need to list files from an source SFTP server based on a specific filename pattern and a date in the format YYYYMMDD. This date should be passed as a parameter to support both:

1. Scheduled Runs – Using the current date dynamically.
2. Ad-hoc or Past Date Processing – Allowing users to specify any past date manually.

This flexibility ensures that integrations can process files efficiently, whether through automated schedules or on-demand executions.

Date validation condition:

 (((FileProcessingDate = 'YYYYMMDD') or (FileProcessingDate = '')) or (FileProcessingDate = format-dateTime(concat(substring(FileProcessingDate, 1, 4), '-', substring(FileProcessingDate, 5, 2), '-', substring(FileProcessingDate, 7, 2)), '[Y0001][M01][D01]')))

File pattern population logic:

When processingdate != 'YYYYMMDD' and  processingdate != ''

replace(lookupValue("Common_CCS_Interface_SFTP_Lookup", "IntegrationId", Var_InterfaceId, "SourceFileName", "CTFCLPMC*"), "YYYYMMDD", FileProcessingDate)

Otherwise:

replace(lookupValue("Common_CCS_Interface_SFTP_Lookup", "IntegrationId", Var_InterfaceId, "SourceFileName", "CTFCLPMC*"), "YYYYMMDD", format-dateTime(current-dateTime(), "[Y0001][M01][D01]"))

Solution Steps:

1. Create a Scheduled Orchestration integration pattern to enable both scheduled and manual triggers.
2. Define a schedule parameter fileprocesingDate (YYYYMMDD format) to accept the date dynamically.
3. Validation: add a throw new fault and put the skip condition, if invalid date, it will throw error. Skip conditions:
1. Procrssing date is YYYYMMDD 
2. Processing date is empty
3. File processing is in YYYYMMDD format. 
4. Configure the sftp adapter and choose list files operations and put the following branching to populate the right file name pattern.
1. When processingdate is not YYYYMMDD and not empty, take file name part and current date from the schedule parameter for adhoc run.
2. Otherwise, take the name and current date for automatic processing.
5. Take the list of files and process one by one using a for each loop
6. If no files found, send a notication or log an appropriate message.

Testing & Deployment

• Test with multiple scenarios:
  • Scheduled execution with the current date.
  • Manual execution with a specific past date.
  • Scenarios where no matching files exist.
• Deploy the integration and configure scheduling as per business needs.

Detailed screenshots: