It was almost 4 years ago that I wrote about Integrating APEX with Outlook via Exchange Web Services (EWS) for our internal applications at Explorer in order to add and remove calendar entries based on the information entered in the application. Not a huge amount has changed with the integration in that time and it has been very stable. The only additions made since the blog is that the same integration is used by other areas of the application now and we have added the ability to update calendar items (rather than delete and re-add) which involved obtaining a changekey and sending an updated calendar item to EWS in order for the entry to update.
The time had come, however, for a potentially big change, which was triggered by us moving from a local exchange server to Office 365. After doing some research, I established that EWS was still available to communicate with Office 365. However, as of 13th October 2020, basic authentication (which the library used utilised) was being retired. This meant that the changeover wasn’t going to be as straightforward as first thought.
During my research, I established that OAuth authentication was possible with EWS so my initial thoughts were that I would enhance the library we already utilised in order to use a more appropriate (especially as we would going over the WWW) type of authentication, which would be supported going forwards.
During the research, I also established that Microsoft now has a newer (RESTful) API available called Microsoft Graph. It too has the capability to talk to Office 365 (amongst many other things).
EWS is a SOAP API so communication is done with XML messages which are a little bit ‘old hat’ now. The vast majority of APIs in my recent experience are either being created as RESTful Web Services or moving to them from SOAP. Microsoft Graph does have some known issues and after some research, I discovered people were frequently complaining on forums it didn’t support something that EWS does. In addition, as far as I could tell, I couldn’t find anyone who had integrated with it using PL/SQL so I would be starting from a relatively blank slate.
As Microsoft Graph is newer, I expect that the known issues will be fixed and it will be Microsoft’s focus going forwards so is likely to support many newer things in the future, and who knows, EWS may eventually be retired.
My mind was made up. Let us get truly up to date when we do this and move the communication to talk to Office 365 over the newer RESTful Web service.
“Microsoft Graph is the gateway to data and intelligence in Microsoft 365. It provides a unified programmability model that you can use to access the tremendous amount of data in Microsoft 365, Windows 10, and Enterprise Mobility + Security. Use the wealth of data in Microsoft Graph to build apps for organizations and consumers that interact with millions of users.”
To get started you will need to register your application in Azure for the tenancy you will be communicating with in order to generate and obtain your Client ID and Client Secret. We are going be communicating with it using OAuth2 token authentication, which involves authenticating, obtaining a time limited token and then communicating with that token. You will also need to grant permissions to the application (I needed Calendars.ReadWrite.Shared)
I usually start a new integration with a RESTful web service by testing it in Postman. There are already some example calls in a library supplied by Microsoft, which is available here.
Using the library, I was able to authenticate and obtain a token, which I then used to successfully call the following request types:
Now it was time to start writing the package which would be used to create and process the requests from the Oracle Database. As per my previous article on EWS, you will need to set up and configure your Access Control List (ACL) and add Microsoft’s certificates to your Oracle Wallet first.
I wanted the package to be as similar to use as possible to the ms_ews_util_pkg package previously used the from alexandria-plsql-utils library so the package written follows a similar structure.
There is a type to represent an event resource (this was known as an ‘item’ in EWS) and I needed to expand the initialisation procedure for the new parameters we would need. We needed a new function to get the user access token for the authentication. Making a request needed to be changed to use APEX_WEB_SERVICE.MAKE_REST_REQUEST and the methods for creating and updating an event needed to be changed to construct the JSON body for the request (instead of XML).
In the end, this is what an example JSON body message for a create event looked like after it had been constructed by the package:
The package written (ms_graph_util_pkg) is available on github here. Included in the repository is a demo script containing example calls to the library.
Currently, only the requests talked about exist in the package. The event resource also isn’t fully represented but I hope in the future (perhaps with contribution from others) this can be expanded to support all event properties and also that further API requests (such as mail) can be added. The API is vast and I imagine it will be feasible to communicate with most things available in the 365 world.
Now everything was in place, the final step was to make updates to the APEX application.
Where there was a page process or dynamic action for maintaining a booking in outlook, I started by duplicating them (and also conditioned them so which integration that is used can be controlled). Then I updated the processes to set the correct properties on the new event object type and then call the procedures and functions in the new ms_graph_util_pkg package.
So, after booking a holiday for a test user, the unique event ID was returned by the create function (to store with the holiday booking for future changes) and the event was successfully created in the 365 calendar:
The summary is the same as the previous blog:
“More and more services and applications have a need to communicate with each other and if those applications or services have a RESTful API or SOAP then it is easy to achieve this communication from within an APEX application.”
As I mentioned in the article, I hope to expand the new library in the future to fully represent everything on the event object and add new functions/procedures for other requests available in the Microsoft Graph API.
Hopefully, this new integration will last at least another 4 years but if we do make any significant changes or expansions in the future, I will be sure to update you!
Author: Paul Jones
Job Title: Oracle APEX Development Consultant
Bio: Paul is a Development Consultant at DSP-Explorer. Paul has a BSc in Computing from the University of Leeds and is now building on considerable experience in development and support using Oracle PL/SQL and Oracle E-Business Suite (EBS). Paul is employing APEX to provide quality, bespoke software solutions both internally and to a range of organisations.
