Office 365 Email Integration using Oracle APEX REST Data Source & Synchronization – Zero (Code) Integration

A blog after many years, thanks to @Jaydip Bosamiya for encouraging me to resume blogging. Recently one of our client wanted to implement Office 365 email integration within Oracle APEX application.

First we started PoC by reviewing Microsoft Graph API, we downloaded Postman Collection, found useful parameters to Sort, Filter and Select columns within Microsoft Graph API and then completed integration using PL/SQL, Oracle APEX Automation, Web Credentials and APEX_WEB_SERVICE.make_rest_request within 2 days.

Once we implemented the PL/SQL code, Jaydip suggested let us explore Oracle APEX REST Data Source and our goal was to implement the Office 365 Email Integration with Oracle APEX REST Data Source with minimum code. As we had already completed the integration earlier using PL/SQL code, we knew all the important aspects and we completed the entire integration within 2 hours 🙂

There are plenty of good articles on internet for Microsoft Graph API and APEX code so I am not going to repeat them again.

You can review them as below:

• https://learn.microsoft.com/en-us/graph/overview
• https://learn.microsoft.com/en-us/graph/connecting-external-content-connectors-api-postman
• https://learn.microsoft.com/en-us/graph/query-parameters
• https://learn.microsoft.com/en-us/graph/filter-query-parameter

We noticed that as there was no access policy we were able to read emails of all the accounts. After doing more research we found following 2 articles useful.

• https://learn.microsoft.com/en-us/graph/auth-limit-mailbox-access
• https://blog.cloudnueva.com/apex-msgraph-introduction (Thanks @Jontahn Dixon)

This was the background, now coming to important points:

Configure Oracle APEX REST Data Source

• Go to your application
• Shared Components

Create REST Data Source

• Data Sources -> Click REST Data Sources -> Click Create -> From Scratch -> Click Next
• Select Simple HTTP as REST Data Source Type
• Enter Data Source Name and URL Endpoint (Replace {{UserID}} value)
• Leave HTTPS Host Name empty (This will be useful when OAuth2 token is generated via Web Credentials)

Create REST Data Source – Remote Server

• Under Remote Server select – Create New – or choose existing Remote Server
• Enter Base URL and Service URL Path as below (Replace {{UserID}} value) -> Click Next
• Select Pagination Type -> No Pagination

Authentication

• Select Authentication Required
• Select – Enter new credentials –
• Select Authentication Type -> OAuth2 Client Credentials Flow
• Enter OAuth Token URL (Replace {{TenantID}} value), Client ID and Client Secret

• Once the Data Source is created -> Edit the same Data Source
• You can remove unused POST, PUT and DELETE Operations
• Add parameters for the API as per the requirements. We have used $filter, $orderby and $select

• Click on Edit Data Profile
• Enter “value” as Row Selector
• Under Columns -> Select all columns and Delete Rows
• Click Apply Changes and your Data Profile should be blank

• Edit Data Profile again
• Click on Rediscover Data Profile

• If your configuration is valid then you should get a response similar to following
• Click on Replace Data Profile
• Click Apply Changes

• You should see Data Profile region similar to below based on $select columns in parameter

• Click on Edit Data Profile again and assign ID column as Primary Key and change the datatype and length of other columns as per the requirements.
• Now we can configure REST Synchronization.

REST Synchronization

• You can click on Manage Synchronization from Right sidebar of recently configured REST Data Source

• Select Parsing Schema
• Select Synchronize to -> New table
• Enter Table Name
• Click on Save

• You will see a Table status as “Synchronization Table does not exist”
• Click on Create Table
• You will see a “Table TABLE_NAME is ready for synchronization”
• Under Details Region
  • Select Synchronization Type -> Merge
  • Edit Synchronization Schedule as per the requirement
• If you have any parameter which are not static such as $filter then you can pass its value using Steps

• Under Advance Settings make sure “Job is active” enabled
• Click “Save” or “Save and Run”
• Wait for scheduler to run if you have selected “Save”
• You should see the data within database table and you can use these emails data within your application.

• You can also view the logs of the REST Synchronization under Logs Region

Conclusion

With this post, we wanted to showcase the real-world use case of Oracle REST Data source and REST Synchronization by automatically reading emails from Office 365 with writing minimum code and out-of-the-box APEX features.