Integrating APEX with Outlook via Exchange Web Services (EWS)

Here at Explorer our employee holiday booking and approval system is (unsurprisingly!) an APEX application. As well as this though, we also maintain a calendar in outlook which after booking and gaining approval for leave in the APEX application it was the employees responsibility to manually create an entry in the outlook calendar too. Manual processes like this are prone to not being followed properly so I was tasked with investigating if it would be possible to integrate our APEX application with outlook so when a holiday entry was booked (and approved) it also created an entry in an outlook calendar.

Exchange Web Services

Quite quickly I was able to establish that there is an Exchange Web Services available from Microsoft as part of an exchange server installation. As described here:

“Exchange Web Services (EWS) provides the functionality to enable client applications to communicate with the Exchange server. EWS provides access to much of the same data that is made available through Microsoft Office Outlook. EWS clients can integrate Outlook data into Line-of-Business (LOB) applications. SOAP provides the messaging framework for messages sent between the client application and the Exchange server. The SOAP messages are sent by HTTP.”

So in other words, it is possible to communicate with the Exchange web services by using SOAP messaging, something which the APEX_WEB_SERVICE package available as standard in APEX is certainly capable of doing.

Setting up the database to communicate with the Exchange Web Services

Before being able to communicate with any external services there are a couple of things which need setting up in the oracle database.
The first is the Access Control List, you will need to create and assign an access control list for the database user who will be connecting to the domain where your exchange web services is hosted.
As described in the UTL_HTTP reference which APEX_WEB_SERVICE calls, because the communication with EWS is secured over https, it requires the Oracle Wallet Manager to be set up on our database and the certificate installed. There is a good guide to completing that task available here.

Coding and almost re-inventing the wheel

After working with one of our DBAs to get the ACL, Oracle Wallet and certificate set up and installed it was then time to start the code needed to send the (secure) request which involved building the SOAP envelope to send to exchange, sending the request and processing the response.
I was anticipating having to write a fair amount of code at this point but then quite early on in the investigation and development I found the alexandria-plsql-utils collection of PL/SQL packages by Morten Braten. Morten has done some brilliant work here and in particular has already worked on a package (ms_ews_util_pkg) for building the SOAP messages and then using his own implementation of APEX_WEB_SERVICE (flex_ws_api) to make the requests.
Almost everything you can think of to do with exchange is possible with the package but for our application the elements needed were the ability to create and delete calendar entries in outlook.
I created a copy of the pl/sql package containing the elements I needed:

• t_item type record to store the details of the appointment which I will populate from a page process with the details of the holiday (start, end, description etc.)
• create_calendar_item function for creating the SOAP message and calling make_request which returns the item_id (unique reference in Exchange) which I will store against the Holiday booking
• delete_item procedure which takes the unique item_id and deletes the calendar entry in outlook.

Customisations required for our version of exchange and setting the time zone

After consulting with the exchange web services reference manual as mentioned above I needed to customise the SOAP message that was to be sent to EWS. In the create_calendar_item SOAP message I added the following:

We also wanted the ability to set the outlook ‘category’ so I added the following function and then call get_categories in the correct place when creating the SOAP message. The categories are passed in to the create_calendar_item function as a string array.

In the end this is what an example SOAP message looked like after it had been constructed in PL/SQL:

Calling the functions from APEX

Now that we have our package for creating and removing calendar entries in exchange the rest is standard APEX. I created a new page process inside our application that is called when a holiday is approved.

The process populates the item with the details of the calendar entry (Subject, Category, Start date, end date) and then calls the create_calendar_item function retrieving the item_id. I then update the holiday entry row with this item_id so that it is stored in case the holiday gets cancelled later (then we call the delete procedure passing this id).

So now we can book (and approve) a holiday in our APEX application:

And it appears in outlook:

Summary

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.

Links

• Exchange Web Services (EWS)
• Access Control List
• UTL_HTTP
• Oracle Wallet
• Using UTL_HTTP and SSL
• alexandria-plsql-utils

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.