Sunday, July 13, 2025

OIC - Extract RSA Public Key from a Certificate – Two Easy Methods (OpenSSL & Java)

๐Ÿ“Œ Use Case:

When integrating with third-party platforms (e.g., Oracle Integration Cloud, REST APIs with JWT, or SAML), you often receive a certificate. To validate tokens or signatures, you must extract the RSA public key from an X.509 certificate file (like .cer or .crt).


✅ Solution Steps

๐Ÿ”ฝ Input:

  • A .cer or .crt file (Base64-encoded X.509 format)
  • Goal: Extract the RSA public key in readable format

๐Ÿ”ง Option 1: Using OpenSSL (Command Line)

๐Ÿ“ฅ Steps:

  1. Save your certificate as cert.pem (Base64 X.509 format).
  2. Run this command:
openssl x509 -in cert.pem -pubkey -noout > public_key.pem

✅ Output:

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQE...
-----END PUBLIC KEY-----

๐Ÿ“Ž Notes:

  • Works on Linux/macOS/WSL/Windows with OpenSSL installed
  • Easy to use in automation scripts

☕ Option 2: Using Java Code (Without OpenSSL)

๐Ÿ“ฅ Steps:

1. Convert .cer File to Base64 Text:

  • Open your .cer file (which is binary) in any Base64 encoder (e.g., PowerShell, online tool, or base64 CLI).
  • It should look like this:
-----BEGIN CERTIFICATE-----
MIIDczCCAlugAwIBAgIEXV...<trimmed>...C2s85w==
-----END CERTIFICATE-----




2. Copy Only the Certificate Key Part:

  • Copy the middle Base64 key part (remove headers and newlines).
  • Store it in a Java string like base64Cert in the code below.

✅ Java Code:

import java.io.ByteArrayInputStream;
import java.security.PublicKey;
import java.security.cert.CertificateFactory;
import java.security.cert.X509Certificate;
import java.util.Base64;

public class RSAPublicKeyExtractor {
    public static void main(String[] args) throws Exception {
        // Step 1: Paste your base64-encoded certificate string here
        String base64Cert = 
            "MIIDczCCAlugAwIBAgIEXV...<full cert key here>...C2s85w==";

        // Step 2: Decode the base64 string
        byte[] certBytes = Base64.getDecoder().decode(base64Cert);

        // Step 3: Convert to X.509 certificate
        CertificateFactory certFactory = CertificateFactory.getInstance("X.509");
        X509Certificate cert = (X509Certificate) certFactory
            .generateCertificate(new ByteArrayInputStream(certBytes));

        // Step 4: Extract public key
        PublicKey publicKey = cert.getPublicKey();

        // Step 5: Print the public key details
        System.out.println("Public Key Algorithm : " + publicKey.getAlgorithm());
        System.out.println("Public Key Format    : " + publicKey.getFormat());
        System.out.println("Public Key (Base64)  : ");
        System.out.println(Base64.getEncoder().encodeToString(publicKey.getEncoded()));
    }
}

๐Ÿงพ Sample Output:

Public Key Algorithm : RSA
Public Key Format    : X.509
Public Key (Base64)  :
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArpM...


Thursday, July 10, 2025

OIC - Lessons Learned & Improvements in OIC Integrations

๐Ÿ“˜ Use Case

During various OIC projects, we identified recurring issues that impacted logging, error tracking, retry handling, and monitoring through tools like DataDog. Here’s a list of key observations and the solutions we applied.


1. Suppressed Error Details

  • Observation:
    OIC sends only a generic error message to DataDog or external logs, hiding the actual root cause.

  • Solution:
    Capture the actual faultMessage in error handlers and send it to DataDog along with other details for better troubleshooting.


2. No Retry for Temporary Errors

  • Observation:
    Transient connectivity or network issues fail immediately without any retry attempt.

  • Solution:
    Add retry logic using fault handlers or scopes for specific error types (like connection timeouts or 5xx errors).


3. Missing Correlation ID for Fusion Failures

  • Observation:
    When a Fusion ESS job fails, the logs don’t include any identifier like the ESS Job ID or request ID, making it hard to trace.

  • Solution:
    Extract and log the ESS request ID or other correlation IDs from Fusion and include them in your custom logs.


4. Payload Not Validated

  • Observation:
    OIC flows sometimes try to process empty or null payloads, which leads to schema errors or misleading messages.

  • Solution:
    Add condition checks early in the flow to verify if payloads contain data before proceeding to mappings or invokes.


5. Only Errors Logged, Not Success

  • Observation:
    DataDog or similar tools receive only error logs, and successful integrations are not tracked, affecting KPI reporting.

  • Solution:
    Log success cases as well, including important business identifiers like invoice number, PO number, or employee ID for better tracking.

6. Integration Timeout Not Handled

Observation:
Some long-running integrations fail due to timeout, especially when calling external systems that take time to respond.

Solution:
Adjust the timeout settings in the connection properties. Also, wrap such calls in a scope with timeout handling logic to provide custom error messages or fallback.

7. Overuse of Hardcoded Values

Observation:
Many integrations had hardcoded values for endpoints, credentials, or lookup keys, making them hard to migrate or scale.

Solution:
Use Lookups, Global Variables, and Connections smartly to externalize values. Parameterize as much as possible.

8. No Archival or Logging of Request Payloads

Observation:
When issues occurred, there was no record of what payload was received—making RCA difficult.

Solution:
Log incoming request payloads (masked if sensitive) to file server, UCM, or external logging systems before processing.

9. Overloaded Error Handlers Catching Everything

Observation:
A single generic error handler catches all faults, masking the actual error and causing confusion.

Solution:
Use specific fault handlers (like for Timeout, AuthenticationFailure, ServiceError) instead of one "catch-all" block. Customize messages accordingly.

10. Lack of Version Control or Documentation

Observation:
Integration flows were updated without tracking changes or maintaining documentation, making it difficult for others to manage.

Solution:
Maintain version notes or release logs.

Use naming conventions for integration versions.

Document integration logic, mappings, and lookups in a central repo or Confluence page.

11. Poor Use of Data Stitching (Unnecessary Variables)

Observation:
Multiple unnecessary variables and assignments are used where direct mapping or transformation would work.

Solution:
Optimize mappings and data handling. Use fewer intermediate variables and go for direct expressions or XSLT if needed.

12. Integration Not Idempotent

Observation:
Some integrations post the same data multiple times if retried, causing duplicates in target systems.

Solution:
Implement idempotency checks—use message IDs, reference numbers, or flags in the target system to avoid re-processing.

๐ŸŽฏ Outcome

Implementing these improvements helped us:

  • Get full visibility into success and failure cases
  • Reduce debugging time
  • Improve monitoring accuracy in tools like DataDog
  • Increase reliability of integrations with retry logic


Sunday, July 6, 2025

OIC - How to upload file to sharepoint using Microsoft graph API

๐Ÿ“Œ Use Case

In this use case, we are building an integration in Oracle Integration Cloud (OIC) that:

  1. Downloads a file from a File Server.
  2. Uploads that file to a specific SharePoint folder using Microsoft Graph API.

This is especially helpful in scenarios where enterprises manage data exports on file servers and want to automate data archival or sharing via SharePoint.


⚙️ Solution Design Overview

The integration follows these main steps:

  1. Trigger – A scheduled or REST-based trigger initiates the process.
  2. Fetch Site ID – Retrieves SharePoint Site ID using the server-relative path.
  3. Fetch Drive ID – Retrieves the Drive ID associated with the Site.
  4. Download File – Reads the file from the file server.
  5. Upload File – Uploads the file to SharePoint using Microsoft Graph API PUT call.

