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.

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

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.

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

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.

PPT - Creating Professional Integration Architecture Diagrams Using PowerPoint

Creating Professional Integration Architecture Diagrams Using PowerPoint

When Visio Isn’t Available, PowerPoint Becomes Your Best Friend

In many enterprise projects, solution architects and integration developers must document system flows clearly. While tools like Visio or Lucidchart are commonly used, they are not always available in corporate environments due to licensing or security restrictions.

In such situations, Microsoft PowerPoint becomes a powerful and reliable alternative for creating clean, professional architecture and integration diagrams.

This blog demonstrates a real integration use case and shows how PowerPoint can be used to design high-quality diagrams.

Use Case: Source System to Target System File Integration

Business Scenario

A payment file is generated by a Source System and manually uploaded to the OIC SFTP location. Oracle Integration Cloud processes the file and finally uploads it into a Target System for downstream financial processing.

The business requires:

• Clear process visibility
• Batch job tracking
• Error monitoring

A simple but professional architecture diagram

Integration Flow Overview

The integration follows these steps:

• File is generated by the Source System.
• File is manually uploaded to OIC SFTP.
• OIC Scheduler picks the file.
• File is transformed into Target System format.
• File is uploaded to OCI Object Storage.
• Faults are logged in monitoring tools.

Solution: Creating This Diagram Using PowerPoint

Step 1: Create Swimlane Structure

• Use Insert → Table (1 row, 3 columns) to represent:
• Source System
• Oracle Integration Cloud
• Target System
• Format the header row with a blue background and white text.

This instantly creates a swimlane layout similar to professional architecture tools.

From Table design >> take a standard table style.

Step 2: Add Process Blocks

Use Rounded Rectangles for each processing step:

• Scheduler
• Get file from OIC SFTP
• Transform to Target format
• Upload to OCI Object Storage

Use light orange or yellow color for process clarity.

Step 3: Add System Objects

Use distinct colors:

Green → Input File 

Orange → Target System 

Color coding improves readability.

Step 4: Use Connectors (Not Lines)

Always use:

Insert → Shapes → Connector → Right Angle Arrow

This ensures connectors stay attached when shapes move.

Step 5: Add Supporting Notes

Use text boxes for:

“Manually uploaded by business team”

“Upload file to Target System”

These clarify ownership.

Step 6: Add Fault Handling Layer

At the bottom, insert a full-width rectangle:

Fault Handler – Monitoring Tool

This highlights error handling.

Step 7: Align & Distribute

Select shapes → Align → Align Center → Distribute Vertically

This gives a Visio-quality look.

Best Practices

Use consistent colors per layer

Keep uniform shape sizes

Follow swimlane structure

Avoid crossing arrows

Keep text action-oriented

Conclusion

PowerPoint is more than a presentation tool. With the right techniques, it becomes a powerful architecture diagramming solution that works perfectly for integration, data flow, and system design documentation.

This approach is ideal for:

Integration solution design

Technical documentation

Client walkthroughs

Knowledge transfer sessions

Microsoft Excel - working with excel's lookup function

📘 Working with Excel Lookup Functions

🔹 Microsoft Excel VLOOKUP() Function

VLOOKUP always searches in the first (leftmost) column of the table_array

It then returns a value from a column to the right of that first column

Widely used but has limitations (left-to-right only): “Left-to-right only” means VLOOKUP can return values only from columns that are to the right of the lookup column, never from the left.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

📌 Best for simple vertical lookups

🔹 Microsoft Excel HLOOKUP() Function 

Searches for a value in the first row of a table

Returns data from rows below

Less commonly used than VLOOKUP

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

📌 Best for horizontally structured data

🔹 Microsoft Excel INDEX() Function 

Returns a value from a cell based on row and column number. Very powerful and flexible

Syntax:

=INDEX(array, row_num, [column_num])

📌 Does not perform lookup by itself

🔹 Microsoft Excel MATCH() Function 

Finds the position of a value in a row or column

Often used with INDEX

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

📌 Returns position, not the value

