How read emails in Oracle database using pl/sql

User_DT12P

Hi,

Do we have any PL/SQL API to read email from an incoming email server. We have utl_mail and apex_mail APIs for sending emails.

Thanks,

Ram

John Thorton

3248989 wrote:Hi,Do we have any PL/SQL API to read email from an incoming email server. We have utl_mail and apex_mail APIs for sending emails.Thanks,Ram

no

You need email client software to obtain email messages from email server; all of which have NOTHING to do with Oracle database.

BluShadow

If you consider the "sending" of emails, then the UTL_MAIL package uses the UTL_SMTP package which itself uses the UTL_TCP package to communicate over TCP protocol over the network with the email server.  It is the SMTP protocol that stipulates the communication with the email server for sending emails.

For reading emails, it depends on the email server, but you would have to implement something like the POP protocol using the UTL_TCP package.

As far as I'm aware, Oracle doesn't provide a protocol for reading emails from an email server, either via POP or other protocols, but you could easily write it yourself as the protocols for email communication (sending and receiving) are fairly straightforward.  The first thing though would be to check what reading protocol your email server supports.

If you search the web for something like "oracle pl/sql pop3 protocol" then you may find someone has already written such a package, and then it's up to you whether you use that, adapt that, or write your own.

EdStevens

This sounds like an interesting project.  I'm curious as to the purpose.  Why is the database expected to go retrieve emails from an email server, and what will the database do with them once it has them?

Usually when I ask a question like this, I'm trying to get the OP to question the "requirement" because I suspect he is focusing on some pre-conceived technique instead of the business problem.  But this time I'm genuinely curious about how this is to be used.

BluShadow

EdStevens wrote:This sounds like an interesting project. I'm curious as to the purpose. Why is the database expected to go retrieve emails from an email server, and what will the database do with them once it has them?Usually when I ask a question like this, I'm trying to get the OP to question the "requirement" because I suspect he is focusing on some pre-conceived technique instead of the business problem. But this time I'm genuinely curious about how this is to be used.

It's not that unusual a requirement.  We have such requirements within our business.  There are data files received via email from certain sources (often outside our network and a 3rd party so we have no control over their choice of data format), and we have to have a process that email to remove the attachment, and then process the file in to the database.  Obviously it would be better to have web API's or suchlike... but sometimes you just have to work with what you've got. 

Mike Kutz

@BluShadow

Most of the SMTP servers I've seen have some sort of REST/SOAP API to "Fetch emails".

First question to the OP:  Which SMTP Server?

From there, I suspect that the package APEX_WEB_SERVICES would make short work of the implementation.

My $0.02

MK

John Thorton

3248989 wrote:Hi,Do we have any PL/SQL API to read email from an incoming email server. We have utl_mail and apex_mail APIs for sending emails.

please click  on URL below

LMGTFY

Billy Verreynne

3248989 wrote:Do we have any PL/SQL API to read email from an incoming email server. We have utl_mail and apex_mail APIs for sending emails.

UTL_TCP needs to be used for your PL/SQL code to talk POP3 or IMAP.

The POP3 and IMAP application protocols are clear-text protocols and fairly easily understood and implemented. And as these only require TCP client sockets, it can be implemented in PL/SQL (natively) using UTL_TCP.

The complex part is not doing POP3 or IMAP. That's easy. The complex part is processing the e-mail received. Especially if its multi-Mime and contains HTML and attachments.

The real question/issue is, what do you intend to do with the raw e-mail in the database? It needs to be a CLOB (only data type that supports reading that raw mail via POP3 or IMAP). Which means unstructured data that needs to be parsed. And the parser being clever enough to deal with the gunk that some mail readers generate (like Outlook).

Billy Verreynne

EdStevens wrote:This sounds like an interesting project. I'm curious as to the purpose. Why is the database expected to go retrieve emails from an email server, and what will the database do with them once it has them?

Ed, in our case we retrieve e-mails with XML attachments, and process these attachments.

As Exchange is used this side, with POP3 and IMAP services disabled, I used DavMail as POP3 (bridge) interface into Exchange, wrote a POP3 PL/SQL API, and wrote several SQL types to use for parsing a raw e-mail.

The latter was the most complex part, as MIME bodies (attachments) can be nested. And all e-mail headers are not equal. Dislike Exchange, as it screws around with perfectly valid mails received, by rewriting these, and even changing MIME boundary tags. What the hell, Microsoft?

Would have preferred a web service interface to be used instead of e-mail, for submitting XML files - it would have been far more robust and secure.

User_DT12P

We are implementation a framework to implement in workflow to replace Oracle BPM product. As part of this workflow framework  we need to read emails from email server.

John_K

A real "ad-hoc" way of doing it would be to use an external table with a script pre-processor - and then create a script which uses unix utilities to read the mail from the mailbox. so you can do "select * from my_emails".