OIC - Handling Base64 Encoded JSON NXSD Parsing Issue in Oracle Integration Cloud (OIC)

📌 Problem Statement

In a Real-Time REST integration in Oracle Integration Cloud, the source system sends Base64 encoded JSON.

Flow:

Receive Base64 payload

Decode using oraext:decodeBase64()

Pass to Stage File → Read as JSON

❗ Issue Faced

After decoding, the JSON becomes invalid because:

The closing curly brace } is missing

This causes NXSD parsing errors in Stage File

🔍 Root Cause

During decoding or transformation:

Extra whitespace / formatting issues

Improper encoding at source

OIC string handling edge cases

👉 Result: JSON becomes malformed, especially missing }

💡 Solution Approach

Instead of directly parsing decoded JSON:

✅ Step 1: Decode Base64

✅ Step 2: Clean the JSON string

✅ Step 3: Validate & fix missing closing brace

✅ Step 4: Re-encode to Base64

✅ Step 5: Pass as Opaque to Stage File

🛠️ Implementation (XSLT Logic)

✅ Step 1: Decode Base64

Xslt

<xsl:variable name="decoded"

 select="oraext:decodeBase64(/nssrcmpr:execute/ns16:request-wrapper/ns16:message)"/>

✅ Step 2: Clean unwanted whitespace

Xslt

<xsl:variable name="cleaned"

  select="replace($decoded, '(:\s*&quot;)\s+', '$1')"/>

👉 Removes unnecessary spaces after : in JSON

✅ Step 3: Fix Missing Closing Brace

Xslt

<xsl:variable name="finalJson">

  <xsl:choose>

    <xsl:when test="contains($cleaned, '}')">

      <xsl:value-of select="$cleaned"/>

    </xsl:when>

    <xsl:otherwise>

      <xsl:value-of select="concat($cleaned, '}')"/>

    </xsl:otherwise>

  </xsl:choose>

</xsl:variable>

👉 Ensures JSON is always valid

✅ Step 4: Encode Back to Base64

Xslt

<xsl:value-of select="oraext:encodeBase64($finalJson)"/>

🔄 Integration Flow Design

🧩 OIC Flow Steps

REST Trigger

Stage File (Write File)

Write as Opaque

Use above XSLT

Stage File (Read File)

Now JSON is valid

Parse using NXSD schema

Continue processing…

🎯 Why Opaque Handling Works

Using opaque avoids:

Early validation failures

NXSD parsing errors on invalid JSON

👉 You fix JSON before parsing

⚠️ Best Practices

Always validate decoded payload:

Use contains() or ends-with() for }

Log decoded payload (for debugging)

Avoid direct parsing of decoded Base64 without validation

🏁 Conclusion

Handling Base64 JSON in OIC can be tricky due to:

Encoding inconsistencies

Transformation side effects

👉 The decode → clean → fix → re-encode → stage as opaque pattern is a reliable solution.

Code screenshots:

Stage write:

Code xslt:

Read json:

Code snippet:

<xsl:template match="/" xml:id="id_11">

  <nstrgmpr:Write xml:id="id_12">

    <ns31:opaqueElement>

      <!-- Step 1: Decode -->

      <xsl:variable name="decoded"     select="oraext:decodeBase64(/nssrcmpr:execute/ns16:request-wrapper/ns16:message)"/>

      <!-- Step 2: Clean whitespace after ":" -->

      <xsl:variable name="cleaned"

        select="replace($decoded, '(:\s*&quot;)\s+', '$1')"/>

      <!-- Step 3: Check and fix closing brace -->

      <xsl:variable name="finalJson">

        <xsl:choose>

          <xsl:when test="contains($cleaned, '}')">

            <xsl:value-of select="$cleaned"/>

          </xsl:when>

          <xsl:otherwise>

            <xsl:value-of select="concat($cleaned, '}')"/>

          </xsl:otherwise>

        </xsl:choose>

      </xsl:variable>

      <!-- Step 4: Encode back -->

      <xsl:value-of select="oraext:encodeBase64($finalJson)"/>

    </ns31:opaqueElement>

  </nstrgmpr:Write>

