PL/SQL and Microsoft technology

Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).

Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.

Over the last couple of years, I've written a number of blog posts on this topic. This post is just a convenient collection of links to these previous posts:


PL/SQL and NTLM

• An implementation of the NTLM protocol for PL/SQL, which you can use to call web services on servers with Integrated Windows Authentication
• NTLM Single Sign-On in Oracle Application Express (Apex), how to set up your Apex applications so that users will automatically be logged in without having to enter their usernames and passwords, useful for intranet sites

Oracle Data Provider for .NET (ODP.NET)

• Minimal, non-intrusive install of ODP.NET, or how to make ODP.NET "just work" without interfering with other software on the machine, with the smallest possible footprint

Oracle Application Express (Apex) and IIS

• Using the Thoth Gateway to run Apex on Microsoft Internet Information Server (IIS), a freely available, easy-to-install, high-performance port of mod_plsql that runs on Microsoft's web server, including IIS 6 and IIS 7
• Using Oracle Proxy Authentication with the Thoth Gateway, or how to pass your Windows credentials from the the web server on to the database server

Microsoft Office and Office Open XML (OOXML)

• PL/SQL package for working with the OOXML file format, or how to read and write OOXML documents (Word, Excel, Powerpoint) from PL/SQL

Microsoft Exchange (Email, Calendar and Contacts)

• PL/SQL package for working with Microsoft Exchange, or how to read and write email, attachments, calendar events and contact information from PL/SQL

Microsoft Sharepoint

• I have not yet had the need (or the desire...) to work with Sharepoint from PL/SQL, but it would be quite easy to create a PL/SQL library package to interface with the Sharepoint web services, similar to the Exchange package listed above. Perhaps I will revisit this topic sometime in the future... ? 

MS Exchange API for PL/SQL

As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!

Features

Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.

Prerequisites

• You need access to an Exchange server, obviously! I've done my testing against an Exchange 2010 server, but should also work against Exchange 2007.
• If the Exchange server uses HTTPS, then you need to add the server's SSL certificate to an Oracle Wallet. See this page for step-by-step instructions.

Usage

The API should be fairly self-explanatory. The only thing you need to do is to call the INIT procedure at least once per session (but remember that in Apex, each page view is a new session, so place the initialization code in a before-header process or a page 0 process).


begin
  ms_ews_util_pkg.init('https://thycompany.com/ews/Exchange.asmx', 'domain\user.name', 'your_password', 'file:c:\path\to\Oracle\wallet\folder\on\db\server', 'wallet_password');
end;

Note also that there are two varieties of most functions: One pipelined function intended for use from plain SQL, and a twin function suffixed with "AS_LIST" that returns a list intended for use with PL/SQL code.

Code Examples

-- resolve names


declare
  l_names ms_ews_util_pkg.t_resolution_list;
begin
  debug_pkg.debug_on;
  l_names := ms_ews_util_pkg.resolve_names_as_list('john');
  for i in 1 .. l_names.count loop
    debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).mailbox.name, l_names(i).mailbox.email_address);
  end loop;
end;




-- resolve names (via SQL)


select *
from table(ms_ews_util_pkg.resolve_names ('john'))




-- expand distribution list


declare
  l_names ms_ews_util_pkg.t_dl_expansion_list;
begin
  debug_pkg.debug_on;
  l_names := ms_ews_util_pkg.expand_public_dl_as_list('some_mailing_list@your.company');
  for i in 1 .. l_names.count loop
    debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).name, l_names(i).email_address);
  end loop;
end;




-- get folder


declare
  l_folder ms_ews_util_pkg.t_folder;
begin
  debug_pkg.debug_on;
  l_folder := ms_ews_util_pkg.get_folder (ms_ews_util_pkg.g_folder_id_inbox);
  debug_pkg.printf('folder id = %1, display name = %2', l_folder.folder_id, l_folder.display_name);
  debug_pkg.printf('total count = %1', l_folder.total_count);
  debug_pkg.printf('child folder count = %1', l_folder.child_folder_count);
  debug_pkg.printf('unread count = %1', l_folder.unread_count);
end;




-- find up to 3 items in specified folder


declare
  l_items ms_ews_util_pkg.t_item_list;