🔹 INDEX() + MATCH() Combined

A powerful alternative to VLOOKUP

Can lookup left, right, up, or down

Example:

=INDEX(B2:B10, MATCH(E1, A2:A10, 0))

📌 More flexible and efficient than VLOOKUP

🔹 Dynamic HLOOKUP() using MATCH() — Advanced

MATCH dynamically identifies the row number

Prevents formula breakage when structure changes

Example:

=HLOOKUP(A1, A1:D10, MATCH("Sales", A1:A10, 0), 0)

📌 Makes HLOOKUP adaptable and robust

✅ Summary Table

OIC - Processing Files Larger Than 1 GB in Oracle Integration Cloud (OIC)

Processing large files (greater than 1 GB) in Oracle Integration Cloud (OIC) requires a well-thought-out design. OIC is an integration and orchestration platform, not a bulk file-processing engine. Therefore, the recommended approach is to offload heavy file handling and let OIC coordinate the process.

This blog covers the most effective and supported patterns to handle large files in OIC reliably.

Core Strategies for Large File Processing

1. Chunking (Segmentation)

File Chunking involves splitting a large file into smaller chunks and processing them one by one.

How It Works

The source system divides the file into smaller parts (for example, 50–100 MB chunks) 

Each chunk is sent separately 

OIC processes or routes each chunk

The target system reassembles the chunks into the original file

Benefits

Reduces memory pressure on OIC

Supports retries at chunk level instead of reprocessing the whole file

Improves reliability over unstable networks

Enables parallel processing if required

OIC Usage Notes

Pass chunk metadata (file name, chunk number, total chunks)

Use asynchronous integrations

Store chunk status externally if reassembly is required

2. Streaming

Streaming processes large files without loading the entire content into memory.

Benefits

Highly memory efficient

Faster processing start time

Suitable for continuous or large data flows

OIC Perspective

OIC does not support full custom streaming logic

Adapters such as FTP Adapter and OCI Object Storage Adapter internally stream files

Avoid mapping large payloads into integration variables

Best practice: Let adapters stream the file; keep OIC orchestration lightweight.

3. Cloud Object Storage (Preferred Pattern)

Using OCI Object Storage (or similar cloud storage) is the recommended approach for handling very large files.

Pattern Overview

Source uploads the file directly to Object Storage

OIC receives only file metadata (object name, bucket, URL)

OIC orchestrates downstream processing

Target system downloads the file directly from Object Storage

Benefits

Designed for multi-GB and TB-scale files

Built-in multipart upload support

High durability and scalability

Keeps OIC payload sizes small

Additional Recommended Approaches

4. OCI SDK / REST API (Multipart Uploads)

To bypass OIC’s payload size limitations:

Use OCI SDKs or OCI REST APIs

Perform multipart uploads directly to Object Storage

OIC only triggers or monitors the process

Benefits

No 1 GB payload limitation

Better performance and reliability

Ideal for custom upload/download logic

5. Oracle Functions for File Handling

Oracle Functions can act as a lightweight processing layer between systems.

Typical Use Case

Oracle Function downloads a large file from source

Performs validations or transformations if needed

Uploads the file to Object Storage using multipart upload

OIC is notified after completion

Benefits

Serverless and scalable

Keeps OIC free from heavy processing

Ideal for pre-processing or post-processing large files

Recommended Architecture

Source System → Object Storage / OCI SDK / Oracle Functions → OIC (Orchestration) → Target System

OIC’s role should be:

Triggering integrations

Orchestrating workflows

Handling errors and notifications

Tracking metadata (not file content)

Conclusion

When dealing with files larger than 1 GB in Oracle Integration Cloud:

✔ Use Object Storage as the central file repository

✔ Prefer chunking or streaming over full payload loads

✔ Leverage OCI SDKs, REST APIs, or Oracle Functions

✔ Keep OIC focused on orchestration, not file movement

Following these patterns ensures scalability, reliability, and compliance with OIC best practices.

OIC - Oracle Integration Cloud (OIC): Design Best Practices & Common Pitfalls to Avoid

Introduction

Oracle Integration Cloud (OIC) is a powerful platform for connecting cloud and on-premises applications. However, poor integration design can lead to performance issues, timeouts, backlogs, and scalability problems.

In this blog, we’ll explore key design best practices recommended by Oracle and highlight common pitfalls you should avoid while designing integrations in Oracle Integration Cloud (OIC 3).

Why Integration Design Matters

A well-designed integration:

• Improves performance and scalability
• Prevents stuck instances and scheduler backlogs
• Reduces timeout and failure scenarios
• Ensures smooth interaction between systems

Poor design, on the other hand, can impact the entire OIC instance, not just a single integration.

Different Best Practices:

1. Create Scheduled Integrations Mindfully

The Problem

Having too many scheduled integrations running at the same time can:

• Exhaust scheduler resources
• Cause delays in execution
• Lead to integrations not starting on time
• Long-running scheduled integrations are especially dangerous.

Best Practices

✔ Avoid clustering multiple schedules at the same time 

✔ Do not create long-running scheduled integrations (for example, those running over 1 hour) 

✔ Spread schedules across different time slots

Recommended Design Pattern

Instead of doing heavy processing inside a scheduled integration:

Convert the scheduled integration into a REST Adapter–triggered integration

Create a lightweight scheduler that:

Triggers the REST-based integration asynchronously

Completes within milliseconds

Benefits

Scheduler starts on time 

Reduced resource contention 

Better scalability and reliability

📌 Tip: Start converting:

Longest-running schedules

High-frequency schedules (every 10 minutes or less)

2. Follow Guidelines for Synchronous Integrations

A synchronous integration should not depend on asynchronous request–response services.

• One-way (fire-and-forget) asynchronous calls are okay.
• Oracle Integration does not support true asynchronous request–response modeling. Since scheduled integrations internally work in an asynchronous manner, calling a scheduled integration from a synchronous flow is a bad design practice.
• If a synchronous integration calls multiple services and the total processing time goes beyond five minutes, OIC may mark it as a stuck thread.

Best Practices

✔ Keep synchronous integrations short and fast

✔ Avoid calling scheduled integrations from synchronous flows

✔ Fire-and-forget (one-way) asynchronous calls are acceptable

3. Design Long-Running Integrations as Asynchronous

The Golden Rule

If an integration takes more than 2 minutes, it should be asynchronous.

Why?

• Client applications may time out
• OIC has server-side timeout limits
• Long waits block system resources

Recommended Approach

✔ Expose long-running processes as asynchronous integrations 

✔ Return immediate acknowledgment 

✔ Continue processing in the background

This improves user experience and system stability.

4. Understand Timeouts in Synchronous Integrations

Timeout Reality in OIC

• Synchronous calls must complete within 300 seconds (5 minutes).

But there’s more:

• Oracle Public Cloud proxy default timeout: 120 seconds
• On-premise proxies/firewalls may have their own timeout values

Key Insight

If multiple layers have timeouts configured, the first timeout reached causes failure.

Best Practice

✔ Avoid chaining multiple synchronous calls

✔ Minimize dependency on slow external systems

✔ Use asynchronous patterns wherever possible

5. Send Data to Third-Party Systems Efficiently

The Challenge

Sending data to multiple third-party systems in parallel without hitting timeout limits.

Recommended Design Pattern: Parent–Child Integrations

Step 1: Parent Integration

Receives and processes data

No outbound calls

Step 2: Child Integrations

Handle individual outbound REST calls

Triggered asynchronously

Communication Options

✔ Asynchronous REST (fire-and-forget)

✔ Publish/Subscribe model (queues, events)

Benefits

Parallel processing

Reduced timeout risks

Better resource utilization

🚫 Avoid making all outbound synchronous calls in a single integration—it can easily exceed the 5-minute limit.

Common Design Pitfalls to Avoid

❌ Too many scheduled integrations at the same time

❌ Long-running synchronous integrations

❌ Using schedules for request-response patterns

