Thursday, September 4, 2025

OIC - SAS Token Generation for Azure Event Hub REST API Authorization in OIC

📌 Use Case

When calling Azure Event Hub REST APIs from Oracle Integration Cloud (OIC), authentication requires a Shared Access Signature (SAS) token.

  • SAS token = authorization key generated using HMAC SHA256 hashing.
  • OIC does not provide native functions to generate this.
  • Solution → Create a custom JavaScript library to build SAS token dynamically and inject it into OIC REST calls.

SAS key vs SAS Token:

An SAS Key (Shared Access Signature Key) is a security credential used in Microsoft Azure to grant limited, time-bound access to resources like Event Hubs, Blob Storage, Queues, Service Bus, etc.

🔑 How it works:

  • When you create an Azure resource (like an Event Hub namespace), Azure generates Access Keys for it.
  • These are usually two keys: Primary Key and Secondary Key.
  • Using one of these keys, you (or your code) can generate an SAS Token.
  • The SAS Token contains:
    • Resource URI (what you want to access)
    • Expiry time (when the token becomes invalid)
    • Signature (HMAC-SHA256 signed using the SAS Key)

👉 The SAS Key is the secret you store securely, and from it you generate SAS Tokens that your app or OIC flow uses in the Authorization header.

⚠️ Important:

  • Never expose the SAS Key directly in your apps or clients.
  • Always generate SAS Tokens from it and use those instead.
Solution - FLow diagram:


⚙️ Solution Steps

1. Build the OIC Custom Library

  • Download CryptoJS v3.1.2 from GitHub → CryptoJS v3.1.2.
  • Copy content from:
    • rollups/hmac-sha256.js
    • components/enc-base64.js
  • Append the following function at the bottom:
function createSharedAccessTokenUpd(uri, saName, saKey) {
    if (!uri || !saName || !saKey) {
        throw new Error("Missing required parameter");
    }

    var encoded = encodeURIComponent(uri);
    var now = new Date();
    var week = 60 * 60 * 24 * 7; // 1 week in seconds
    var ttl = Math.round(now.getTime() / 1000) + week;
    var signature = encoded + '\n' + ttl;

    var hash = CryptoJS.HmacSHA256(signature, saKey);
    var hashInBase64 = CryptoJS.enc.Base64.stringify(hash);

    var sasToken = "SharedAccessSignature sr=" + encoded +
                   "&sig=" + encodeURIComponent(hashInBase64) +
                   "&se=" + ttl +
                   "&skn=" + saName;

    return sasToken;
}
  • Save file as OICHmacSHA256.js.
  • Upload it into OIC Libraries.



2. Generate SAS Token inside OIC Mapping

  • In the integration, open your mapping canvas for the REST call.
  • For the Authorization header, call the custom JS function:
createSharedAccessTokenUpd(
  "https://<your-namespace>.servicebus.windows.net",
  "DefaultFullSharedAccessSignature",
  "<your-shared-access-key>"
)

Here, the function dynamically generates the SAS token and places it in the Authorization header.


3. Mapping in the Integration XML

In the integration file, OIC internally translates this mapping into XSLT/XML. Example:

<ns25:StandardHttpHeaders>
  <ns25:Authorization>
    <xsl:value-of select="ora:js: createSharedAccessTokenUpd(
      &quot;https://test-ns.servicebus.windows.net&quot;,
      &quot;DefaultFullSharedAccessSignature&quot;,
      &quot;wjoLBJ...= &quot; )"/>
  </ns25:Authorization>
</ns25:StandardHttpHeaders>

This ensures every REST call to Azure Event Hub uses the correct SAS token dynamically generated at runtime.


Summary

  • Built a custom OIC library with CryptoJS HMAC-SHA256 + Base64.
  • Added SAS token generator function (createSharedAccessTokenUpd).
  • Called the function in OIC mapping → populated the Authorization header.
  • Verified via XSLT/XML that the SAS token gets injected into the REST API call.

This approach ensures secure and reusable Azure Event Hub connectivity from OIC.



Wednesday, September 3, 2025

XSD - Difference Between ref and type in XSD

Using type (local definition)

Here, we define elements directly with their datatypes.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           targetNamespace="http://example.com/auth"
           xmlns="http://example.com/auth"
           elementFormDefault="qualified">

  <!-- Local elements -->
  <xs:element name="UserName" type="xs:string"/>
  <xs:element name="Password" type="xs:string"/>

</xs:schema>

XML Output (local namespace elements):

<auth:UserName xmlns:auth="http://example.com/auth">usr</auth:UserName>
<auth:Password xmlns:auth="http://example.com/auth">pwd</auth:Password>

