Friday, February 6, 2026

OIC - Using OCI Functions to Resolve Redirect URLs for Oracle Integration Cloud (OIC)

Introduction

Oracle Integration Cloud (OIC) is great at orchestrating APIs, but it has a limitation when dealing with HTTP redirects. Many external services return shortened or deflected URLs that internally redirect multiple times before reaching the final destination URL.

OIC REST Adapter does not reliably expose the final redirected URL in such cases. This becomes a blocker when downstream logic depends on extracting values (like UUIDs, tokens, or IDs) from the final URL.

To solve this, we can use OCI Functions as a lightweight middleware to resolve redirects and return the final URL back to OIC.

Problem Statement (Use Case)

OIC invokes an external API (for example: email body from Microsoft Graph, SendGrid, or any notification service). The response contains a short or redirected URL. OIC needs the final resolved URL to:

Extract a UUID or parameter

Call another API

Perform validation or tracking

OIC REST Adapter does not provide the final redirected URL easily.

Solution Overview

We introduce OCI Functions between OIC and the redirected URL.

High-Level Flow

OIC extracts the redirected/short URL from API response.

OIC calls an OCI Function, passing the URL as input.

OCI Function:

Follows HTTP redirects

Resolves the final destination URL

OCI Function returns the final URL to OIC.

OIC continues processing (UUID extraction, API calls, etc.).

Architecture Diagram (Logical):

OIC → OCI Function → External Redirect URL

      ↑                     ↓

      └──── Final URL ──────┘

OCI Function Implementation

You can use Java, Node.js or Python. Below I have used Java code.

package com.clp.fn;

import java.net.URI;

import java.net.URLDecoder;

import java.net.http.HttpClient;

import java.net.http.HttpRequest;

import java.net.http.HttpResponse;

import java.nio.charset.StandardCharsets;

import java.time.Duration;

import java.util.Optional;

import java.util.logging.Level;

import java.util.logging.Logger;

public class SendGridRedirectResolver {

private static final Logger logger =

Logger.getLogger (SendGridRedirectResolver.class.getName());

private static final int MAX REDIRECTS = 10;

private static final HttpClient CLIENT = HttpClient.newBuilder()

.connectTimeout (Duration.ofSeconds (60))

.followRedirects (HttpClient.Redirect.NEVER)

.build();

//DTOS (Modern Java Records)

public static class Input {

public String input_url;

}

public static class Result {

public String output_url;

public String uuid;

public String executionInfo;

}

// Entry method

public Result handleRequest (Input input) {

Result result = new Result();

try {

logger.log(Level.INFO, "OIC-Input URL: {0}", input.input_url);

System.out.println("OIC-Input URL: " + input.input_url);

String decodedUrl = extractFromUrlDefense (input.input_url);

logger.log (Level.INFO, "OIC-Decoded SendGrid URL: (0)", decodedUrl); System.out.println("OIC-Decoded SendGrid URL : " + decodedUrl);

String finalUrl = resolveFinalUrl (decodedUrl);

logger.log (Level.INFO, "OIC-Final URL: (0)", finalUrl);

System.out.println("OIC-Final URL:: " + finalUrl);

result.output_url = finalUrl;

result.uuid = "";

result.executionInfo = "SUCCESS";

return result;

} catch (Exception e) {

result.executionInfo = e.getMessage();

logger.log (Level.SEVERE, "Processing failed", e);

System.out.println("OIC- Exception: + e);

return result;

}

}

// Core Logic

private static String extractFromUrlDefense (String url) {

int start = url.indexOf("/");

int end = url.indexOf(";");

if (start < 0 || end < 0 || end <= start + 3) {

throw new IllegalArgumentException("Invalid urldefense URL");

}

}

String encoded = url.substring(start + 3, end);

return URLDecoder.decode (encoded, StandardCharsets.UTF8);

private static String resolveFinalUrl (String url) throws Exception {

URI current URI.create(url);

for (int i = 0; i < MAX REDIRECTS; i++) {

HttpRequest request = HttpRequest.newBuilder (current) .timeout(Duration.ofSeconds (60))

.GET()

.build();

HttpResponse<Void> response =

CLIENT.send(request, HttpResponse.BodyHandlers.discarding());

int status = response.statusCode();

if (status >= 300 && status < 400) {

Optional<String> location =

response.headers().firstValue("Location");

if (location.isEmpty()) {

}

throw new RuntimeException("Redirect without Location header");

current = current.resolve(location.get());

} else {

}

return current.toString();

}

}

throw new RuntimeException("Too many redirects");

}

// Standalone Test

public static void main(String[] args) {

String urlDefense =

"Paste your redirect url here";

Input input = new Input();

input.input_url = urlDefense;

SendGridRedirectResolver resolver = new SendGridRedirectResolver();

Result result resolver.handleRequest(input);

System.out.println("output_url:" + result.output_url);

System.out.println("uuid:" + result.uuid);

}

}

