Saturday, May 26, 2012

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.

12 comments:

Mark Lancaster said...

Hi Morten

Just wanted to say, that your MS Exchange API is a very interesting idea. And also, very generous of you to put into the public arena as open source.

Mark

Anonymous said...

Hello Morten,

Have you resolved the create id XML parsing issue.

This is really really useful API .

Thanks

Jan S.

Unknown said...

Hi, great kit. Do you know if you can get this to work with Office365 and an email as an account (no domain) Can't seem to authenticate with just an email and password using this.

Thanks,

Pete

Morten Braten said...

@Pete: I don't have access to Office365 myself, so I've never tried it, but according to this blog post "Exchange Online uses basic authentication, and chances are your client is expecting to use NTLM authentication":

http://blogs.msdn.com/b/exchangedev/archive/2013/06/28/authenticate-your-exchange-client-in-office-365.aspx

If that is the case, then the solution would involve modifying the ms_ews_util_pkg.make_request function (remove the use of ntlm_http_pkg and simply pass a basic authentiation username and password to the flex_ws_api.make_request function instead).

- Morten

Ligon said...

Morten, mate you are a multi-talented genius!! just overwhelmed with the stuff you been developing!

< troll >
I wonder, if you create a framework that runs on top of, or with APEX which has:
1. Seamless integration with MS-Office, Exchange, Active Directory, etc
2. Ability to integrate with open office
3. Ability to integrate with Google docs, google maps
4. Ability to have kerberos SSO over Thor Gateway or Oracle REST Data Services

then you just beaten Sharepoint with pure PL/SQL awesomeness!!

Oracle will sure, buy it from you for big $$$ and kill it, coz it will stop their Java stuff moving :)

If not MS will buy it out from you and kill it twice :)

< / troll>

Cheers

Jing said...

Is it possible to make the Oracle Wallet optional? My company's database doesn't use Oracle Wallet.

Morten Braten said...

@Jing: You need a Wallet to use SSL (HTTPS) from the database.

You could set up a proxy that accepts HTTP requests from the database and forwards them to the Exchange server via HTTPS, but obviously that would leave part of the communication unencrypted.

- Morten

Anonymous said...

First of all thank you for this library.

My name si Marco.

I'm trying to use your library over an office 365 enterprise cloud environment.

All this is runnin behind corporate web proxy (windows credentials required).

I tried applying your suggestions to make it working with o365.
I faced different problems, no luck for me.

My doubts are a out:

- service URL
- proxy
- username format
- authentication communication ( NTLM vs simple)

Any help by someone?

Many thanks,
Marco

Scott Horowitz said...

It works pretty nice.. found a bug in the calendar item (there was an uninitialized item on the calendar call for p_required_attendees), but also, every calendar appointment I add is 4 hours earlier than I want it.

Any suggestions?

Scott Wesley said...

Great tool, and I've been looking through known issues on git (#22) regarding the missing item_id. This still appears to be a problem, which affects me trying to form attachments.
Can we generate & supply/seed this ID, rather than wait for it to be returned?
Or is there an adjustment on this topic I've missed?

Maxie said...

Hi,
Any updates from anyone on O365 use?

I've been using the packages against our local Exchange server for some years, but now the organisation has moved to O365 and, whilst I saw the post to Pete from way back and am trying to follow the instructions both HTTP and O365 are fighting me all the way.

Cheers, Max.

Unknown said...

Hey Max,

I've had your post marked to follow up to as soon as my blog went live but I recently did some work to integrate with O365 via the new MS Graph REST API in PL/SQL (for an APEX application to use). You can read about it here: http://bit.ly/ms_graph_util_pkg

Cheers, Paul