</xsl:template>

OIC - Handling Multiple Stub & Payment Matching in XSLT (OIC Optimization)

🚀 Handling Multiple Stub & Payment Matching in XSLT (OIC Optimization)

📌 Problem Context

We receive:

Multiple StubRecords

Multiple PaymentRecords

Same Transaction Number across records

👉 Goal:

Correctly match multiple stubs with multiple payments

Ensure accurate mapping

Avoid 120 sec XSLT timeout

⚠️ Issue with Traditional Matching

XML

//ns25:PaymentRecord[ns25:TransactionNumber = $txn]

❌ Fetches all records

❌ Incorrect matching

❌ Slow → Timeout

✅ Optimized Steps Using xsl:key

🔑 Step 1: Define Keys

XML

<xsl:key name="stubKey"

         match="ns25:StubRecord"

         use="concat(ns25:TransactionNumber,'|',ns25:OperatorID)"/>

<xsl:key name="payKey"

         match="ns25:PaymentRecord"

         use="concat(ns25:TransactionNumber,'|',normalize-space(ns25:Filler3))"/>

<xsl:key name="payKeyExact"

         match="ns25:PaymentRecord"

         use="concat(ns25:TransactionNumber,'|',normalize-space(ns25:Filler3),'|',ns25:PaymentAmount)"/>

🔄 Step 2: Loop Through Stub Records

XML

<xsl:for-each select="$ReadSourceFile/.../ns25:StubRecord">

🧮 Step 3: Extract Values

XML

<xsl:variable name="txn" select="ns25:TransactionNumber"/>

<xsl:variable name="op" select="ns25:OperatorID"/>

<xsl:variable name="amt" select="ns25:PaidAmount"/>

🔗 Step 4: Build Composite Keys

XML

<xsl:variable name="stubKeyVal" select="concat($txn,'|',$op)"/>

<xsl:variable name="payKeyVal" select="concat($txn,'|',$op)"/>

<xsl:variable name="payKeyExactVal" select="concat($txn,'|',$op,'|',$amt)"/>

⚡ Step 5: Fetch Using Key

XML

<xsl:variable name="sameStub" select="key('stubKey',$stubKeyVal)"/>

<xsl:variable name="payments" select="key('payKey',$payKeyVal)"/>

<xsl:variable name="paymentsExact" select="key('payKeyExact',$payKeyExactVal)"/>

🔁 Step 6: Handle Multiple Stub Records

XML

<xsl:if test="count($sameStub) > 1">

👉 Ensures:

Only process when multiple stubs exist for same transaction + operator

Avoid incorrect or duplicate mapping

🎯 Step 7: Choose Best Payment Match

XML

<xsl:variable name="finalPayments"

    select="if (exists($paymentsExact)) then $paymentsExact else $payments"/>

👉 Prefer:

Exact match (txn + operator + amount)

Else fallback

🔄 Step 8: Map & Merge Data

XML

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

<xsl:value-of select="oraext:create-delimited-string($finalPayments/ns25:ChequeNumber,'|')"/>

📤 Step 9: Generate Output

👉 One output per valid stub, enriched with matched payments

🎯 Final One-Line Summary

👉 “Use xsl:key with composite keys to accurately match multiple stub and payment records for the same transaction, handle multi-stub scenarios using count logic, and ensure optimized performance to avoid XSLT timeout in OIC.”

Code Screenshots:

OIC - Optimizing XSLT Transformations Using xsl:key | Muenchian grouping

🚀 Optimizing XSLT Transformations Using xsl:key

(With Real Integration Example from OIC)

When working with large XML payloads in Oracle Integration Cloud (OIC) or any XSLT-based transformation, performance and duplicate handling become critical. One powerful but often underused feature is xsl:key, which enables fast lookups and efficient grouping.

Let’s break down your scenario and understand it step by step.

📌 Problem Context

👉 ““We need to uniquely match payment records with stub records using transaction number and operator, and merge their details efficiently. Using traditional XPath-based conditional matching causes repeated XML scans, increasing processing time and leading to XSLT timeout issues (120 seconds) in OIC. Our goal is to optimize this using xsl:key for faster lookup and better performance.”