๐Ÿ” Step-by-Step Solution


✅ Step 1: Get SharePoint Site ID

  • REST Endpoint Name: GetSiteID
  • Method: GET
  • Relative URI:
    /sites/{tenant}.sharepoint.com%3A/sites/{server-relative-path}
    
  • Response Sample:
    {
      "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites/$entity",
      "createdDateTime": "2022-09-26T07:22:04.923Z",
      "description": "sp_org_app_DWCSSystemIntegration_qa",
      "id": "yourtenant.sharepoint.com,c87b311d-f1f0-4576-9f43-256b0366ccd4,315734c4-892c-486f-8901-5c8827144a16",
      "lastModifiedDateTime": "2024-08-23T11:25:16Z",
      "name": "sp_org_app_DWCSSystemIntegration_qa"
    }

✅ Step 2: Get SharePoint Drive ID

  • REST Endpoint Name: GetDriveID
  • Method: GET
  • Relative URI:
    /sites/{siteId}/drives
    
  • Query Parameter:
    $filter = name eq '<folder_name>'
    
  • Site ID is dynamically extracted from the previous response using an XSL mapping.

๐Ÿง  Conditionally Construct Filter Query:

  • Use substring-before() if parentpath has /
  • Else use as is

Sample response:

{

  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#drives",

  "value": [

    {

      "id": "b!0mFabc12345def6789ghiJKLmnopQRSTuvwxYZaBCDE",

      "driveType": "documentLibrary",

      "name": "Documents",

      "webUrl": "https://yourtenant.sharepoint.com/sites/testsite/Shared%20Documents",

      "createdDateTime": "2023-04-20T10:30:00Z",

      "lastModifiedDateTime": "2024-03-15T08:45:00Z",

      "createdBy": {

        "user": {

          "displayName": "Admin User",

          "id": "admin-user-id"

        }

      },

      "lastModifiedBy": {

        "user": {

          "displayName": "Admin User",

          "id": "admin-user-id"

        }

      }

    },

    {

      "id": "b!9xYz321klmn456uvwXYZabcDEfghiJKLMNoPQrsTUv",

      "driveType": "documentLibrary",

      "name": "Shared Documents",

      "webUrl": "https://yourtenant.sharepoint.com/sites/testsite/Shared%20Documents"

    }

  ]

}



✅ Step 3: Download File from File Server

  • Action: Use File Adapter with Native File System (FS)
  • Read Mode: Binary
  • Output: Stream Reference

✅ Step 4: Upload File to SharePoint

  • REST Endpoint Name: UploadFileToSharepoint
  • Method: PUT
  • Relative URI:
    /drives/{driveid}/root:/{filename}:/content
    
  • Payload Format: Binary
  • Content-Type: Set as dynamic or static depending on file type
    Example: text/csv or application/octet-stream

๐Ÿงฉ Key Integration Design Elements

Component Description
Trigger REST or Schedule Trigger
File Server Native File Adapter
SharePoint Microsoft Graph API
Mapping Used to extract siteId, build filter, and construct headers
Headers Content-Type (optional but recommended)
Payload Binary Stream from File Adapter

๐Ÿ› ️ Pre-requisites

  • Microsoft Graph OAuth 2.0 Authentication configured in OIC
  • SharePoint API permissions:
    • Sites.Read.All
    • Files.ReadWrite.All
  • File server connection configured
  • OIC connectivity agent if on-prem file server

๐Ÿ“Œ Conclusion

With this approach, you can automate file transfers between a File Server and SharePoint seamlessly using OIC. This design is scalable and allows for dynamic path and filename handling, making it robust for real-world enterprise use cases.


Implementation screenshots:

Trigger:



Get file from file server



Get site id




Get drive id






Upload file to sharepoint







Thursday, July 3, 2025

OIC - How to Generate JWT CID Token with SHA256 Hash in Oracle Integration Cloud (OIC)

