OIC CCS Utility Adapter – Misleading “Client ID Wrong” Error (Actual Issue: Catalog Not Configured)

📌 Overview

While configuring a CCS (Customer Cloud Service) / Utilities Adapter connection in Oracle Integration Cloud (OIC) using Client Credentials, you might encounter an error stating:

❌ “Client ID is invalid” or “Client credentials are incorrect”

At first glance, this looks like an authentication issue—but that’s not always the case.

This blog explains a real scenario where the credentials were correct, but the actual issue was missing Web Service Catalog configuration in CCS.

⚠️ The Problem Statement

You create an OIC connection using:

Security Policy: OAuth Client Credentials

Correct Client ID & Secret

While testing the connection:

❌ Error: Invalid Client ID / Authentication Failed

Even after rechecking:

Credentials ✔️

Token URL ✔️

Scope ✔️

Still failing.

🧠 Root Cause (Hidden Issue)

The actual issue was:

🚨 Web Service Catalog was NOT configured in the CCS (Utilities) portal

Without this configuration:

OIC cannot discover or invoke backend services

The adapter fails internally

And misleadingly throws authentication-related errors

📖 What is Web Service Catalog?

The Web Service Catalog in Oracle Utilities defines:

Which REST services are exposed

Which services OIC can access via the Utilities Adapter

Without it, even valid credentials won’t help.

🛠️ Fix: Configure Catalog in CCS

Follow these steps:

1️⃣ Login to CCS / Utilities Portal

2️⃣ Navigate to: Admin → Integration / W → Web Service Catalog

3️⃣ Select: REST Web Service Class

4️⃣ Add Required Services:

W1-ServiceCall → Service Call Maintenance

W1-Communication → Communication Maintenance

👉 These are essential inbound services for OIC communication.

🔁 Retest in OIC

After configuring the catalog:

Go back to OIC Connection

Click Test

✅ Connection should now succeed

💡 Key Takeaways

❌ Don’t trust error messages blindly in OIC

🔍 “Invalid Client ID” ≠ Always authentication issue

⚙️ Always verify:

Catalog configuration in CCS

Required REST services availability

🚀 Pro Tip

When working with Oracle Utilities Adapter:

Always validate backend readiness (Catalog + Services) before debugging authentication

If error feels misleading → check service exposure first

🧾 Conclusion

This issue is a classic example where:

Everything looks like a security problem

But turns out to be a configuration gap in backend

Fixing the Web Service Catalog resolved the issue completely—without changing any credentials.

Reference:

https://docs.oracle.com/en/industries/energy-water/cloud-integrations/24b/ccs-wacs-configuration-guide/CCS-WACS-CONFIGURATION-GUIDE/

OIC XSLT: Execute Only When At Least One Field Has Value and None Are Blank

🔷 OIC XSLT: Validate Fields Before Executing Logic

🔹 Requirement

Fields: field1, field2, field3, field4, field5

✔ Execute only when: At least one field has a value

AND no provided field is empty (including spaces)

🔹 Final XSLT

XML

<xsl:if test="

(

  string-length(normalize-space(field1)) &gt; 0 or

  string-length(normalize-space(field2)) &gt; 0 or

  string-length(normalize-space(field3)) &gt; 0 or

  string-length(normalize-space(field4)) &gt; 0 or

  string-length(normalize-space(field5)) &gt; 0

)

and

(

  (not(field1) or string-length(normalize-space(field1)) &gt; 0) and

  (not(field2) or string-length(normalize-space(field2)) &gt; 0) and

  (not(field3) or string-length(normalize-space(field3)) &gt; 0) and

  (not(field4) or string-length(normalize-space(field4)) &gt; 0) and

  (not(field5) or string-length(normalize-space(field5)) &gt; 0)

)

">

   <!-- Execute logic -->

</xsl:if>

🔹 Key Idea (1 Line)

👉 Run only if:

At least one field has non-space value AND no field is empty/blank

🔹 Why normalize-space() Matters

Converts "   " → ""

Prevents false positives from whitespace

Ensures clean validation in OIC payloads

🔹 Quick Examples

Input == Result

<root/>❌ Skip

<field1>   </field1> ❌ Skip

<field1>ABC</field1> ✅ Execute

<field1>ABC</field1><field2> </field2> ❌ Skip

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.