You are processing payment records like:

XML

ns24:Payments/ns24:PaymentRecord

Each record contains:

TransactionNumber

OperatorID

PaymentMethod

PaymentAmount

etc.

👉 The goal is to:

Identify unique records

Avoid duplicates

Map values efficiently

Improve transformation performance

Steps for resolution with key(): 

🔍 Step 1: Understanding the Key Definition

XML

<xsl:key name="stub-by-txn-op"

         match="ns24:StubRecord"

         use="concat(ns24:TransactionNumber, '|', ns24:OperatorID)"/>

💡 What this does:

Creates an index (like a hash map) on StubRecord, where the key is a combination of TransactionNumber and OperatorID (e.g., TX123|OP456) for fast lookup.

✅ Why important:

Enables O(1) lookup. Avoids looping through entire XML repeatedly

🔍 Step 2: Iterating Over Payment Records

XML

<xsl:for-each select="$ReadSourceFile/.../ns24:PaymentRecord[

  count(. | key('stub-by-txn-op', concat(ns24:TransactionNumber,'|',normalize-space(ns24:Filler3)))) = 1

]">

💡 What’s happening here:

👉 Uses Muenchian grouping with xsl:key to fetch matching stub records and ensure only unique records are processed, efficiently eliminating duplicates.

🔍 Step 3: Variable Creation for Lookup

XML

<xsl:variable name="txn" select="ns24:TransactionNumber"/>

<xsl:variable name="op" select="normalize-space(ns24:Filler3)"/>

👉 These variables:

Store values for reuse

Improve readability

Avoid repeated XPath evaluation

🔍 Step 4: Fetch Matching Stub Record

XML

<xsl:variable name="stub"

    select="key('stub-by-txn-op', concat($txn,'|',$op))"/>

💡 Key Benefit:

Direct lookup instead of looping

Much faster than:

XML

//ns24:StubRecord[TransactionNumber=$txn and OperatorID=$op]

🔍 Step 5: Data Mapping Logic

Example: Payment Mode

XML

<xsl:choose>

  <xsl:when test="ns24:PayMethod='C'">CASH</xsl:when>

  <xsl:when test="ns24:PayMethod='N'">CHEQUE</xsl:when>

</xsl:choose>

👉 Converts coded values into business-friendly values.

Example: Payment Source

XML

<xsl:choose>

  <xsl:when test="ns24:PayMethod='C'">HKPOSTCS</xsl:when>

  <xsl:when test="ns24:PayMethod='N'">HKPOSTCQ</xsl:when>

</xsl:choose>

Example: Account Number from Key Lookup

XML

<xsl:value-of select="normalize-space($stub/ns24:StubAccountNumber)"/>

👉 This uses the key-based lookup result.

Example: Amount Formatting

XML

<xsl:choose>

  <xsl:when test="number(ns24:PaymentAmount)=0">0.00</xsl:when>

  <xsl:otherwise>

    <xsl:value-of select="format-number(ns24:PaymentAmount div 100,'#.00')"/>

  </xsl:otherwise>

</xsl:choose>

👉 Converts amount into proper decimal format.

⚡ Why xsl:key is Critical in OIC

❌ Without xsl:key

Nested loops

Slow performance (O(n²))

Difficult to maintain

✅ With xsl:key

Fast lookup (O(1))

Clean logic

Scales for large payloads

📊 Real Impact in OIC Integrations

Scenario - 10,000 records

Without Key: Slow. Lookup logic: Complex

With Key: Fast lookup logic; Simple

🧠 Best Practices

✔ Always use xsl:key when:

Matching records across nodes

Handling large XML files

Avoiding nested loops

✔ Use composite keys:

XML

concat(field1, '|', field2)

✔ Normalize data:

XML

normalize-space()

🎯 Final Thoughts

Your implementation is a textbook example of efficient XSLT design:

Uses Muenchian grouping

Implements fast lookup via keys

