30. April 2009

Email abrufen mit PL/SQL: Package MAIL_CLIENT

English title: PL/SQL Mail Client API

Das Versenden von Emails mit PL/SQL ist ja recht einfach; dazu gibt es fertige Pakete: UTL_SMTP, UTL_MAIL und für APEX-Entwickler gibt es nochmals APEX_MAIL. Anders sieht es aus, wenn es darum geht, ein Postfach auszulesen, also Mails zu empfangen. Dazu hatte ich schon die eine oder andere Anfrage, so dass ich mich vor einiger Zeit entschlossen habe, das Thema mal anzugehen.
As most of you know sending emails from SQL or PL/SQL is not a problem. There are PL/SQL packages available out-of-the-box: UTL_SMTP, UTL_MAIL or APEX_MAIL. The latter one is intended for APEX developers. But when it is about retrieving messages from a mailbox server there is no package available. At the first glance this seems to be not possible. But wait: There is a JVM in the database and java developers know about the Java Mail API. This API is also contained in the database. Since I had some talks about such a functionality in the past I once started to build a Package MAIL_CLIENT which is just capable to receive emails.
Das Ergebnis ist ein PL/SQL-Package MAIL_CLIENT und einige Objekttypen. Die Kommunikation mit dem Mailserver via POP- oder IMAP-Protokoll wird von der Java Mail API übernommen, die dank der datenbankinternen JVM in der Datenbank vorhanden ist. Ihr könnt das Package hier herunterladen.
You can download the package here (documentation and sample scripts are contained):
Dokumentation und Beispielskripte sind im Paket enthalten. Die Nutzung ist recht einfach. Zunächst muss eine Verbindung zum Mailserver hergestellt werden.
Usage is easy: First connect to a mail server and open a folder (INBOX in most cases).
After that retrieve the mail headers ...
Nun kann man sich die vorhandenen Mails abrufen ...
Danach kann man sich eine Nachricht als MAIL_T abrufen. Der Zugriff erfolgt dabei stets über die Nachrichten-Nummer (msg_number)...
A message is being represented by MAIL_T. MAIL_CLIENT.GET_MESSAGE retrieves the message details but not the actual message content.
MAIL_T ist ein Objekttyp und steht für eine Nachricht auf dem Server. Einfache Textnachrichten kann man bspw. mit MAIL_T.GET_SIMPLE_CONTENT_CLOB abrufen. Für Multipart-Nachrichten (Mails mit Attachments) gibt es eigene Methoden. Mehr dazu in der Dokumentation.
The actual content is being retrieved with MAIL_T.GET_SIMPLE_CONTENT_CLOB. This is for simple text messages. There are special methods for Multipart messages. A messages with attachments is an example for a multipart message. See the documentation for more information.
Und schließlich wird die Verbindung zum Mailserver getrennt.
Finally disconnect from the mailserver.
Viel Spaß beim Ausprobieren ...
Have fun.

Kommentare:

Dimitri Gielis hat gesagt…

Hi Carsten,

Very nice package.
Does it also handles attachments in mails?
At first glance I couldn't see it in the documentation.

Thanks,
Dimitri

Carsten Czarski hat gesagt…

Hi Dimitri,

it does. MAIL_T.GET_STRUCTURE to retrieve the message's part structure ...

select partindex, content_type, part_size from table(mail_client.get_message(30).get_structure())The PARTINDEX column then allows you to retrieve a specific "body part". A body part might be "inline" or "attachment"; the CONTENT_DISPOSITION column indicated this.

select mail_client.get_message(30).get_bodypart_content_blob('0,1')
from dual
I've chosen this approach to handle the ("fully-blown object-oriented") java mail structures in PL/SQL ...

Best regards

-Carsten

Patrick Wolf hat gesagt…

Cool!

Anonym hat gesagt…

Hi Carsten,

Does your package works also on Oracle XE ?

Thanks

Bertrand

Carsten Czarski hat gesagt…

Hi,

no, since OracleXE has no embedded Java VM, this package cannot work in XE.

Best regards

-Carsteb

Carsten hat gesagt…

Hallo Carsten,

kannst du bitte das löschen der Mail nochmal erklären?

Ich habe den 2Zeiler aus der Doku genommen und bekomme folgende Meldung:

'component "MARK_DELETE" must be declared'

Carsten Czarski hat gesagt…

Hallo Carsten,

in der Version 0.1 hatte die Doku einen Fehler; MARK_DELETE muss so verwendet werden:

declare
v_msg mail_t;
begin
v_msg := mail_client.get_message(1);
v_msg.mark_delete();
end;

in der aktuellen Version 0.2 ist das korrigiert.

Grüße

-Carsten

Shichao hat gesagt…

Hello,

May I ask that if this package support the SSL connection for pop3?
Is it possible to connect to for example: pop3.live.com (Port 995)?

Thanks,
Shichao

Carsten Czarski hat gesagt…

Hi Shichao,

I haven't worked with SSL so far - a prerequisite for this is the correct handling of SSL certificates. The Mail Cleint API needs to validate the server's certficate in this case.

Maybe I try this with one of the next releases ...

Regards

-Carsten

Carsten hat gesagt…

Hallo Carsten,

ich verwende u.a. die Funktion GET_BODYPART_CONTENT_BLOB um die Attachments aus der Mail zu holen und sie in einer extra Tabelle für Dokumente abzulegen.

Nun kam heute eine Mail rein, die nicht weiterverarbeitet wurde. Ich bin dem auf die Spur und bekam folgende Exception:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: Selected Message Part is a javax.mail.Multipart object

So sieht der content_type der entsprechenden Mail aus:

multipart/alternative";
boundary="----_=_NextPart_001_01CB2320.D44FB214"

Hast du eine Idee, wie da zu verfahren ist?

Grüße,
Carsten

Carsten Czarski hat gesagt…

Hallo Carsten,

es gibt(zwar selten, aber es gibt sie) manche Emails, die die Java-API nicht verarbeiten kann - diese werfen dann solche Exceptions. Dann hilft nur die Funktion DUMP_CLOB bzw. DUMP_BLOB.

Hast Du die neueste Version (0.93) installiert - in diesem Umfeld gab es auch ein paar kleinere Bugs.

Beste Grüße

-Carsten

Anantha hat gesagt…

Thanks for the package.

I was trying to access my gmail account through this, (SSL) but could not get past this:

v_property := dbms_java.set_property('java.security.egd', '/dev/urandom');

It says component SET_PROPERTY must be declared.

How can i check that the dbms_java is of the version you were using?

JServer JAVA Virtual Machine - 10.2.0.4.0
But java_pool_size = 0. Is this the problem?

Carsten Czarski hat gesagt…

Hi,

that might be the problem - you might check whether java is being installed and configured correctly in your database.

The "java_pool_size" parameter leads to the assumption that there might be problems ...

Regards

-Carsten

Stefan hat gesagt…

Hallo Herr Czarski,
ein tolles Package. Schade, dass Oracle so etwas nicht von Haus aus mitliefert.

Leider funktioniert der Connect aus einer 11.1er Datenbank zu einem SBS 2011 bei mir nicht. Ich bekomme nur diesen Fehler, obwohl ich p_ssl auf false gesetzt habe. Könnte es an Einstellungen des Mailservers liegen?

ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet: javax.mail.MessagingException: Unrecognized SSL message, plaintext connection?;
nested exception is:
javax.net.ssl.SSLException: Unrecognized SSL message, plaintext connection?

Mit besten Grüßen
Stefan Schmitz

Carsten Czarski hat gesagt…

Hallo Herr Schmitz,

hmmm ... passen die verwendete Portnummer und p_ssl zusammen ...? Meist ist die Ursache, dass SSL über den "nicht-SSL-Port" oder "Nicht-SSL" über den SSL-Port versucht wird.

Beste Grüße

-Carsten Czarski

Sarah Buschner hat gesagt…

Hallo Carsten,

tolles Package! Wirklich gute Arbeit.
Was ich allerdings noch nicht nachvollziehen kann: Wie genau funktioniert das Speichern des Attachments? Ich möchte das ganze als BLOB in eine Tabelle speichern. Könntest Du mir bitte weiterhelfen?

Viele Grüße,

Sarah

Carsten Czarski hat gesagt…

Hallo Sarah,

dazu brauchst Du mehrere Aufrufe. Zunächst solltest Du Dir mit einem Call auf GET_STRUCTURE die "Struktur" der Mail ansehen (es gibt ja vielleicht mehr als ein Attachment - und eingebettete Bilder sind ebenfalls welche). Mit dem PARTINDEX kannst Du dann mit dem Call GET_BODYPART_CONTENT_BLOB eben dieses Attachment holen und Dir als BLOB zurückgeben lassen. Und den speicherst Du dann in eine Tabelle ...

Beste Grüße

-Carsten

Beliebte Postings