begin
  debug_pkg.debug_on;
  l_items := ms_ews_util_pkg.find_items_as_list('inbox', p_max_rows => 3);
  for i in 1 .. l_items.count loop
    debug_pkg.printf('item %1, subject = %2', i, l_items(i).subject);
  end loop;
end;




-- get items in predefined folder


select *
from table(ms_ews_util_pkg.find_folders('inbox'))




-- get items in predefined folder, and search subject


select *
from table(ms_ews_util_pkg.find_items('inbox', 'the search term'))




-- get items in user-defined folder


select *
from table(ms_ews_util_pkg.find_items('the_folder_id'))




-- get items in user-defined folder, by name


select *
from table(ms_ews_util_pkg.find_items(
             ms_ews_util_pkg.get_folder_id_by_name('Some Folder Name', 'inbox')
            )
          )




-- get item (email message)


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item := ms_ews_util_pkg.get_item ('the_item_id', p_include_mime_content => true);
  debug_pkg.printf('item %1, subject = %2', l_item.item_id, l_item.subject);
  debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
  debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
end;




-- get item (calendar item)


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item := ms_ews_util_pkg.get_item ('the_item_id', p_body_type => 'Text', p_include_mime_content => true);
  debug_pkg.printf('item %1, class = %2, subject = %3', l_item.item_id, l_item.item_class, l_item.subject);
  debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
  debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
  debug_pkg.printf('start date = %1, location = %2, organizer = %3', l_item.start_date, l_item.location, l_item.organizer_mailbox_name);
end;




-- create calendar item


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item.subject := 'Appointment added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.start_date := sysdate + 1;
  l_item.end_date := sysdate + 2;
  l_item.item_id := ms_ews_util_pkg.create_calendar_item (l_item);
  debug_pkg.printf('created item with id = %1', l_item.item_id);
end;




-- create task item


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item.subject := 'Task added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.due_date := sysdate + 1;
  l_item.status := ms_ews_util_pkg.g_task_status_in_progress;
  l_item.item_id := ms_ews_util_pkg.create_task_item (l_item);
  debug_pkg.printf('created item with id = %1', l_item.item_id);
end;




-- create message item


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item.subject := 'Message added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.item_id := ms_ews_util_pkg.create_message_item (l_item, p_to_recipients => t_str_array('recipient1@some.company', 'recipient2@another.company'));
  debug_pkg.printf('created item with id = %1', l_item.item_id);
end;




-- update item
-- item id and change key can be retrieved with following query:
-- select item_id, change_key, subject, is_read from table(ms_ews_util_pkg.find_items('inbox'))


declare
  l_item_id varchar2(2000) := 'the_item_id';
  l_change_key varchar2(2000) := 'the_change_key';
begin
  ms_ews_util_pkg.update_item_is_read (l_item_id, l_change_key, p_is_read => true);
end;




-- get list of attachments


select *
from table(ms_ews_util_pkg.get_file_attachments('the_item_id'))




-- download and save 1 attachment


declare
  l_attachment ms_ews_util_pkg.t_file_attachment;
begin
  debug_pkg.debug_on;
  l_attachment := ms_ews_util_pkg.get_file_attachment ('the_attachment_id');
  file_util_pkg.save_blob_to_file('DEVTEST_TEMP_DIR', l_attachment.name, l_attachment.content);
end;




-- create attachment (attach file to existing item/email)


declare
  l_attachment ms_ews_util_pkg.t_file_attachment;
begin
  debug_pkg.debug_on;
  l_attachment.item_id := 'the_item_id';
  l_attachment.name := 'Attachment added via PL/SQL';
  l_attachment.content := file_util_pkg.get_blob_from_file('DEVTEST_TEMP_DIR', 'some_file_such_as_a_nice_picture.jpg');
  l_attachment.attachment_id := ms_ews_util_pkg.create_file_attachment (l_attachment);
  debug_pkg.printf('created attachment with id = %1', l_attachment.attachment_id);
end;

Download

The MS_EWS_UTIL_PKG package is included in the Alexandria Utility Library for PL/SQL.

Known Issues

The CREATE_ITEM functions don't seem to return the ID of the created item (which do get created in Exchange). I think the issue is with the XML parsing of the returned results; I will look into this later.

Please report any bugs found via the issue list.

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

• Exchange Web Services Reference
• Simple example in VBScript, how to list contents of your inbox using EWS

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).