Ensures clean and scalable transformation

Screenshot of source code:

OIC - Handling Complex Stub and Payment Mapping in Oracle Integration Cloud (OIC)

Introduction

In real-time file-based integrations using Oracle Integration Cloud (OIC), handling relationships between Stub Records and Payment Records can become tricky.

A common scenario:

• One Stub → Multiple Payments
• Multiple Stubs → One Payment

This blog explains how to handle such complex mappings using XSLT logic inside OIC.

---

Problem Statement

From the source file:

• We receive a structure containing:
  • StubRecord
  • PaymentRecord

But the relationship is not always 1:1:

• Sometimes 1 Stub → Multiple Payments
• Sometimes Multiple Stubs → 1 Payment

We need to: ✔ Match records using Transaction Number
✔ Ensure correct mapping between Stub and Payment
✔ Avoid duplication or data mismatch

---

Approach

Step 1: Iterate Over Payment Records

We start by looping through each Payment Record:

<xsl:for-each select="Payments/PaymentRecord">

---

Step 2: Check Matching Stub Count

We check how many Stub Records match the current Payment using Transaction Number:

count(StubRecord[TransactionNumber = current()/TransactionNumber])

---

Step 3: Conditional Logic (1:1 vs Multiple)

Case 1: 1 Stub ↔ 1 Payment

If count = 1:

• Direct mapping is done
• Simple and straightforward

<xsl:when test="count(...) = 1">

✔ Map fields directly
✔ No extra handling required

---

Case 2: Multiple Mapping Scenario

If count > 1:

• Either:
  • One Payment → Multiple Stubs
  • Multiple Payments → One Stub

👉 Here we handle carefully using variables

---

Step 4: Store Payment Info in Variable

We store payment-related data in variables so it can be reused:

<xsl:variable name="paymentAmount" select="PaymentAmount"/>
<xsl:variable name="transactionNumber" select="TransactionNumber"/>

This helps in: ✔ Avoiding repeated calculations
✔ Reusing values across multiple stub mappings

---

Step 5: Loop Through Stub Records

Now, iterate through matching Stub Records:

<xsl:for-each select="StubRecord[TransactionNumber = $transactionNumber]">

Inside this loop:

• Map stub-specific fields
• Use stored payment variables

---

Step 6: Final Mapping Strategy

Scenario	Logic
1 Stub → 1 Payment	Direct mapping
1 Stub → Multiple Payments	Loop Payments
Multiple Stubs → 1 Payment	Store Payment in variable, loop Stubs
Multiple ↔ Multiple	Combine filtering + variables

---

Key Highlights

✔ Use current() for correct context reference
✔ Use count() to identify mapping scenarios
✔ Use xsl:variable to store reusable values
✔ Always filter using Transaction Number

---

Sample Logic Summary

<xsl:template match="/">

  <Target>

    <!-- Loop Payment Records -->
    <xsl:for-each select="ReadResponse/Payments/PaymentRecord">

      <xsl:variable name="txn" select="TransactionNumber"/>

      <Record>

        <!-- Payment Info -->
        <PaymentTxn>
          <xsl:value-of select="$txn"/>
        </PaymentTxn>

        <PaymentAmount>
          <xsl:value-of select="PaymentAmount"/>
        </PaymentAmount>

        <!-- Loop matching Stub Records -->
        <xsl:for-each select="/ReadResponse/Payments/StubRecord[TransactionNumber = $txn]">

          <Stub>

            <StubAccount>
              <xsl:value-of select="StubAccountNumber"/>
            </StubAccount>

            <StubAmount>
              <xsl:value-of select="StubAmount"/>
            </StubAmount>

          </Stub>

        </xsl:for-each>

      </Record>

    </xsl:for-each>

  </Target>

</xsl:template>

---

Conclusion

Handling complex relationships between Stub and Payment records requires:

• Smart use of XSLT looping
• Conditional checks using count()
• Efficient reuse using variables

By implementing this approach, you can: ✔ Ensure accurate 1:1 mapping
✔ Handle multiple scenarios seamlessly
✔ Avoid duplication and mismatches

