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.