Emails aus einem Postfach abrufen ... wieder mal mit SQL!

English title: Retrieving mails from an IMAP server - again: with SQL!

Emails aus der Datenbank heraus zu versenden, ist keine allzu kompilizierte Aufgabe - es wird tagtäglich mit Hilfe der bekannten PL/SQL-Pakete wie UTL_SMTP oder UTL_MAIL gemacht. Eine interessante Aufgabe wäre dagegen das Abrufen von Emails aus einem Postfach. Wozu kann sowas gut sein - man könnte bspw. Prozesse durch Emails antriggern - die Datenbank holt die Mails ab und starten dann PL/SQL-Logik ...

It is no problem to send emails from the database - with the provided PL/SQL packages UTL_SMTP or UTL_MAIL this is done day by day without problems. But the other way around is an interesting challenge: to retrieve mails from an mail account using SQL or PL/SQL. What's this good for? This is obvious: The process of retrieving emails could be triggered by a database event - some PL/SQL collects the email and processes them further.

Doch wie macht man das? Schließlich gibt es kein PL/SQL-Pakete zum Abrufen von Emails. Die Lösung lautet Java in der Datenbank. Die Java Mail API ist in der Java Welt sehr bekannt und kann nicht nur Mails senden, sondern auch aus Postfächern (POP3 und IMAP) abrufen. Also schreiben wir eine Java Stored Procedure: Diese verbindet sich auf den Mailserver, ruft die Mails ab und gibt Sie als Array zurück. Das Array wird dann auf ein PL/SQL VARRAY abgebildet, so dass die Java Stored Procedure wie eine Table Function verwendet werden kann. Doch genug der Vorrede - hier kommt der Code (dieses Codebeispiel ist nur mit IMAP-Mailservern getestet - eim Umschreiben auf POP3-Postfäche sollte aber nicht so kompliziert sein)

But how to do this? There is no PL/SQL package for retrieving Mails - with pure PL/SQL this simply cannot be done. The solution is (as in many other cases) java in the database. The java mail API is very well known (in the java programmers' world) and it can not only send mail but also retrieve it via POP3 as well as via the IMAP protocol. So we'll create a java stored procedure: This procedure connects to the mail server, retrieves the mail headers and returns them as an array - this array must then be converted to a PL/SQL VARRAY. This java stored procedure can finally be accessed like a table function. But this is enough talking - here's the code (this example is for IMAP email servers - changing it to POP3 should be an easy task)

set define off

drop procedure get_mail_headers
/
drop type mail_header_ct
/
drop type mail_header_t
/

create or replace type mail_header_t as object(
  subject       varchar2(4000),
  sender        varchar2(100),
  sender_email  varchar2(100),
  sent_date     date,
  deleted       char(1),
  read          char(1),
  recent        char(1),
  answered      char(1)
)
/

create or replace type mail_header_ct as table of mail_header_t
/


create or replace java source named "GetMailHeaders" as 
import java.sql.Connection;
import java.sql.DriverManager;

import java.util.Properties;
import java.util.Vector;

import javax.mail.Folder;
import javax.mail.FetchProfile;
import javax.mail.Message;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.Flags;
import javax.mail.internet.InternetAddress;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;


class getMails {

  public static ARRAY getMails(String host, int port, String user, String pass, int maxHeaders) 
  throws Exception {
    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    ArrayDescriptor aDescr = ArrayDescriptor.createDescriptor("MAIL_HEADER_CT", con);
    StructDescriptor rDescr = StructDescriptor.createDescriptor("MAIL_HEADER_T", con);
    Object[] mailHeader = new Object[8];
    STRUCT oraMail = null;
    Vector vMails = new Vector();
      
    Properties props = new Properties();
    props.setProperty("mail.store.protocol", "imap");
    Session s = Session.getDefaultInstance(props);
    Store store = s.getStore();
    store.connect(host, port, user, pass);
    Folder folder = store.getFolder("INBOX");
    folder.open(Folder.READ_ONLY);
    Message message[] = folder.getMessages();
    FetchProfile fp = new FetchProfile();
    fp.add(FetchProfile.Item.ENVELOPE);
    fp.add(FetchProfile.Item.FLAGS);
    folder.fetch(message, fp);
  
    String sPersonal = null;

    for (int i=message.length-1; i>=0 && ((i>=(message.length - maxHeaders)) || maxHeaders == -1);i--) {
      mailHeader[0] = new String(message[i].getSubject());
      sPersonal = ((InternetAddress)(message[i].getFrom()[0])).getPersonal();
      if (sPersonal == null) {
        mailHeader[1] = new String("");
      } else {
        mailHeader[1] = new String(sPersonal);
      }
      mailHeader[2] = new String(((InternetAddress)(message[i].getFrom()[0])).getAddress());
      mailHeader[3] = new java.sql.Timestamp(message[i].getSentDate().getTime());
      mailHeader[4] = (message[i].isSet(Flags.Flag.DELETED)?"Y":"N"); 
      mailHeader[5] = (message[i].isSet(Flags.Flag.SEEN)?"Y":"N"); 
      mailHeader[6] = (message[i].isSet(Flags.Flag.RECENT)?"Y":"N"); 
      mailHeader[7] = (message[i].isSet(Flags.Flag.ANSWERED)?"Y":"N"); 
      oraMail = new STRUCT(rDescr, con, mailHeader);
      vMails.add(oraMail);
    }
    folder.close(false);
    store.close();
    return new ARRAY(aDescr, con, vMails.toArray());
  }
}
/

alter java source "GetMailHeaders" compile
/
sho err

Hier ist der PL/SQL Wrapper, welcher die Java-Klasse auf eine PL/SQL Table Function abbildet.

This is the "PL/SQL wrapper" which makes the java method available to PL/SQL.

create or replace function get_mail_headers(
  p_mailhost in varchar2,
  p_mailport in number,
  p_username in varchar2,
  p_password in varchar2,
  p_max_mails in number 
) return MAIL_HEADER_CT
is language java name 'getMails.getMails(
  java.lang.String, 
  int, 
  java.lang.String, 
  java.lang.String, 
  int
) return oracle.sql.ARRAY';
/

