OIC Gen3 - How To Extend Oracle Integration Cloud Gen3 Accelaretor Project

Oracle Integration 3 is a fully managed, preconfigured environment that gives you the power to integrate your cloud and on-premises applications also known as OIC.

In OIC Gen3, you can extend (customize) an integration in an accelerator project by adding and configuring an extension group. An extension group enables you to extend your integration by adding the following actions to the integrations in your accelerator project.

1. Invoke connections; 
2. Data stitch, 
3. for-each, 
4. switch, 
5. map
6. integration actions; and global variables 

More details on PDF:

https://drive.google.com/file/d/10MiKZx6l1Afjdb8hz7CWdJhaKVx9lda1/view?usp=drivesdk

Reference:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=222723139129443&parent=EXTERNAL_SEARCH&sourceId=REFERENCE&id=3017378.1&_afrWindowMode=0&_adf.ctrl-state=ml7w8ezmi_4

OIC - How to List Files from Object Storage in OIC Without Using Wildcard Patterns

Use Case:

In OIC, you need to list files from Object Storage. However, the Object Storage adapter does not support wildcard patterns (e.g., *.csv, *.txt) in the file name field. You still need to retrieve and process a group of related files dynamically.

---

Solution Steps:

1. Get Prefix from Lookup: Store and retrieve the required fixed prefix from a Lookup table (e.g., invoice_, report_2025-04-), making the integration dynamic and configurable.
2. List Files Using Prefix: Configure the Object Storage adapter to list files using this fixed prefix. This will return all files that start with the given prefix.
3. Pass Each File to Main Integration: Use a For-Each loop to iterate through the list of files, and call the main integration for each file with the filename as a parameter.
4. Optional Filtering in Logic: If needed, apply additional filtering inside the loop based on extension, date, or naming pattern using simple conditions.

Detailed screenshots:

Integration flow

Configure object storage invoke adapter and add prefix query parameter.

Map the prefix, fetched from lookup or a combination of lookup and dynamic data.

OIC - How to Send Email Using Twilio SendGrid API in Oracle Integration Cloud (OIC)

How to Send Email Using Twilio SendGrid API in Oracle Integration Cloud (OIC)

Use Case

You want to send transactional or notification emails from an OIC integration using SendGrid, a cloud-based email service by Twilio. This method is REST-based, reliable, and gives full control over the email content and delivery.

Why we should opt for Twilio sendgrid over traditional OIC built-in SMTP mail.

• Rich HTML & Attachments: SendGrid supports advanced HTML emails, attachments, and templates; OIC SMTP is basic.
• Email Tracking: SendGrid gives open/click stats; OIC SMTP has no tracking.
• High Deliverability: SendGrid ensures better inbox delivery; SMTP can be throttled or blocked.
• Secure Auth: SendGrid uses API keys; SMTP often uses plain credentials.
• Scalability: SendGrid handles bulk emails; SMTP suits low volume.
• Use SMTP for simple, internal alerts.
• Use SendGrid for professional, scalable, and trackable emails.

Solution Steps

1. Get Your SendGrid API Key

• Log in to SendGrid Dashboard.
• Go to Settings > API Keys > Create API Key.
• Choose “Full Access” or customize scopes as needed.
• Copy and save the API key securely.

2. Create a REST Connection in OIC for SendGrid

1. Go to Connections > Create in OIC.
2. Choose REST Adapter.
3. Enter a name like SendGrid_REST_API.
4. Set Connection Type: Trigger and Invoke.
5. In Connection Properties:
   • Configure URL: https://api.sendgrid.com
6. Under Security:
   • Security Policy: API key based authentication
   • Provide api key
7. Test and save the connection.

3. Use the Connection in Your Integration

• Create an App-Driven or Scheduled Integration.
• Drag the SendGrid_REST_API as an Invoke.
• Choose operation: POST
• Resource Path: /v3/mail/send
• Request Media Type: application/json or text/html