Using ref (reusing global elements)

First, define global reusable elements in a common schema.

common.xsd

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           targetNamespace="http://example.com/common"
           xmlns="http://example.com/common"
           elementFormDefault="qualified">

  <!-- Global elements -->
  <xs:element name="MessageId" type="xs:string"/>
  <xs:element name="Timestamp" type="xs:string"/>

</xs:schema>

Now, reference those in the service schema.

service.xsd

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           targetNamespace="http://example.com/service"
           xmlns:svc="http://example.com/service"
           xmlns:common="http://example.com/common"
           elementFormDefault="qualified">

  <!-- Import the common schema -->
  <xs:import namespace="http://example.com/common" schemaLocation="common.xsd"/>

  <!-- Reference global elements -->
  <xs:element ref="common:MessageId"/>
  <xs:element ref="common:Timestamp"/>

</xs:schema>

XML Output (reused common namespace elements):

<common:MessageId xmlns:common="http://example.com/common">12345</common:MessageId>
<common:Timestamp xmlns:common="http://example.com/common">2025-09-03T10:00:00</common:Timestamp>

3. Combined SOAP Example

Both styles used together in a real request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                  xmlns:auth="http://example.com/auth"
                  xmlns:common="http://example.com/common">
   <soapenv:Header/>
   <soapenv:Body>
      <auth:LoginRequest>
         <!-- Local elements (defined with type) -->
         <auth:UserName>usr</auth:UserName>
         <auth:Password>pwd</auth:Password>

         <!-- Global reusable elements (referenced with ref) -->
         <common:MessageId>12345</common:MessageId>
         <common:Timestamp>2025-09-03T10:00:00</common:Timestamp>
      </auth:LoginRequest>
   </soapenv:Body>
</soapenv:Envelope>

✅ Summary

  • type → Local definition, used for service-specific fields (UserName, Password).
  • ref → Reference to global elements, used for shared fields (MessageId, Timestamp).

This way you can mix both:

  • Use type for fields unique to a service.
  • Use ref for fields that must come from a common namespace.