๐Ÿ” How to Generate JWT CID Token with SHA256 Hash in Oracle Integration Cloud (OIC)

๐Ÿงฉ Use Case

As part of secure API integration with HSBC (or any financial institution requiring strict identity/authentication enforcement), the client must send a JWT (JSON Web Token) as a CID (Client Identification Token) in the Authorization header of each API request. This token includes a signed hash (SHA-256) of the payload body to ensure message integrity.

This post walks you through how to:

  • Construct the JWT token using base64 encoded header and payload.
  • Generate the SHA256 hash of the payload body.
  • Sign the token using a private key  and java oci function.
  • Assemble and use the CID token in OIC integration.

Overall high level Steps: 

1. Send the CID token to the vendor.
2. Generate CID JWT token.


Step1: Send JWT token to the Vendor:

Headers:

Standard:

  • Authorization : JWS <CID Token> 
  • Accept-Language : en-GB 
  • Content-Type : application/json 
Custom:
  • X-Forwarded-For : <IP Address> Metadata/Environment/baseURL
  • X-HSBC-Chnl-CountryCode : HK 
  • X-HSBC-Chnl-Group-Member : HBAP
  • X-HSBC-Global-Channel-Id : PARTNER
  • X-HSBC-Request-Correlation-Id : JTI
  • X-HSBC-Client-Id :client name
Request:
  • Salt
  • MessageBody
  • Sugnature


Step2: Generate CID JWT token.

๐Ÿ—️ JWT Structure

Format:

JWT token = BASE64URL(JWT Header) + "." + BASE64URL(JWT Body) + "." + BASE64URL(Signature)

๐Ÿ” OIC Implementation Steps

  1. Configure trigger for common service to generate token
  2. Write payload data required for hashing
  3. Write JWT Header data
  4. Write JWT Body data with hashing
  5. Remove base64 padding training chars from JWT header and body data
  6. Call the common function to create signature
  7. Generate JWR token and share 
Step1: Configure trigger:
Request Payload
{
  "ParentProcessId": "",
  "InterfaceId": "INTXXX",
  "Data": {
    "Salt": "XXXXXXXXXX",
    "MessageBody": "XXXXXXXXXXXXXX",
    "Signature": "XXXXXXXXXXXXXXXXXXXXxxx"
  }
}
Response Payload
{
  "cidToken": "Encrypted Message",
  "iat": "1750411716",
  "jti": "91be275c-a920-4ef9-ac39-1dbe3f50372d",
  "payload_message": ""
}




Step2: Write Payload data required for hashing




Step3: Write JWT header data

Example:

{
  "ver":"1.0",
  "typ": "JWT",
  "alg": "RS256",
  "kid": "XYZ"
}


Step4: Write JWT body with hashing

Create payload data for hashing:

{"Salt":"","MessageBody":"","Signature"}




Example payload:

{
  "sub": "CLP",
  "aud": "EPS",
  "payload_hash_alg": "SHA-256" or "RSASHA256",
  "payload_hash": "<hash from JS function>",
  "iat": 1750411716,
  "jti": "91bee275c-a920-4ef9-ac39-1dbe3f50372d"
}

Used custom checksum function to create hash key for the payload data stored in the stage. Use below blog for details:

For iat : use below blog to generate teh unix time.

https://soalicious.blogspot.com/2025/04/oic-converting-normal-datetime-to-unix.html

Step5: Remove base64 padding training chars from JWT header and body data


See my below blog for more details:

https://soalicious.blogspot.com/2026/08/oic-removing-base64-padding-for-jwt.html

Step6: Create signature(sign/verify):



Messgae passed as concat of "encode base64 url jwt header data" , "." ,"emcode base64 url jwt body data"

Step7: create JWT token and send back as resposne to the caller service.

Jwt token : base64 url(header) . Base64url(body).Base64Url(signature)

Payload message: same sent as received in step2



