Tuesday, May 1, 2012

Select * from Inbox

... or how to read your (Microsoft Exchange) email using SQL and PL/SQL.

Introduction


You are probably familiar with sending email using PL/SQL; there's the UTL_SMTP package, the APEX_MAIL package, and various other utilities that do this.

But what about reading email using PL/SQL? You need to investigate the POP and IMAP protocols. A quick search on Google turns up some sample code and even a feature-rich package, although the latter requires Java in the database (which I try to avoid whenever possible, and which is not available in Oracle XE in any case).

Approaching this from a different angle, a lot of companies use Microsoft Exchange as their mail server. Exchange has (since at least Exchange 2007) a robust set of web services that expose its features:

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.

This figure from Microsoft shows the steps involved in calling the web service:



So it's basically SOAP (XML) over HTTP. We can easily do that in PL/SQL!

Use Cases

Of course, you can use Exchange Web Services for lots of things besides just reading your email, for example:


  • Integration with other systems via email messages (your PL/SQL program checks email periodically and processes the contents of the email and/or attachments)
  • Searching email archives, combining the search results with search results from the database
  • Checking availability of people for scheduling
  • Adding or updating appointments and tasks
  • Sending emails
  • And lots more

You are welcome to add a comment below if you have more good examples of how the Oracle Database and Microsoft Exchange can work together.

Prerequisites

The building blocks we need to work with Exchange Web Services in PL/SQL are:


  • For building and parsing the SOAP messages we can use the built-in XMLTYPE data type.
  • For calling the web service we can use the APEX_WEB_SERVICE package, or alternatively the FLEX_WS_API package (which is more or less the same package as that included in Apex, but the code is not wrapped, so you can study and modify it).
  • Your Microsoft Exchange server is very likely set up with Integrated Windows Authentication, which means we need to authenticate to the web service using NTLM. I wrote a package last year called NTLM_UTIL_PKG which allows us to do just that.
  • Your Microsoft Exchange server is probably also set up with SSL security, in which case you need to set up an Oracle Wallet with the SSL certificate of the Exchange server. There is a good explanation of that here and here. (By the way, there has been some confusion as to whether you can use Oracle Wallets with Oracle XE, as the Wallet Manager is a feature of the Advanced Security Option not included in XE, but this old thread states that "only the latter [call ins (i.e client to database network encryption)] requires ASO, SSL call outs don't". Since we are doing callouts from the database to Exchange that should be okay then.)

The MS_EWS_UTIL_PKG package


Given the above building blocks, I have started implementing a PL/SQL package that offers an easy-to-use API for working with Exchange Web Services (EWS). The name, unsurprisingly, is MS_EWS_UTIL_PKG.



Currently, the quite-not-yet-ready-for-public-consumption package handles the following operations:


  • Get Folder Id By Name
  • Find Folders
  • Find Items
  • Get Item
  • Get File Attachment
  • Move Item
  • Delete Item


Note that an "Item" in Exchange can be an email item, a calendar entry, a task, and so on. Currently, the package handles email items and calendar entries.

So here's me, checking my inbox from SQL*Plus....



Of course, if you wanted an actual inbox in your database, you could just run "create or replace view my_inbox_v as select * from table (ms_ews_util_pkg.find_items(...) )".

Or create an Interactive Report on top of it in Apex. Or create a Region Plugin for Apex that displays a mail folder. Or create an Apex calendar on top of the calendar entries.

The possibilities are many!

References



Next Steps

When I get this polished enough for an initial public release, I'm going to include it in the Alexandria Utility Library for PL/SQL.

Leave a comment below if you are interested in this package, and if there are any specific parts of the EWS API that you would like to see implemented (and why).

13 comments:

Jeff Kemp said...

Very nice - watching with interest.

Andre said...

Hello Martin,
thank you for introducing this good looking and promising idea.
Although I'm using Carstens package (with only little adoptions) for an quite long period of time the WS-way (and yes also the possibillity to avoid java) looks also good.
Since you asked for the "specific parts" one would like to see implemented:
At least what Carstens package provide should be there - and of cource everything that is possible via the API in addition...
As you said there are a wide range of use cases ...
Thank you and good luck!
Andre

Tim Hall said...

Hi.

Very neat!

Can't say I've got a requirement to use it at the moment, but it's nice to know it exists if I get one in the future.

Cheers

Tim...

Anonymous said...

Very nice,.. I will follow your Blog for more updates on this post.
I'd really love to give it a try...
Looks very promising, so please keep up the good work!!

ronr.blogspot.com said...

Nice post! Will see how I can test this.

Steve said...

Morten,

Rather odd brother...

I had the itch to do this last week...

Scoured the web for anything PL/SQL and found VERY few articles of value.

After deciding to go about it on my own and post to my blog, YOU miraculously decided to do the same thing! Where were you like 2 weeks ago??

Anywho, i've posted my extremely incomplete, but available source code to my blog. I'm sure yours will be much more complete as you're taking longer to complete yours but I just wanted to get mine out there for folks to find NOW. Mostly so they wouldn't have to go through the same thing I did :).

Blog is:
http://techblog.steveandyvonne.com/2012/05/exchange-web-services-ews-using-plsql.html

Morten Braten said...

@Andre: "At least what Carstens package provide should be there - and of cource everything that is possible via the API in addition..."

Okay, that narrows it down a bit, right? :-) Actually, I had a look at the documentation to that package, and it seems you should be able to do most things using the EWS API. I'm aiming for the most common operations in the first release.

@Steve: That is an odd coincidence! Great minds think alike, eh? :-)

- Morten

Anonymous said...

Hello Morten,

Wow ! This is just over the top cool.
I've spend months just going around searching for some sort of EWS integration. Tried the java in database approach ... lets just say I dropped that idea quickly.

And I pretty much just gave up on this ever coming to life.

Really awesome, can't wait to put it to the test.

Jan S.

Anonymous said...

Hello Morten,

I just wanted to add that there are some basics that should included in the package. The good (INSERT,UPDATE,DELETE). In the EWS terms:
[Calendar]
===========
- create item (ie. create appopintment "planned database maintenance")
- update item (ie update the date and time of appointment "planned database maintenance"
- delete item (ie delete the appointment "planned database maintenance")

If you package included this like this that would be awesome.

Jan S.

Kim Berg Hansen said...

Looking forward to this in Alexandria :D

We have at present a dot.njet program using MAPI to read the inbox of a particular Exchange account, extract the email body as plaintext and put it in a clob, and extract attachments and put them in blobs.

It works, but the program has to be restarted like a couple of times a week just because something happens to the MAPI connection.

So it would be very very nice for us to have Exchange access within the database without being dependent on a program on some server somewhere in the middle ;-)

I'll keep an eye on Alexandria and see when you go public.

(Oh, and thanks for the other stuff in Alexandria - definitely some nice things in there :D )

Tim St. Hilaire said...

Love the idea and I am very interested in trying it. We currently work with Perl. I would second the comment on Attachments, and that is one of our goals - to get the attachments to a repository.

Unknown said...

Morten,

This is fantastic, thanks for sharing it.

I have it working successfully for reading my own folders in my mailbox.

I also have access to shared inbox, is it possible for me to traverse the folders in that inbox?

Could you post the code examples please?

Many thanks in advance,
Steve

Morten Braten said...

@Steve: The find_items function takes a p_username parameter to retrieve data from other inboxes. This assumes the user you are authenticating with has been delegated access to the other user.

- Morten