4. Dezember 2007

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.

Kommentare:

Patrick Wolf hat gesagt…

Wie schon auf der DOAG gesagt, sehr nett!

Lg
Patrick

Anonym hat gesagt…

Hi Patrick,
Thanks for posting this on your blogsite. I will really appreciate if you please send me the english verison of this code to achieve this. I have tried to translate your this page (via google) but did not work. I will really appreciate if you please send or make this available in Eng version also. Many thanks & regards, Kavin
love9k@sify.com

Filipe Silva hat gesagt…

I got

java.lang.ClassCastException
at oracle.jdbc.driver.PhysicalConnection.putDescriptor(PhysicalConnection.java:3374)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:114)
at getMails.getMails(GetMailHeaders:31)

the error occurs in the ArrayDescriptor aDescr = ArrayDescriptor.createDescriptor("MAIL_HEADER_CT", con); line.
How can I solve that error?

Carsten Czarski hat gesagt…

hmm ... which database version did you test on ...?

I tested on 10.2 as well as on 11.1 ans it compiles and runs without problems.

Best regards

-Carsten

Filipe Silva hat gesagt…

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bits

Filipe Silva hat gesagt…

I tried it in a clean instance and it worked!

Thanks!
great code!
I am suer I can find this info but maybe you already have it. How can I change a message to "read" status after I "read" it?

Carsten Czarski hat gesagt…

the java class "Message" contains a method for this - so you'd have again to code this in java and to "wrap" the java method as a PL/SQL stored procedure.

I think it would be a good idea to have some kind of DBMS_MAILCLIENT package which uses the java API to expose more functionality to PL/SQL - but coding this requires some time ... and as always: time is a rare good ...

Best regards

-Carsten

Pulakesh Dey hat gesagt…

Hi Carsten,

Though this post is too old but still we do not have any other alternative of this. I was to receive mails in APEX to analyze some of the text of mail content.

I tried to use your code but its not getting compiled.
Below is the error I am getting.

create or replace and compile java source named "GetMailHeaders" as
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "with" at line 61, column 36.
Was expecting one of:
"instanceof" ...
")" ...
"," ...
"<" ...
"?" ...
"==" ...

Thanks,
Pulakesh

Pulakesh Dey hat gesagt…

Hi,

Though this post is quite old, but its still quite relevant.

I tried to use this but got below error which I can not find any solution. Can you please help me on this? I tried to "set define off", I believe its something different.

I am using Oracle 12c
--------------------------
create or replace and compile java source named "GetMailHeaders" as
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "with" at line 61, column 36.
Was expecting one of:
"instanceof" ...
")" ...
"," ...
"<" ...
"?" ...
"==" ...

Carsten Czarski hat gesagt…

Hi Pulakesh,

I'd recommend to get the latest package from here:
http://plsqlmailclient.sourceforge.net/

This is a more complete codebase. Then, install either with SQLPlus or, when using APEX, upload the file as a "SQL Script" and run it as such. Do not use "SQL Commands" within SQL Workshop.

Then it should install without problems.

Best regards

Carsten

Pulakesh Dey hat gesagt…

Thanks for your super fast response.

only one thing, I need to test this solution with gmail.com email id.

If I use sample scripts supplied with api then how should it be used?


Thanks,
Pulakesh

Pulakesh Dey hat gesagt…

I was trying to use sample file to just setup a connection with my gmail id but throwing error as below.

------------------------------------
SQL> @0_open-mailbox.sql
>> This script connects to a mail server with the specified login credentials.
>> After connection it opens the mailbox folder "INBOX"
>>
>> Mailserver [mail.domain.de] imap.gmail.com
>> Mailserver port (pop3=110|imap=143) [143] 993
>> Mailserver protocol (pop3|imap) [imap] imap
>> Mailbox username [] pulakesh.dey@gmail.com
>> Mailbox password [] XXXXXXXXXXXXXX

begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NoClassDefFoundError
ORA-06512: at "SYS.MAIL_CLIENT", line 8
ORA-06512: at "SYS.MAIL_CLIENT", line 37
ORA-06512: at line 2
------------------------------------

Thanks in advance. It will be really helpful if you give your response real quick. Appreciate your help.

Thanks,
Pulakesh

Carsten Czarski hat gesagt…

Did you run the "install.sql" script?

Carsten

Pulakesh Dey hat gesagt…

Yes, I have executed install and grant both the scripts

Carsten Czarski hat gesagt…

Hi Pulakesh,

when you see the "NoClassDefFoundError", I'm pretty sure, that the "install.sql" did *not* run through without errors. Looking at the console output you should see some error messages telling you what's wrong on your instance.

Best regards

-Carsten

Beliebte Postings