Snap of codes:





Deploying OCI Function

  • Create an OCI Function Application
  • Deploy the function using Fn CLI
  • Expose it via OCI API Gateway
  • Secure it using:
  • OCI IAM
  • API Key or OCI Resource Principal
  • Calling OCI Function from OIC

Steps in OIC

Create a REST Adapter integration

Configure API Gateway endpoint

Pass the redirected URL as request payload

Receive response:

Json

{

  "finalUrl": "https://example.com/path/uuid/12345"

}

Extracting Required Data in OIC

Once OIC receives the final URL:

Extract UUID or parameters

Continue downstream orchestration

Example:

substring-after(finalUrl, '/uuid/')

Benefits of This Approach

✅ Overcomes OIC redirect limitations

✅ Clean separation of concerns

✅ Serverless and cost-effective

✅ Reusable across multiple integrations

✅ Easy to maintain and enhance

Real-World Scenarios

Email tracking links (SendGrid, Twilio)

Microsoft Graph email body links

Payment gateway redirect URLs

Identity verification flows

Short URL expansion

Conclusion

Using OCI Functions as a redirect resolver is a simple yet powerful pattern when working with OIC. It keeps integrations clean, avoids complex workarounds, and provides full control over HTTP behavior.

If your OIC flow depends on the final destination URL, this approach is highly recommended.


OIC - OIC Deployment Challenge: How to Avoid Manually Deselecting Integrations Every Time

Problem Statement

While creating a Deployment in Oracle Integration Cloud (OIC), the system automatically selects all integrations by default.

In real projects, we usually need to:

Deploy only a subset of integrations

Exclude unused, POC, or unrelated flows

However, OIC forces us to manually deselect dozens of integrations every single time, which is:

❌ Annoying

❌ Time-consuming

❌ Error-prone (easy to miss one)

This becomes especially painful when:

The environment has 50+ integrations

Frequent exports are required (DEV → TEST → PROD)

Use Case

You want to export only specific integrations from OIC for:

Environment promotion

Backup

Partial deployment

But during Create Deployment, OIC:

Auto-selects all integrations

Requires manual deselection every time

Observed Limitation in OIC

OIC does not provide an option like:

“Select none by default”

“Remember previous selection”

Latest versions are always auto-selected

The Workaround (Proven & Simple) ✅

Here’s the trick that saves a LOT of time 👇

Solution Steps

Step 1: Create a Deployment (Initial Attempt)

  • Navigate to Integrations → Packages / Deployments 
  • Click Create Deployment
  • Enter:
  • Name
  • Identifier
  • Proceed to Choose Integrations
  • You’ll see all integrations selected by default


Step 2: Do NOT Save — Discard Instead ❌

  • Click Back
  • OIC prompts:
  • “There are unsaved changes. Do you want to save before exiting?”
  • Click Discard

👉 This is the key step

Step 3: Reopen the Same Deployment 🧠