---

Pro Tip 💡

Always validate your mapping with:

• Single record case
• Multiple record case
• Edge cases (missing or unmatched Transaction Numbers)

OIC - Converting UTC DateTime String to Hong Kong Time in Oracle Integration Cloud

When integrating systems in Oracle Integration Cloud (OIC), it is common to receive date-time values in UTC format as strings from source systems. Sometimes downstream systems require the date-time in a different timezone, such as Hong Kong Time (HKT).

In this blog, we will see how to convert a UTC datetime string into Hong Kong time using XPath functions inside OIC.

Problem Statement

The source system sends a datetime value in UTC format as a string:

2026-01-29T10:00:00Z

But the target system expects the time in Hong Kong timezone (UTC +8).

So we need to:

• Convert the string to xsd:dateTime
• Adjust the timezone to +8 hours
• Format the output datetime.

Solution

We can use the XPath functions adjust-dateTime-to-timezone and format-dateTime.

XPath Expression

xp20:format-dateTime(

   fn:adjust-dateTime-to-timezone(

      xsd:dateTime("2026-01-29T10:00:00Z"),

      xsd:dayTimeDuration("PT8H")

   ),

   "[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z"

)

Explanation

1. Convert String to DateTime

xsd:dateTime("2026-01-29T10:00:00Z")

This converts the string value into an XML datetime format.

2. Adjust Timezone

fn:adjust-dateTime-to-timezone(... , xsd:dayTimeDuration("PT8H"))

PT8H means plus 8 hours

Hong Kong timezone is UTC +8

So the system adjusts the time accordingly.

Example:

UTC Time

Hong Kong Time

2026-01-29T10:00:00Z

2026-01-29T18:00:00

3. Format the Output

xp20:format-dateTime(...,"[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z")

This formats the datetime into a standard ISO format.

Output:

2026-01-29T18:00:00Z

When to Use This

This approach is useful when:

Source system sends UTC timestamps

Target system expects local timezone

Integration logic is implemented in OIC mapper expressions

Typical scenarios include:

ERP integrations

HR systems

Global payroll or workforce systems

Cross-region API integrations

Key Takeaways

OIC supports timezone manipulation using XPath functions.

adjust-dateTime-to-timezone helps convert UTC to any timezone.

PT8H represents 8 hours offset for Hong Kong time.

Always convert string → xsd:dateTime before timezone adjustment.

Microsoft Excel - New Excel functions for 2019 and microsoft excel 365 editions

Working...

📘 Introduction to New Excel Functions

Microsoft introduced powerful dynamic array functions in Microsoft Excel 365 that are not available in Excel 2019 (except a few like TEXTJOIN).

✅ Excel 365 (Dynamic Array Enabled)

FILTER()

SORT()

UNIQUE()

XLOOKUP()

SWITCH()

TEXTSPLIT()

Dynamic array behavior (spill feature)

⚠ Excel 2019

No dynamic array functions

XLOOKUP not available

FILTER, SORT, UNIQUE not available

TEXTJOIN and SWITCH are available

No spill functionality

🔹 Microsoft Excel 365 FILTER() Function

📌 Purpose:

Filters data based on criteria.

✅ Syntax:

Excel

Copy code

=FILTER(array, include, [if_empty])

✅ Example:

Excel

Copy code

=FILTER(A2:C10, B2:B10="Sales")

Returns only rows where department is Sales.

🔹 Microsoft Excel 365 SORT() Function

📌 Purpose:

Sorts data dynamically.

✅ Syntax:

Excel

Copy code

=SORT(array, [sort_index], [sort_order])

✅ Example:

Excel

Copy code

=SORT(A2:C10, 2, 1)

Sorts by 2nd column in ascending order.

🔹 Microsoft Excel 365 UNIQUE() Function

📌 Purpose:

Returns distinct values.

✅ Syntax:

Excel

Copy code

=UNIQUE(array)

✅ Example:

Excel

Copy code

=UNIQUE(A2:A20)

Removes duplicates dynamically.

🔹 Dynamic FILTER() with Data Validation