OIC - Handling Multiple Namespaces in SOAP Payloads in Oracle Integration Cloud (OIC

Handling Multiple Namespaces in SOAP Payloads in Oracle Integration Cloud (OIC)

Use Case

When integrating with SOAP-based APIs in OIC, the payload sometimes requires elements from different namespaces within the same request.
For example, a SOAP login request may contain:

  • MessageId, ReplyAddress, and Timestamp (from a Common namespace)
  • UserName and Password (from an API-specific namespace)

If the WSDL does not define these elements properly, OIC generates an incorrect SOAP request, causing deserialization errors such as:

CASDK-0033: Received a SOAP fault while invoking endpoint target...
The formatter threw an exception while trying to deserialize the message:
'Element 'UserName' from namespace ... is not expected.
Expecting element 'MessageId'.

Problem

The SOAP request requires two different namespaces for different elements:

  • Namespace A → MessageId, ReplyAddress, Timestamp
  • Namespace B → UserName, Password

If the WSDL only defines one namespace, OIC incorrectly generates the payload, mixing up the expected prefixes.


Solution Steps

Step 1: Define a Schema for MessageId

Create a schema (.xsd) for the Common namespace elements:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           targetNamespace="http://www.mv90xiApi.com/Common/2022/5"
           elementFormDefault="qualified">

  <xs:element name="MessageId" type="xs:string"/>
  <xs:element name="ReplyAddress" type="xs:string"/>
  <xs:element name="Timestamp" type="xs:string"/>

</xs:schema>

Step 2: Import Schema into WSDL

In your WSDL, import the schema so OIC can resolve the namespace correctly:

<xs:import namespace="http://www.mv90xiApi.com/Common/2022/5"/>

Then, instead of defining MessageId inline, reference it:

<xs:element ref="common:MessageId"/>

Step 3: Fix UserName and Password Namespace

Update the WSDL for the AuthRequestMessage.
Originally it might look like this (incorrect namespace usage):

<xs:complexType name="AuthRequestMessage">
  <xs:complexContent mixed="false">
    <xs:extension base="tns:RequestMessage" xmlns="http://www.mv90xiApi.com/Common/2022/5">
      <xs:sequence>
        <xs:element name="UserName" nillable="true" type="xs:string"/>
        <xs:element name="Password" nillable="true" type="xs:string"/>
      </xs:sequence>
    </xs:extension>
  </xs:complexContent>
</xs:complexType>

Change it to explicitly use the API namespace:

<xs:complexType name="AuthRequestMessage">
  <xs:complexContent mixed="false">
    <xs:extension base="tns:RequestMessage" xmlns="http://www.mv90xiApi.com/api/2022/5">
      <xs:sequence>
        <xs:element name="UserName" nillable="true" type="xs:string"/>
        <xs:element name="Password" nillable="true" type="xs:string"/>
      </xs:sequence>
    </xs:extension>
  </xs:complexContent>
</xs:complexType>

Step 4: Validate Final Payload

After modifying the WSDL, OIC generates the correct SOAP request with both namespaces:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"
               xmlns:ns="http://www.mv90xiApi.com/api/2022/5"
               xmlns:ns1="http://www.mv90xiApi.com/Common/2022/5">
  <soap:Header/>
  <soap:Body>
    <ns:Login>
      <ns:message>
        <ns1:MessageId>12345</ns1:MessageId>
        <ns1:ReplyAddress>http://reply.com</ns1:ReplyAddress>
        <ns1:Timestamp>2025-09-03T10:00:00</ns1:Timestamp>
        <ns:UserName>user</ns:UserName>
        <ns:Password>pwd</ns:Password>
      </ns:message>
    </ns:Login>
  </soap:Body>
</soap:Envelope>

Benefits of This Approach

  • Resolves namespace conflicts between common elements and authentication fields.
  • Ensures SOAP payload matches the service contract.
  • Avoids CASDK-0033 deserialization errors in OIC.

👉 Reference: Oracle Docs – Working with SOAP Integrations



Friday, August 22, 2025

OIC - Sending JSON Data as Email Body Content via Twilio SendGrid API in OIC

Use Case Description:

When integrating Oracle Integration Cloud (OIC) with Twilio SendGrid API to send emails, JSON data intended for the email body is sometimes sent as an attachment instead of inline content. This issue occurs because the API interprets the payload as attachment content, especially if the content type isn't correctly specified. The goal is to send JSON data directly within the email body using the string() function and setting the Content-Type as text/plain or application/json.

Solution Steps:
1. Serialize JSON Data to String
Use the string() function in OIC to convert your JSON object into a string format suitable for embedding in the email body.
2. Structure the Email Payload
Prepare the payload adhering to SendGrid's API format:Set "content" with "type": "text/plain" or "application/json".
Include the serialized JSON string in the "value" field.
Example payload snippet:
json
Copy
{ "personalizations": [ { "to": [{"email": "recipient@example.com"}], "subject": "JSON Data Email" } ], "from": {"email": "sender@example.com"}, "content": [ { "type": "text/plain", // or "application/json" based on requirement "value": "{your JSON string here}" } ] }
3. Setup API Headers
Ensure the HTTP headers include:
Content-Type: application/json
4. Make the API Call in OIC
Use an HTTP action to POST the above payload to SendGrid's API endpoint (https://api.sendgrid.com/v3/mail/send).
Pass the API key in Authorization headers.
5. Validate the Email Content
Check the received email to confirm that JSON data appears inline in the email body, not as an attachment.
Summary:
By serializing JSON data with string(), structuring the payload correctly, and setting the content type appropriately, you can send JSON data directly as the email body in Twilio SendGrid API through OIC, avoiding it being treated as an attachment.

Wednesday, August 13, 2025

OIC - How to Reprocess an HCM Extract in OIC Without Resubmitting the Flow

Use Case

In Oracle HCM integrations, it’s common to schedule extracts and process their output to a target destination like Oracle Object Storage. However, in real-world scenarios, the extract may fail in the middle of the process — for example, due to downstream errors — even though the extract itself completed successfully in HCM.

When this happens, you often want to reprocess the existing extract output rather than re-running the extract flow in HCM (which could cause data duplication or require additional system resources).

To handle this, we design an OIC-based solution with:

  • A Scheduler Integration to initiate the process.
  • A Main Integration to execute or reprocess the extract depending on parameters.

Solution Approach

We will create two integrations:

1. Scheduler Integration

  • Purpose: Accepts runtime parameters and decides whether to submit a new extract or reprocess an existing one.
  • Parameters:
    • EffectiveDate – Extract run date (YYYY-MM-DD format)
    • ExtractFlowInstanceName – Name of the extract flow instance to reprocess
    • SkipExtractSubmissionYes/No flag to skip submitting the extract and instead retrieve an existing output
  • Logic:
    • If SkipExtractSubmission = No → Call HCM submit extract API, wait for completion, download the file.
    • If SkipExtractSubmission = Yes → Skip submit step, directly get extract instance details, retrieve document ID, and download from UCM.



2. Main Integration

  • Purpose: Handles the extract execution, monitoring, file retrieval, and delivery to Object Storage.
  • Key Steps:
    1. Assign & Initialize Variables – Store parameters.
    2. Switch Condition – Decide if extract needs submission or reprocessing.
    3. While Loop – Poll HCM extract status until completion.
    4. Get Document ID – Retrieve from extract instance data.
    5. Download from UCM – Fetch the output file.
    6. Transform Data – Apply required mapping/format changes.
    7. Upload to Object Storage – Store file in the designated bucket.
    8. Error Handling – Throw faults if extract fails or file retrieval fails.

High-Level Flow Diagram



Benefits

  • No Duplicate Data – Avoid re-running the same extract unnecessarily.
  • Faster Recovery – Quickly reprocess failed integrations.
  • Parameter Driven – Flexible execution controlled at runtime.
  • Error Handling Built-In – Ensures issues are caught and handled.

How to download HCM extract, for details >> follow my previous blog:

https://soalicious.blogspot.com/2024/08/oic-hcm-how-to-schedule-and-download.html?m=1

Tuesday, August 12, 2025

OIC - Handling SOAP XML in a REST Trigger with Oracle Integration (OIC)

How to accept and respond with SOAP XML payloads in a REST API


Use Case

Many legacy systems still use SOAP-based XML messages for data exchange, while modern applications and integrations often rely on REST APIs.
In this scenario, we need to create an OIC REST Trigger that can:

  1. Accept a SOAP XML payload as input (request).
  2. Process the data.
  3. Return a SOAP XML response back to the caller.

This allows seamless communication between SOAP-based systems and modern RESTful endpoints without requiring the legacy system to change.


Solution Steps

1. Design the OIC Integration

  • Create a new App-Driven Orchestration in Oracle Integration.
  • Select REST as the trigger connection.

2. Configure the REST Trigger

  • Resource URL: e.g., /soapxmlhandler
  • HTTP Method: POST
  • Request Payload:
    • Set the media type to application/xml or text/xml.
    • Paste the SOAP request XSD in the request schema section.
  • Response Payload:
    • Also use application/xml or text/xml.
    • Paste the SOAP response XSD in the response schema section.





3. Import the SOAP Envelope Schema

  • Use the SOAPENV.xsd (like the 2006 OGC version in your screenshot) to define the outer SOAP structure.
  • Import your business-specific XSD (e.g., VoltageDipIncidentCustomerAccountsMessage.xsd) for the actual payload.
Add import to include xsd required.

<xs:import namespace="http://iec.ch/TC57/2011/VoltageDipIncidentCustomerAccountsMessage"
 schemaLocation="VoltageDipIncidentCustomerAccountsMessage.xsd"/>


Add request and response element from the imported xsd:

<xs:element name="Body" type="tns:Body"/>
<xs:complexType name="Body">
    <xs:sequence>
        <xs:element ref="ns1:VoltageDipIncidentCustomerAccountsResponseMessage"/>
        <xs:element ref="ns1:CreatedVoltageDipIncidentCustomerAccounts"/>
    </xs:sequence>
    <xs:anyAttribute namespace="##any" processContents="lax">
        <xs:annotation>
            <xs:documentation>
                Prose in the spec does not specify that attributes are allowed on the Body element.
            </xs:documentation>
        </xs:annotation>
    </xs:anyAttribute>
</xs:complexType>



SOAP 1.1 Specification

4. Map the Incoming SOAP Request

  • Use OIC’s mapper to extract the SOAP Body content into integration variables.
  • Process or transform as required.

5. Prepare the SOAP Response

  • Map your processed data back into the SOAP Response structure.
  • Ensure proper namespace handling (as per the SOAP schema).

6. Test the REST Endpoint

  • Use Postman or SOAP UI:
    • Send a POST request with the full SOAP XML as the body.
    • Set the Content-Type header to text/xml.
  • Verify that the response is a valid SOAP envelope.
Tested xml data:
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" 
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
            xmlns:ns1="http://iec.ch/TC57/2011/VoltageDipIncidentCustomerAccountsMessage">

    <s:Body>
        <ns1:CreatedVoltageDipIncidentCustomerAccounts>

            <!-- Header Section -->
            <Header>
                <Verb xmlns="http://iec.ch/TC57/2011/schema/message">created</Verb>
                <Noun xmlns="http://iec.ch/TC57/2011/schema/message">VoltageDipIncidentCustomerAccounts</Noun>
                <Revision xmlns="http://iec.ch/TC57/2011/schema/message">2.0</Revision>
                <Timestamp xmlns="http://iec.ch/TC57/2011/schema/message">2024-08-05T09:59:30.3759213+08:00</Timestamp>
                <Source xmlns="http://iec.ch/TC57/2011/schema/message">ABC</Source>
                <MessageID xmlns="http://iec.ch/TC57/2011/schema/message">638584487073759213</MessageID>
                <CorrelationID xmlns="http://iec.ch/TC57/2011/schema/message">638584487073759213</CorrelationID>
            </Header>

            <!-- Payload Section -->
            <Payload>
                <VoltageDipIncidentCustomerAccounts xmlns="http://iec.ch/TC57/2007/VoltageDipIncidentCustomerAccounts#">

                    <!-- Incident Record 1 -->
                    <IncidentRecord>
                        <mRID>INC1233038290</mRID>
                        <createdDateTime>2024-08-05T09:52:21+08:00</createdDateTime>
                        <CustomerAccounts>
                            <mRID>32812156411</mRID>
                        </CustomerAccounts>
                        <CustomerAccounts>
                            <mRID>32812156412</mRID>
                        </CustomerAccounts>
                    </IncidentRecord>

                    <!-- Incident Record 2 -->
                    <IncidentRecord>
                        <mRID>INC1233038291</mRID>
                        <createdDateTime>2024-08-05T08:32:25+08:00</createdDateTime>
                    </IncidentRecord>

                    <!-- Incident Record 3 -->
                    <IncidentRecord>
                        <mRID>INC1233038292</mRID>
                        <createdDateTime>2024-08-05T07:35:21+08:00</createdDateTime>
                        <CustomerAccounts>
                            <mRID>32812156412</mRID>
                        </CustomerAccounts>
                    </IncidentRecord>
               </VoltageDipIncidentCustomerAccounts>
            </Payload>
     </ns1:CreatedVoltageDipIncidentCustomerAccounts>
    </s:Body>
</s:Envelope>

7. Deployment

  • Activate the integration in OIC.
  • Share the REST endpoint URL with the consuming SOAP system.


Monday, August 4, 2025

OIC - How to Rename a File in Oracle Object Storage using Oracle Integration (OIC)

Use Case

In Oracle Cloud, Object Storage is often used as a staging area for ERP file processing, such as GL Extract, HCM Extract, or bulk data loads. However, once a file is processed successfully in ERP, it’s best practice to rename the file to avoid reprocessing or for better traceability.

For example, after a GL Extract File is loaded into ERP successfully, we want to rename it by adding a prefix such as Processed_ or appending a timestamp. This avoids confusion and maintains clear file lifecycle management.


Solution Steps

1. Object Storage REST API – renameObject

Oracle Object Storage provides a renameObject action that allows you to rename an object within a bucket without re-uploading it.

API Endpoint Format:

POST /n/{namespaceName}/b/{bucketName}/actions/renameObject

Oracle Docs:
renameObject API – Oracle Cloud Infrastructure


2. Sample Request JSON

{
  "sourceName": "SourceObjectName",
  "newName": "TargetObjectName",
  "srcObjIfMatchETag": "*",
  "newObjIfMatchETag": "*",
  "newObjIfNoneMatchETag": "*"
}
  • sourceName → Current file name in the bucket.
  • newName → New file name after rename.
  • * in ETag fields ensures no version conflicts during rename.

3. Implementation in Oracle Integration (OIC)

Step 3.1 – Configure REST Invoke

  • Name: RenameGLExtractFile
  • Method: POST
  • Relative URI:
    /n/{namespaceName}/b/{bucketName}/actions/renameObject
    
  • Enable Add and review parameters & Configure request payload options.

Step 3.2 – Create Request Mapping

From the OIC mapping canvas:

  • Map sourceName to the original filename variable (e.g., glExtFileName).
  • Map newName to the expression that generates the updated filename:
    concat(Var_PrefixTag_AddToFilename_FileUploaded, name)
    
  • Set srcObjIfMatchETag, newObjIfMatchETag, and newObjIfNoneMatchETag to "*".

Step 3.3 – Pass Template Parameters

  • bucketName → OIC variable holding the target bucket name.
  • namespaceName → OIC variable holding Object Storage namespace.

Step 3.4 – Test the Flow

Once the file is loaded successfully in ERP:

  1. Invoke the renameObject API via your configured REST connection.
  2. Verify in OCI Console → Object Storage → The file appears with the new name.

Example Scenario

  • Before Rename: GLExtract_20250804.txt
  • After Rename: Processed_GLExtract_20250804.txt

References

Screenshots:






Featured Post

OIC - OIC Utility to Reprocess Failed Real-Time Integration JSON Payloads

📌 Use Case In real-time OIC integrations, JSON payloads are exchanged with external systems via REST APIs. When such integrations fail (du...