Open the same deployment again

Go to Choose Integrations

✨ Magic happens here:

All integrations are now deselected

You start with a clean slate

Step 4: Select Only Required Integrations ✅

Select only the needed integrations

Click Save

Deployment is now:

Clean

Accurate

Ready for export

Result / Benefits

✅ No more bulk deselection

✅ Faster deployment creation

✅ Reduced human error

✅ Much smoother export process

✅ Works reliably across environments

Key Takeaway

Although OIC doesn’t natively support selective deployment defaults, this discard-and-reopen workaround is a practical and efficient solution for real-world projects.


Saturday, January 31, 2026

Microsoft Excel - Working with Excel Text-Based Function

Working...

📊 Working with Excel Text-Based Functions

A Practical Guide to LEFT, RIGHT, MID, LEN, SEARCH & CONCAT excel functions

In day-to-day work, Excel is not just about numbers. Very often we deal with text data—emails, IDs, file names, codes, descriptions, etc.

Microsoft Excel provides powerful text-based functions to extract, search, and combine text efficiently.

In this blog, we’ll explore the most commonly used Excel text functions with simple examples and real-life use cases.

🔹 1. LEFT() Function

Purpose: Extracts a specified number of characters from the left side of a text string.

Syntax: LEFT(text, num_chars)

Example:

=LEFT("ORACLEOIC", 6)

Result: ORACLE

Use Case:

Extract country codes

Get prefixes from employee IDs

Read file name initials

🔹 2. RIGHT() Function

Purpose: Extracts characters from the right side of a text string.

Syntax: RIGHT(text, num_chars)

Example:

=RIGHT("INV_2026", 4)

Result: 2026

Use Case:

Extract year from invoice numbers

Get last digits of mobile numbers

Read file extensions

🔹 3. MID() Function

Purpose: Extracts text from the middle of a string.

Syntax: MID(text, start_num, num_chars)

Example:

=MID("EMP-12345-IND", 5, 5)

Result: 12345

Use Case:

Extract employee or order IDs

Parse structured codes

Read values between delimiters

🔹 4. LEN() Function

Purpose:

Returns the total number of characters in a text string (including spaces).

Syntax: LEN(text)

Example:

=LEN("Excel Functions")

Result: 15

Use Case:

Validate text length

Dynamically calculate MID / RIGHT values

Detect extra spaces

🔹 5. SEARCH() Function

Purpose:

Finds the position of a character or word within text (not case-sensitive).

Syntax: SEARCH(find_text, within_text)

Example:

=SEARCH("@", "user.name@gmail.com")

Result: 10

Use Case:

Locate special characters (@, -, _)

Split emails or file names

Dynamic text extraction

🔹 6. CONCAT / CONCATENATE Function

Purpose:

Joins multiple text strings into one.

Syntax (Modern Excel): CONCAT(text1, text2, ...)

Example:

=CONCAT("Oracle", " ", "Integration", " ", "Cloud")

Result:

Oracle Integration Cloud

Use Case:

Combine first & last names

Build dynamic messages

Create file names or IDs

🔹 7. Real-Life Combined Example (Most Important)

🎯 Extract Domain Name from Email ID

Email: john.doe@company.com

Formula:

=MID(A1, SEARCH("@", A1) + 1, LEN(A1))

Result:

company.com

👉 This example shows the real power of Excel, where multiple text functions work together.

✅ Why These Functions Matter

Save manual effort

Avoid data errors

Make formulas dynamic

Essential for reporting, automation & integration work

Whether you’re working in finance, HR, IT, ERP, or integrations, these text functions are absolute must-knows.


Friday, January 30, 2026

OIC - How to Schedule an OIC Integration to Run at 9:15 AM for the First 10 Days of Every Month Using iCal

🔍 Use Case

In Oracle Integration Cloud (OIC), there are scenarios where an integration must run only during a specific part of the month, instead of daily or monthly as a whole.