You can combine FILTER + Data Validation (Dropdown).

Example:

Create dropdown in E1 (Departments)

Use formula:

Excel

=FILTER(A2:C20, B2:B20=E1)

Now results change automatically based on dropdown selection.

🔹 Microsoft Excel 365 XLOOKUP() Function

📌 Purpose:

Modern replacement for VLOOKUP & HLOOKUP.

✅ Syntax:

Excel

Copy code

=XLOOKUP(lookup_value, lookup_array, return_array)

✅ Example:

Excel

=XLOOKUP(E2, A2:A10, C2:C10)

✅ Advantages over VLOOKUP:

No column index number needed

Works left to right & right to left

Exact match by default

Error handling built-in

🔹 EXERCISE: Dynamic Employee Order List

🎯 Scenario:

Create a dynamic list of employees sorted by order amount.

Solution:

Excel

Copy code

=SORT(FILTER(A2:D20, C2:C20>50000), 3, -1)

✔ Filters orders above 50,000

✔ Sorts by amount descending

✔ Updates automatically

🔹 Microsoft Excel 365 SWITCH() Function

📌 Purpose:

Multiple condition replacement for nested IF.

✅ Syntax:

Excel

Copy code

=SWITCH(expression, value1, result1, value2, result2, default)

✅ Example:

Excel

Copy code

=SWITCH(A2,

"HR","Human Resource",

"IT","Information Tech",

"Unknown")

💡 TIP: TRUE Expression with SWITCH()

You can use TRUE for logical comparisons:

Excel

Copy code

=SWITCH(TRUE(),

A2>90,"A Grade",

A2>75,"B Grade",

A2>50,"C Grade",

"Fail")

This replaces multiple IF conditions.

🔹 Microsoft Excel 365 TEXTJOIN() Function

📌 Purpose:

Combines text with delimiter.

✅ Syntax:

Excel

Copy code

=TEXTJOIN(delimiter, ignore_empty, text1, text2…)

✅ Example:

Excel

Copy code

=TEXTJOIN(", ", TRUE, A2:A5)

💡 TIP: TEXTJOIN() with Criteria

Combine with FILTER:

Excel

Copy code

=TEXTJOIN(", ", TRUE, FILTER(A2:A20, B2:B20="Sales"))

Joins only Sales employees.

🔹 Microsoft Excel 365 TEXTSPLIT() Function

📌 Purpose:

Splits text into multiple columns/rows.

✅ Syntax:

Excel

Copy code

=TEXTSPLIT(text, col_delimiter)

✅ Example:

Excel

Copy code

=TEXTSPLIT(A2, ",")

Splits comma-separated values.

Microsoft Excel - Protecting excel worksheets and workbooks

🔐 1️⃣ Protecting an Entire Worksheet

This prevents users from editing locked cells.

Steps:

Open your Excel sheet.

Go to Review tab.

Click Protect Sheet.

Set a password (optional but recommended).

Choose what users are allowed to do (select cells, format, etc.).

Click OK.

👉 By default, all cells are locked — protection works only after you enable “Protect Sheet”.

🔒 2️⃣ Protecting Specific Cells in a Worksheet

If you want users to edit only certain cells:

Step 1: Unlock editable cells

Select the cells you want users to edit.

Right-click → Format Cells.

Go to Protection tab.

Uncheck Locked → Click OK.

Step 2: Protect the sheet

Go to Review → Protect Sheet.

Set password → Click OK.

Now only unlocked cells can be edited ✅

🏗 3️⃣ Protecting the Structure of a Workbook

This prevents users from:

Adding new sheets

Deleting sheets

Renaming sheets

Moving sheets

Steps:

Go to Review tab.

Click Protect Workbook.

Check Structure.

Add password (optional).

Click OK.

🔑 4️⃣ Adding a Workbook Password (Open Password)

This prevents the file from opening without a password.

Steps:

Click File → Info.

Click Protect Workbook.

Select Encrypt with Password.

Enter password → Click OK.

Save the file.

⚠️ Important: If you forget this password, it cannot be recovered easily.