Follow below blog for java function code - sign / verify using RSA private and public key pair.

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


✅ Final Output

A complete CID token is structured like:

JWS eyJ2ZX...<Header>.eyJzdW...<Payload>.X1c8Cp...<Signature>

It is passed to the Authorization header like:

Authorization: JWS eyJ2ZX...<Signature>

๐Ÿงช Testing & Validation

  • Use Postman or SoapUI to validate the generated JWT.
  • Tools like jwt.io help decode and verify token.
  • Ensure OIC has access to private key and correct time sync for iat.

Sunday, June 22, 2025

OIC ERP - How to Restore Missed or exhausted business events in OIC

Receiving Missed Business Event in OIC

Step1: Deactivate an OIC Orchestration which has subscribed to ERP Business Events for PO Receipts.

Note: If the orchestration to be deactivated contains a business event subscription, a message is displayed asking if you want to delete the event subscription while deactivating the orchestration. If you select to delete the event subscription, the integration does not receive any events after it is reactivated. Below is just an example screenshot.

If you do not want to delete the event subscription, the events in this integration are resent if the integration is activated within six hours. Beyond 6 hours those requests will be exhausted.

Step2: Create PO Receipt. PO Receipt 10944 is created in fusion at 10:46 AM, during that time integration was deactivated.

Step3: Re-activate orchestration after some time.

Integration was activated at 1PM and we see that the Integration subscribed to the business event for the specific Receipt (10944)

Conclusion: Business events are retried in SaaS and automatically captured within 6 hours of unavailability of OIC services.

Restoring Exhausted Business Events

Step1: Deactivate Integration.

Integration is deactivated for one day. This integration subscribes to “PurchaseOrder” Business event.


Step2: Run API to find exhausted Business Events:

Total number of Exhausted Business event count in last 24 hours is 68.

API URL: <fusion url>/soa-infra/PublicEvent/diagnostic/exhaustedEventsDetail?lastHours=24&pageSize=100

/soa-infra/PublicEvent/diagnostic/exhaustedEventsCount?lastHours=24&pageSize=100

Note: We can find exhausted events for a specific Business Event like “PurchaseOrder” using Subscription ID. We have 43 requests which are exhausted in the last 24 hours. We have filtered out these using SubscriptionID.

 Step3: Activate the integration and restore the Exhausted event for “PurchaseOrder” using APIs

API URI: /soa-infra/PublicEvent/exhaustedEvents/restore

Sample Payload:

{

"subscriptionId": "(*****-***-***-**********-hy.integration.ap-hyderabad-1.ocp.oraclecloud.com):aHR0cHM6Ly9zb21pYy1vaWMtZGV2LWF4bGc4Ymlta2ZuZC1oeS5pbnRlZ3JhdGlvbi5hcC1oeWRlcmFiYWQtMS5vY3Aub3JhY2xlY2xvdWQuY29tL2ljL3dzL2ludGVncmF0aW9uL3YxL2Zsb3dzL2VycC9QT19FVkVOVC8xLjAv",

//"startDate": "29-04-2025 04:03:24",

//"endDate": "29-04-2025 10:20:25",

"lastHours":24

}

After restoring, check the count of Exhausted business event for “PurchaseOrder” is now 0

All 43 records have been subscribed through integration.

Conclusion: Restoring exhaust events is feasible via provided Oracle APIs

Poc document link:

https://docs.google.com/document/d/1E4KYFsKJhDrEEvYo9wQcHjZQraunWKgQ/edit?usp=drivesdk&ouid=105651791254983245041&rtpof=true&sd=true

Reference Document: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=13339287498551&id=2751325.1&_afrWindowMode=0&_adf.ctrl-state=ccb6zmdcu_4



Featured Post

OIC - Split Semicolon-Separated Values Using tokenize() in XSLT

Introduction While developing integrations in Oracle Integration Cloud (OIC), we may receive a field containing multiple values separated by...