❌ Chaining multiple synchronous service calls

❌ Ignoring proxy and firewall timeouts

Conclusion

Designing integrations in Oracle Integration Cloud is not just about making systems talk—it’s about performance, scalability, and reliability.

By following these best practices:

You reduce failures and stuck instance 

Improve scheduler efficiency 

Build integrations that scale with business growth

A small design decision today can prevent major production issues tomorrow.

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/integrations-user/common-integration-style-pitfalls-and-design-best-practices.html

OIC - Understanding Message Pack Consumption in Oracle Integration Cloud (OIC)

Introduction

Oracle Integration Cloud (OIC) licensing is based on message pack consumption, and misunderstanding how messages are billed can lead to unexpected cost overruns. Each integration design choice—payload size, trigger type, file handling, and invoke responses—directly impacts message usage.

This blog explains:

• How OIC message packs are consumed
• Key billing rules you must consider
• Real-world use cases
• Practical solution steps to optimize message consumption

What Is a Message Pack in OIC?

A message pack represents a predefined number of messages processed by an OIC instance.

Consumption Models

BYOL (Bring Your Own License)

1 pack = 20,000 messages/hour

Non-BYOL

1 pack = 5,000 messages/hour

OIC for SaaS

Metered monthly

1 pack = 1 million messages/month

Each message is calculated in increments of 50 KB.

Core Rules That Impact Message Consumption

1️⃣ Trigger Rule

Every inbound trigger is billed

Payload size is rounded up in 50 KB blocks

Examples:

30 KB → 1 message

70 KB → 2 messages

120 KB → 3 messages

📌 Trigger payload size is the most critical cost driver

2️⃣ Invoke Rule

Invoke request → ❌ Not billed

Invoke response:

≤ 50 KB → ❌ Not billed

50 KB → ✅ Billed in multiples of 50 KB

📌 Large responses from REST, SOAP, BIP, or SaaS APIs increase message usage.

3️⃣ File Server Rule

Applies to scheduled/file-based integrations

Each file > 50 KB is counted as messages

Billing is done in ceil(file_size / 50 KB)

Example:

170 KB file → ceil(170/50) = 4 messages

4️⃣ Internal Calls Rule

Calls within the same OIC instance are not billed

Integration → Integration

Process → Integration

Visual Builder → Integration

⚠️ Calling another OIC instance incurs billing on:

Target instance (always)

Source instance (if response > 50 KB)

Common Real-World Use Cases & Consumption Analysis

Use Case 1: REST Inbound → SaaS Update

Scenario

REST inbound payload: 40 KB

SaaS invoke response: 20 KB

Consumption

Trigger: ceil(40/50) = 1

Response: Not counted

✅ Total = 1 message

Use Case 2: Large REST Inbound Payload

Scenario

REST inbound payload: 120 KB

Consumption

Trigger: ceil(120/50) = 3

✅ Total = 3 messages

Use Case 3: File-Based Scheduled Integration

Scenario

Scheduled job downloads 3 files:

20 KB

40 KB

170 KB

Consumption

Only file > 50 KB is counted

ceil(170/50) = 4 messages

✅ Total = 4 messages

Use Case 4: Database Adapter with Loop Invokes

Scenario

DB adapter pulls 2 rows (20 KB)

REST invoke per row returns 20 KB

Consumption

Trigger: ceil(20/50) = 1

Invoke responses < 50 KB → Not billed

✅ Total = 1 message

Use Case 5: SOAP Trigger + Multiple File Downloads

Scenario

SOAP inbound: 70 KB

Files downloaded:

20 KB

170 KB

40 KB

Consumption

Trigger: ceil(70/50) = 2

File > 50 KB: ceil(170/50) = 4

✅ Total = 6 messages

Solution Steps to Optimize Message Consumption in OIC

✅ Step 1: Minimize Trigger Payload Size

Avoid sending unnecessary fields

Use query parameters instead of body where possible

Use lightweight JSON over SOAP when feasible

✅ Step 2: Control Invoke Response Size

Use REST APIs with selective fields

