OIC - Secure Payload Handling in OIC using OCI Vault & Functions (AES + RSA) | Message level encryprion/decryption and signing/verification

Work in progress...

Overview

This blog covers a secure and scalable design pattern in Oracle Integration Cloud (OIC) where:

• Incoming payload is AES encrypted and RSA signed
• Keys are fetched from OCI Vault
• Cryptographic operations are handled via OCI Functions
• Response is encrypted and signed if required before sending back

---

Architecture

Source
 ↓ (AES Encrypted + RSA Signed)
Main Integration
 ↓
Crypto Integration
   → Fetch keys from OCI Vault
   → Call Function (AES Decrypt)
   → Call Function (RSA Verify)
 ↓
Main Integration
   → Call Target
   ← Response
 ↓
Crypto Integration
   → Call Function (AES Encrypt)
   → Call Function ( RSA Signed) optional
 ↓
Source (Encrypted and signed Response)

OCI Functions: 

   - Function 1: AES Encrypt/Decrypt
   - Function 2: RSA Sign/Verify

---

Scenarios:

Scenario 1:  Source data is AES encrypted. In OIC, we decrypt the data and use and later on we encrypt the response and send back to Source.

For java function code , use below link:

https://soalicious.blogspot.com/2026/04/oic-oci-function-code-to-encrypt-and.html

Scenario 2: Source data is RSA signed. Need to verify request message and sign to send response back

https://soalicious.blogspot.com/2026/04/oic-rsa-sign-and-verify-java-code-for.html

Scenario 3:  source data is in AES encrypted, cipher key = rsa encrypted(aes key), sign key rsa key, Need to verify the message and decrypt cipher key as rsa decryprion to get AES key which to use to decrypt the data/message.

RSA key Encryption Decryption:

https://soalicious.blogspot.com/2026/05/oic-oci-java-function-code-for-rsa.html

AES Key Encryption Decryption:

https://soalicious.blogspot.com/2026/04/oic-oci-function-code-to-encrypt-and.html

RSA SIGN Verify:

https://soalicious.blogspot.com/2026/04/oic-rsa-sign-and-verify-java-code-for.html

End-to-End Flow:

Step 1: Receive Request (Main Integration)

• Expose REST API
• Input contains:
• Encrypted payload (Base64)
• Signature
• Salt or IV

Why Salt or IV required?

TBD

➡️ Call Crypto Integration for processing

---

Step 2: Crypto Processing Integrations

This integration acts as a central reusable crypto layer.

2.1 Fetch Keys from OCI Vault

• Retrieve:
  • AES Secret Key
  • RSA Private/Public Keys
• Use secure REST call / OCI SDK

Ensures no key is hardcoded in OIC

---

2.2 Call OCI Function – Decrypt

• Pass encrypted payload + key reference
• Function performs:
  • Base64 decode
  • AES decryption

Returns: Plain payload

---

2.3 Call OCI Function – RSA Verify

• Pass payload + signature + public key
• Function validates signature

Condition:

• Valid → proceed
• Invalid → throw fault

---

Step 3: Return Decrypted Data to Main Integration

• Crypto Integration sends verified plain payload back

---

Step 4: Business Processing (Main Integration - 1)

• Transform data
• Call target system
• Receive response

---

Step 5: Encrypt Response

Main Integration again calls Crypto Integration (2)

5.1 Call OCI Function – AES Encrypt

• Encrypt response payload
• Base64 encode

No RSA signing required for response

---

Step 6: Send Response

• Return encrypted response to source system

---

Conclusion

This approach provides a clean, secure, and enterprise-ready pattern in OIC by combining:

• OCI Vault for secure key management
• OCI Functions for cryptographic operations
• Reusable integrations for maintainability

A perfect design for handling sensitive real-time integrations at scale.

---

OIC File Handling – Removing .pgp and Preserving .csv (Dynamic File Name Logic) | use of ast-index-within-string() function

📌 Problem Statement

In Oracle Integration Cloud (OIC), while working with Stage File, we often receive source files like:

a.b.c.csv.pgp 🔐 (encrypted file)

a.b.c.csv 📄 (plain file)

🎯 Requirement

If file = a.b.c.csv.pgp → Target should be a.b.c.csv

If file = a.b.c.csv → Keep as a.b.c.csv

⚠️ Challenge

Handling multiple dots (.) in file names:

Simple logic may break

Functions like last-index-of() (or OIC equivalent) may not always behave as expected in mapper

✅ Approach 1: Nested substring-before/after (Working & Stable).

💻 Code Snippet

XML

fn:concat(

  fn:substring-before($FileName, '.'),

  '.',

  fn:substring-before(fn:substring-after($FileName, '.'), '.'),

  '.',

  fn:substring-before(fn:substring-after(fn:substring-after($FileName, '.'), '.'), '.'),

  '.csv'

)

✔️ Behavior

Extracts each part between dots

Reconstructs filename ending with .csv

👍 Advantages

Works consistently in OIC mapper

No dependency on special functions

👎 Disadvantages

Not dynamic (hardcoded for 3 segments like a.b.c)

Breaks if file structure changes (e.g., a.b.c.d.csv.pgp)

⚡ Approach 2: Using oraext:last-index-within-string (Dynamic but Risky)

💻 Code Snippet

XML

substring(

  $filename,

  1,

  oraext:last-index-within-string($filename, '.')

)

✔️ Behavior

Dynamically trims extension

Works for any filename length

👍 Advantages

Fully dynamic

Handles multiple dots easily

👎 Disadvantages

❌ In OIC mapper, sometimes does not evaluate correctly

Can fail depending on context (Stage File / namespaces)

Less predictable compared to substring chaining

🧠 Recommended Hybrid Logic

👉 Best practical approach:

Use substring logic when:

File pattern is fixed (like a.b.c.csv.pgp)

Use last-index logic when:

File pattern is dynamic

Tested properly in your integration

🚀 Pro Tip

If your only goal is to remove .pgp, a simpler and safer approach:

XML

fn:replace($filename, '.pgp', '')

✔️ Works for both cases:

a.b.c.csv.pgp → a.b.c.csv

a.b.c.csv → unchanged

🧾 Conclusion

OIC file handling becomes tricky with multiple dots

substring-before → stable but rigid

last-index-within-string → flexible but unreliable in some cases

🎯 Best solution depends on your file pattern stability

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: