PL/SQL Email Client API

Package MAIL_CLIENT

Author: Carsten Czarski Follow @cczarski

Links

Features at a glance

  • Connect to a mail server using either the IMAP or POP3 protocol
  • Retrieve messages of a mailbox folder as a virtual table (table function)
  • Get message contents even for multipart messages
  • Get structure of a multipart message
  • Delete or modify messages on the server
  • Sample Scripts are contained within the package

Java in the database: other examples


 In the Oracle database there are two PL/SQL APIs to send mails: UTL_SMTP and UTL_MAIL. Application Express (APEX) contains another Package: APEX_MAIL. So sending emails is no problem and frequently done from Oracle database installations. But there is no package which is able to retrieve messages from a mailbox on a server. The here provided package MAIL_CLIENT closes this gap.

Message listing in Oracle SQL*Plus
Message listing in Oracle SQL*Plus

New Features (current version 1.1.5)

  • Added some code to deal with 11.2.0.4 databases
  • Added MAIL_T.GET_RECIPIENTS function
  • GET_MESSAGE_COUNT functions
  • Added Fetch Limits to deal with large mailboxes
  • Added an FAQ
  • Tested on 11.2.0.4 and Oracle12c databases (see FAQ)

New Features (version 1.0)

  • Support for server-side search filters (Package MAIL_FILTER)
  • Get the message "Receive Date"
  • Internal optimizations
Thanks to Andre Meier, Christian Haßlbauer, Thomas Schild and Axel Röber for valuable contributions (Message UID, POP3 Expunge Folder workaround), Mail filters

Technical background

This package utilizes the JVM which is embedded in the database since Oracle8i. Since Oracle10g the standard Java Mail APIis included with this JVM. With the Java Mail API messages can be constructed and sent as well as being retrieved from a mail server (mailclient capability). This package makes the java mail functionality available to SQL and PL/SQL - it fully relies on the given POP3 / IMAP implementation and does not  contain any own POP3 or IMAP code. Therefore it works with all mailservers the Java Mail API works with. If a mailserver does not work with Java Mail it does also not work with this package.

Java has an own security mechanism: The database user which calls the functions and procedured as well as the owner of the objects needs appropriate privileges in order to access the mail server over the network. These privileges can be granted fine-grained using the DBMS_JAVA.GRANT_PERMISSION procedure. A typical call (to be executed with DBA privileges) is as follows:

begin
  dbms_java.grant_permission(
    grantee           => '[dbuser]',
    permission_type   => 'SYS:java.net.SocketPermission',
    permission_name   => '[mailserver name or "*" for the whole network]',
    permission_action => 'connect,resolve'
  );
end;

Permissions might be granted for the whole network, subnets or individual hostnames or IP addresses. Furthermore negative permissions (DBMS_JAVA.RESTRICT_PERMISSION) are possible: A user might be granted privileges for a network but not for a particular server. More information about the security mechanism is contained in the Oracle documentation.

last updated:  December 2nd, 2016