Example Scenario:

Run a scheduled integration

Execute every day at 9:15 AM

Only for Day 1 to Day 10 of each month

Used for:

Early-month payroll validations

Monthly reports generation

First-10-days billing or reconciliation jobs

The Simple scheduler does not support such complex patterns, so we use iCal expressions.

🛠️ Solution Approach (Using iCal Recurrence)

Oracle OIC supports iCal-based scheduling, which allows precise control over:

Frequency

Monthly - 1 to 10 days

Time - 9:15 AM

✅ iCal Expression to Use

FREQ=MONTHLY;BYMONTHDAY=1,2,3,4,5,6,7,8,9,10;BYHOUR=9;BYMINUTE=15;

🧭 Step-by-Step Configuration in OIC

Go to Integrations >> Schedules >> Open your scheduled integration >> Click Edit Schedule >> Select Define Recurrence → iCal >> Paste the iCal expression:

FREQ=MONTHLY;BYMONTHDAY=1,2,3,4,5,6,7,8,9,10;BYHOUR=9;BYMINUTE=15;

Click Validate Expression

Set:

Start Date (e.g., beginning of the month)

Time Zone (important for correct execution)

Save and Start the Schedule

⏱️ Execution Behavior

Runs daily at 9:15 AM

Executes only from 1st to 10th day

Automatically repeats every month

No manual intervention required


Thursday, January 29, 2026

OIC - Automated Report Retrieval Using Twilio SendGrid, Microsoft Graph API, and OCI Functions

Working... will add api details and related info.

Use Case

In many enterprise integrations, reports are not directly exposed via APIs. Instead, systems like Twilio SendGrid generate reports and send secure download links via email. These links are time-bound, redirected, and protected, making manual download inefficient and error-prone.

This use case addresses the need to automatically retrieve a report sent via email, extract the required download link, resolve security redirects, download the file, transform it, and finally deliver it to a target system such as OCI Object Storage or any downstream consumer.

Solution Overview

The solution orchestrates multiple services—Twilio SendGrid, Microsoft Graph API, OCI Functions, and Oracle Integration Cloud (OIC)—to fully automate report extraction and delivery without human intervention.

Solution Steps

Step 1: Trigger SendGrid Report Generation

Invoke the Twilio SendGrid API by passing the required start date and end date.

SendGrid generates the report and sends an email containing the report download information to a configured mailbox.

https://api.sendgrid.com/v3/messages/download?query=(last_event time BETWEEN TIMESTAMP "2025-11-07T16:00:00.0002" AND TIMESTAMP "2025-11-11T23:59:59.9992")



Step 2: Fetch Email Using Microsoft Graph API

Use Microsoft Graph API to read messages from the mailbox.

Filter emails based on:

Sender email address

Subject or timestamp (optional but recommended)

This ensures only the relevant report email is processed.

https://graph.microsoft.com/v1.0/users/{emailUser}/messages





Step 3: Extract Download Link from Email Body

Parse the email body retrieved from Graph API and extract the secure report link embedded in the message content.

Step 4: Resolve Secure Redirect via OCI Function

Invoke an OCI Function, passing the extracted link.

The function handles:

Redirect resolution

Security headers

URL decoding

It returns the final redirected URL required for further processing.

https://soalicious.blogspot.com/2026/02/oic-using-oci-functions-to-resolve.html

Step 5: Extract UUID from Redirected URL

From the resolved URL, extract the UUID (or unique report identifier).

This UUID is mandatory for subsequent SendGrid API calls.

Step 6: Fetch Final Report Download URL

Call the SendGrid API again using the extracted UUID to retrieve the final report download URL

/messages/download/{download_uuid}



Step 7: Download Report Using No-Security REST Connection

Use a No Security REST Adapter to download the report file directly using the final URL.

This step handles binary file content securely within OIC.




Step 8: Transform Report Data

Apply required transformations based on target system needs:

File format conversion

Data filtering or enrichment

Renaming or metadata adjustments

Step 9: Deliver File to Target System

Send the transformed file to the target system, such as:

OCI Object Storage

SFTP server

Another REST endpoint

From here, the target application can consume the report seamlessly.

Key Benefits

✅ Fully automated, zero manual intervention

✅ Secure handling of email-based report delivery

✅ Scalable and reusable architecture

✅ Ideal for scheduled or event-driven integrations


Sunday, January 25, 2026

OIC - How to Run an Oracle Integration Cloud (OIC) Integration on the 3rd Working Day of the Month

Introduction

Many enterprise integrations—especially in payroll, finance, and compliance—must run on a specific working day of the month, such as the 3rd working day.

Oracle Integration Cloud (OIC) schedulers do not natively support “working day” logic, so this requirement must be handled through custom orchestration logic.

This blog explains a reliable and production-ready approach using OIC Scheduler + JavaScript action, with support for weekends and holidays.

Use Case / Business Scenario

An integration must run only on the 3rd working day of every month

Working days exclude:

Saturdays and Sundays

Company or regional holidays

The job should:

Automatically adapt to month start falling on weekends

Not require manual intervention every month

Typical Examples

Payroll file generation

Vendor payment processing

Month-start financial reports

Regulatory data submission

Challenges in OIC

OIC scheduler does not understand working days

iCal or Simple schedules cannot handle:

Weekend exclusion

Holiday calendars

Solution Overview

Design Pattern

Schedule the integration daily and control execution using JavaScript logic

Javascript code used:

function getThirdWorkingDate (currentDate, holidayList) {

// Parse the date

var today;

if (currentDate instanceof Date) {

today = new Date (currentDate);

} else if (typeof currentDate === 'string') {

// Handle different date formats

if (currentDate.includes ('T')) {

today = new Date (currentDate);

} else {

today = new Date (currentDate + 'T00:00:00');

}

} else {

// If no valid date provided, use current date

today = new Date();

}

var year = today.getFullYear();

var month = today.getMonth();

// Start from the first day of the month

var d = new Date (year, month, 1);

// Prepare holiday lookup

var holidays = {};

if (holidayList) {

if (typeof holidayList === 'string') {

var items = holidayList.split(',');

for (var i = 0; i < items.length; i++) {

holidays [items[i].trim()] = true;

} } else if (Array.isArray (holidayList)) {

for (var j = 0; j < holidayList.length; j++) {

holidays [String (holidayList (j))] = true;

}

}

}

var workingDayCount = 0;

// Loop through up to 31 days

for (var k = 0; k < 31; k++) {

var dayOfWeek= d.getDay(); //0=Sunday, 6-Saturday

// Format date as YYYY-MM-DD

var dateStr = d.getFullYear() + "-" +("0" + (d.getMonth() + 1)).slice(-2) + "-" +("0" + d.getDate()).slice(-2);

// Check if it's a working day (not weekend, not holiday)

if (dayOfWeek > 0 && dayOfWeek < 6 && !holidays [dateStr]) {

workingDayCount++;

if (workingDayCount ===3){

return dateStr;

}

}

// Move to next day

d.setDate(d.getDate() + 1);

}

// If no third working day found

return dateStr;

}

console.log(getThirdWorkingDate("2026-02-01","2026-02-03,2026-02-20"))



Key Components:

Daily scheduled integration 

JavaScript action to calculate the 3rd working day 

External holiday list (Lookup / DB / File)

Switch activity to control execution

Solution Steps

Step 1: Schedule the Integration Daily

Use Simple or iCal schedule

Run once every day (early morning preferred)

Step 2: Maintain Holiday Calendar

Store holidays in:

OIC Lookup (recommended)

Database table

Stage file

Pass holiday list to integration as:

YYYY-MM-DD,YYYY-MM-DD

Step 3: Pass Current Date

Use Assign action:

format-dateTime(ora:current-dateTime(), "[Y0001]-[M01]-[D01]")

This ensures: Correct timezone handling and Consistent date format

Step 4: Calculate 3rd Working Day (JavaScript Action)

JavaScript receives:

Current date

Holiday list

Logic:

Start from 1st of the month

Skip weekends

Skip holidays

Identify the 3rd working day

Return the calculated date

Step 5: Control Execution Using Switch

Condition:

currentDate = thirdWorkingDate

True → Execute business logic

False → End integration

Benefits of This Approach

  • Fully automated
  • Handles weekends and holidays correctly
  • No hardcoding of dates
  • Reusable across multiple integrations
  • Easy to explain in audits and design reviews

Conclusion

Oracle Integration Cloud does not provide a built-in way to schedule jobs on the “Nth working day.”

However, by combining daily scheduling with JavaScript logic, you can achieve a clean, flexible, and enterprise-ready solution.

Best practice:

Let the scheduler run daily and let the integration decide when to execute.


OIC - Minimum Schedule Time in Oracle Integration Cloud (OIC): Simple vs iCal – Use Case and Solution

Introduction

Oracle Integration Cloud (OIC) provides scheduled integrations to execute jobs at fixed intervals. While configuring schedules looks straightforward, many developers face confusion around the minimum time supported for Simple and iCal schedules.

This blog clarifies the official limits, common use cases, and recommended solution patterns.

Minimum Schedule Time Supported in OIC

🔹 Simple Schedule

Minimum supported time: 10 minutes

Configured via UI dropdown options

Intervals less than 10 minutes are not allowed

🔹 iCal Schedule

Minimum supported time: 1 minute

Uses iCal (RFC 5545) expressions

Offers more flexibility than Simple schedule

Key takeaway:

Use Simple Schedule for standard batch jobs

Use iCal Schedule when you need 1-minute granularity

Business Use Case Scenario

An organization needs to: Poll ERP / HCM / Database / FTP. Fetch newly created or updated records. Push data to downstream systems quickly

Expectation

Data should be processed near real time (1–5 minutes)

Challenge

Simple schedule does not support less than 10 minutes. Incorrect assumptions may lead to delayed processing

Solution Approaches

Solution 1: Use iCal Schedule for 1-Minute Polling

Best when polling is mandatory

Steps:

Create a Scheduled Integration

Select iCal-based schedule

Use expression:

Copy code

FREQ=MINUTELY;INTERVAL=1;

Maintain last processed timestamp

Fetch only delta records

✔️ Faster execution

✔️ Supported by OIC

⚠️ Use carefully for high-volume systems

Solution 2: App-Driven Integration (Recommended)

Best practice for real-time needs

Steps:

Create an App-Driven Orchestration

Expose REST endpoint using REST Adapter

Source system triggers OIC instantly

Process and route data to targets

✔️ True real-time

✔️ No polling overhead

✔️ Scalable design

⚠️ Solution 3: Simple Schedule + Smart Design

When real-time is not mandatory

Steps:

Configure Simple schedule (10 minutes)

Use delta logic (timestamp / status flag)

Avoid duplicate processing

✔️ Stable

✔️ Easy to maintain

What to Avoid

❌ Assuming Simple schedule supports 5 minutes

❌ Forcing cron tricks below supported limits

❌ Excessive 1-minute polling without volume control

Conclusion

Understanding OIC scheduler limits helps in choosing the right integration pattern.

Design guidance:

Batch processing → Simple Schedule (10 min)

Near real-time polling → iCal (1 min)

Real-time integration → App-Driven / Events

Choosing the correct approach improves performance, scalability, and maintainability of OIC integrations.


Featured Post

OIC - Using OCI Functions to Resolve Redirect URLs for Oracle Integration Cloud (OIC)

Introduction Oracle Integration Cloud (OIC) is great at orchestrating APIs, but it has a limitation when dealing with HTTP redirects. Many e...