Avoid returning full object payloads

Use pagination for large datasets

✅ Step 3: Handle Large Files Smartly

Split large files before processing

Filter files before download

Process only delta files instead of full loads

✅ Step 4: Prefer Internal OIC Calls

Reuse integrations within the same OIC instance

Avoid cross-instance integration unless mandatory

✅ Step 5: Use Scheduled Integrations Carefully

Schedule only when needed

Avoid unnecessary polling

Combine multiple lightweight operations into one flow

✅ Step 6: Monitor Usage Regularly

Use Usage Metrics → Billable Messages

Track hourly (OIC) or monthly (OIC for SaaS) usage

Identify spikes caused by large payloads or files

Key Takeaways

• Trigger payload size is always billed.
• Invoke responses matter, not requests
• Files > 50 KB increase cost quickly
• Good integration design = lower licensing cost

Understanding these rules helps architects and developers design cost-efficient, scalable OIC integrations without compromising functionality.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/oracle-integration-oci/monitoring-billable-messages.html

https://www.oraclespot.net/2023/05/oracle-integration-cloud-oic.html?m=0

Microsoft Excel - Working with excel's conditional functions

1. Microsoft Excel – Working with Excel’s Conditional Functions

Conditional functions perform calculations or return values based on conditions.

Common conditional functions:

• IF()
• COUNTIF(), COUNTIFS()
• SUMIF(), SUMIFS()
• IFERROR()
• AND(), OR(), NOT()

These are widely used in reporting, validation, dashboards, and analysis.

2. Working with Excel Name Ranges

A Named Range assigns a meaningful name to a cell or range.

Example: 

Instead of using

=SUM(A1:A10)

Use:

=SUM(Sales)

How to create a Named Range:

• Select the cells
• Go to Formulas → Define Name
• Enter the name (no spaces)
• Click OK

3. Advantages and Disadvantages of Excel Name Ranges

✅ Advantages

Improves readability of formulas

Easier maintenance

Reduces formula errors

Useful in complex models

Works well with conditional functions

❌ Disadvantages

Difficult to manage in very large workbooks

Hidden names can cause confusion

Not always intuitive for beginners

Broken references if ranges are deleted incorrectly

4. Editing an Excel Name Range

To edit a Named Range:

Go to Formulas → Name Manager

Select the name

Click Edit

Change range or name

Click OK

5. Using Excel’s IF() Function

The IF function returns one value if a condition is TRUE, another if FALSE.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Example:

=IF(A1>=50,"Pass","Fail")

6. Excel’s IF() Function with a Name Range

Assume:

Named Range: Score → A1

=IF(Score>=60,"Qualified","Not Qualified")

This makes formulas easier to understand and reuse.

7. Nesting Functions with Excel

Nesting means using one function inside another.

Example:

=IF(A1>=50, IF(A1>=75,"Distinction","Pass"),"Fail")

8. Nesting Excel’s AND() Function within the IF() Function

Use AND() when multiple conditions must be TRUE.

Syntax:

=IF(AND(condition1, condition2), value_if_true, value_if_false)

Example:

=IF(AND(A1>=50, B1="Yes"), "Eligible", "Not Eligible")

9. Using Excel’s COUNTIF() Function

Counts cells that meet one condition.

Syntax:

=COUNTIF(range, criteria)

Example:

=COUNTIF(A1:A20, ">=60")

Counts students scoring 60 or above.

10. Using Excel’s SUMIF() Function

Adds values that meet a specific condition.

Syntax:

=SUMIF(range, criteria, [sum_range])

Example:

=SUMIF(A1:A10,"Electronics",B1:B10)

Sums sales where category is Electronics.

11. Using Excel’s IFERROR() Function

Handles errors gracefully.

Syntax:

=IFERROR(value, value_if_error)

Example:

=IFERROR(A1/B1, 0)

Prevents #DIV/0! errors.

🔑 Practical Business Use Case

=IFERROR(

   IF(AND(Sales>=Target, Region="East"),"Bonus","No Bonus"),

"No Data")