Die Funktion GET_MAIL_HEADERS hat folgende Parameter:

1. p_mailhost: Der Mailserver-Hostname oder die IP-Adresse
2. p_mailport: Der TCP/IP-Port des Mailservers (normalerweise 143 für IMAP-Postfächer)
3. p_username: Username zum Login in den Mailserver
4. p_password: Passwort zum Login in den Mailserver
5. p_max_mails: Maximal abzurufende Mails, "-1" ruft alle ab.

Und wie immer, wenn man mit Java Stored Procedures Netzwerkverbindungen nach "draußen" öffnen möchte, benötigt man Privilegien: Der folgende Code muss als SYS abgesetzt werden und räumt dem User SCOTT das Privileg ein, Netzwerkverbindungen zu mailserver.mydomain.com zu öffnen.

The function GET_MAIL_HEADERS is called with the following parameters:

1. p_mailhost: The Mailserver hostname or its IP address
2. p_mailport: TCP/IP port of the mailserver (in most cases "143" for IMAP servers)
3. p_username: mailserver username
4. p_password: mailserver password
5. p_max_mails: maximum number of mails to be retrieved; "-1" gets all

And ... as always when opening network connections with java ... some privileges are needed. The following call grants the privilege to connect to mailserver.mydomain.com and must be issued as SYS.

begin
  dbms_java.grant_permission( 
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.net.SocketPermission',
    permission_name   => 'mailserver.mydomain.com', 
    permission_action => 'connect,resolve' 
  );
end;
/

commit
/

Ausprobieren ist dann ganz einfach ...

Testing is very simple ...

select sender, subject from table(
  get_mail_headers(
    'mailserver.domain.com', 
    143, 
    'carsten.czarski',
    'ganzgeheim', 
    -1
  )
)
/

SENDER                                  SUBJECT
--------------------------------------  ----------------------------------------
Max.Muster@maildomain.com               Hallo Carsten
:                                       :

Und was bringt das? Sowie die Mail-Header als (virtuelle) Tabelle mit SQL abrufbar sind, kann man jedes beliebige Web-Framework nutzen, um sie als Tabelle auf eine Webseite zu bringen - Ein Beispiel für die Darstellung mit Application Express findet sich in der Application Express-Community - einfach mal reinschauen.

And what's this good for ...? As the mail headers are available as a virtual table (via the table function) one can use every web framework to get them onto a web page in tabular format ("report"). A (german) example describing how this is used with Oracle Application Express can be found in the german Application Express community.