4. Map Your Email Payload

Sample JSON:

{
  "personalizations": [
    {
      "to": [
        {
          "email": "test@test1.com,test1@test2.com",
          "name": "Test"
        }
      ],
      "cc": [
        {
          "email": "test@test.com",
          "name": "test"
        }
      ]
    }
  ],
  "from": {
    "email": "test@test.com",
    "name": "Example Order Confirmation"
  },
  "subject": "Your Example Order Confirmation",
  "content": [
    {
      "type": "text/html",
      "value": "

<p>Hello from Twilio SendGrid!</p>

<p>Sending with the email service trusted by developers and marketers for

 <strong>time-savings</strong>, 

<strong>scalability</strong>, 

and <strong>delivery expertise</strong>.

</p><p>%open-track%</p>"
    }
  ],
  "attachments": [
    {
      "content": "Base64 content",
      "filename": "index.html",
      "type": "text/html",
      "disposition": "attachment"
    }
  ],
  "categories": ["cake", "pie", "baking"]
}

Map values dynamically if needed using XSLT mapper.

5. Test the Integration

• Activate and run the integration.
• Check your SendGrid dashboard for delivery status.

Detailed screenshots:

Create sendgrid rest Connection:

Create an app driven common integration:

Send grid rest api adapter configuration:

Configure trigger. We can use same above payload and customize as our need:

This common SendGrid email service can then be reused across other integrations wherever there's a requirement to send error or notification emails.

In our integration, if no files are found while listing from the SFTP location, an error is thrown and caught by the global fault handler. From there, we invoke a common SendGrid email service to send the error details via email.

Mapping:

We can keep all the information in a lookup and fetch them during mapping.

Reference:

https://www.twilio.com/docs/sendgrid/api-reference/mail-send/mail-send

OIC - Converting standard DateTime to Unix Time in OIC Using XSLT functions

Use Case

In Oracle Integration Cloud (OIC), there are scenarios where we need to convert a standard DateTime value into Unix time (Epoch time), which represents the number of seconds elapsed since January 1, 1970, UTC. This is commonly required when integrating with APIs that accept timestamps in Unix format.

Solution Steps

To achieve this conversion, we use XPath expressions within OIC, leveraging xsd:dateTime, xp20:format-dateTime, fn:current-dateTime(), and xsd:dayTimeDuration.

Expression Used

floor (((xsd:dateTime (xp20:format-dateTime (fn:current-dateTime(), 

"[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z" ) ) 
- xsd:dateTime ("1970-01-01T00:00:00Z" )) 

div xsd:dayTimeDuration ("PT1S") ))

Breakdown of the Expression

1. fn:current-dateTime() → Retrieves the current DateTime in the integration.
2. xp20:format-dateTime(..., "[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z") → Formats it to a standard UTC DateTime format.
3. xsd:dateTime(...) - xsd:dateTime("1970-01-01T00:00:00Z") → Calculates the difference between the given DateTime and the Unix epoch (January 1, 1970).
4. div xsd:dayTimeDuration ("PT1S") → Converts the duration to seconds.
5. floor(...) → Ensures the result is an integer by rounding down.

Example Output

If the current DateTime is 2025-04-04T12:30:45Z, the XPath expression will return the Unix timestamp 1743772245.

Types of encryption techniques

There are several types of encryption techniques used to secure data, each with its own use case and strengths:

1. Symmetric Encryption

Uses a single key for both encryption and decryption.

• Examples: AES (Advanced Encryption Standard), DES (Data Encryption Standard), 3DES (Triple DES), Blowfish, Twofish

2. Asymmetric Encryption

Uses a pair of public and private keys for encryption and decryption.

• Examples: RSA (Rivest-Shamir-Adleman), ECC (Elliptic Curve Cryptography), Diffie-Hellman, ElGamal

3. Hashing

Transforms data into a fixed-length hash that cannot be reversed.

• Examples: SHA (Secure Hash Algorithm), MD5 (Message Digest Algorithm), bcrypt, Argon2

4. End-to-End Encryption (E2EE)

Ensures data remains encrypted throughout transmission and can only be decrypted by the intended recipient.

• Examples: Signal Protocol, PGP (Pretty Good Privacy), TLS (Transport Layer Security)

5. Homomorphic Encryption

Allows computation on encrypted data without decrypting it first.

• Examples: Paillier Cryptosystem, BGV (Brakerski-Gentry-Vaikuntanathan), CKKS (Cheon-Kim-Kim-Song)

6. Quantum Encryption

Leverages quantum mechanics for ultra-secure encryption.

• Examples: Quantum Key Distribution (QKD), BB84 Protocol

Each encryption type serves a specific purpose, from securing online communication to protecting stored data.

OIC - AES encryption and decryption in javascript using CryptoJS

Use Case

A source application sends an AES-encrypted and RSA-signed request to an OIC REST service, including a cipher key, encrypted message, and IV. OIC retrieves the AES key from Vault secrets, decrypts the data using JavaScript, processes it, then encrypts and signs the response before sending it back.

We use JavaScript (CryptoJS) to encrypt and decrypt messages with IV and fetch the AES key from Vault. For signing and verification, we use the Vault RSA sign key.

In today's digital world, encryption is essential for data security. This blog explains how to use CryptoJS for AES-256 encryption/decryption and Vault for secure key management and RSA signing.

Steps to follow:

1. Download the cryptojs file from https://github.com/ihsmarkitosi/CryptoJS-v3.1.2/blob/master/rollups/aes.js
2. Use the below mentioned encrypt and decrypt code snippet.
3. First place step1 cryptojs code and then add step2 code. Test it in Online tool to test javascript: https://onecompiler.com/javascript

Encrypt and decrypt code snippet:

function EncryptAESCBC(plaintext, aesKey, aesIV) {

    var key = CryptoJS.enc.Utf8.parse(aesKey);

    var iv = CryptoJS.enc.Utf8.parse(aesIV);

    // Encrypt the plaintext

    var cipherText = CryptoJS.AES.encrypt(plaintext, key, {

        iv: iv,

        mode: CryptoJS.mode.CBC

        //padding: CryptoJS.pad.NoPadding

    });

    var encryptedString = cipherText.toString();

    return encryptedString;

}

function DecryptAESCBC(cipherText, aesKey, aesIV) {

    // IV is a base64 string

    var key = CryptoJS.enc.Utf8.parse(aesKey);

    var iv = CryptoJS.enc.Utf8.parse(aesIV);

    var cipherBytes = CryptoJS.enc.Base64.parse(cipherText);

    var decrypted = CryptoJS.AES.decrypt({ciphertext: cipherBytes}, key, {

        iv: iv,

        mode: CryptoJS.mode.CBC

        //padding: CryptoJS.pad.Pkcs7

    });

    var decryptedString = decrypted.toString(CryptoJS.enc.Utf8);

    return decryptedString;

}

//console.log(DecryptAESCBC(EncryptAESCBC('YWFhYWFhYWFhYWFhYWFhYQ', 'h3Hv332dw8JYJcdx', 'aDNIdjMzMmR3OEpZSmNkeA=='), 'h3Hv332dw8JYJcdx', 'aDNIdjMzMmR3OEpZSmNkeA=='));

Screenshot:

Note: you can also take below updated google cryptojs (rollups /aes.js file)

https://code.google.com/archive/p/crypto-js/downloads

Used in OIC Integrations:

Encryption common service:

Integration flow:

Configure trigger

Get AES Key from vault secret

Call javascript action

Call sign service for RSA vault

Decryption common service

Integration flow

Configure trigger

Verify service

Throw new fault if varification failed.

Get AES Key from vault secret

Call Javascript

Connection details: