Forum Stats

  • 3,769,004 Users
  • 2,252,898 Discussions
  • 7,874,837 Comments

Discussions

How read emails in Oracle database using pl/sql

User_DT12P
User_DT12P Member Posts: 3 Employee
edited Mar 27, 2019 1:35PM in SQL & PL/SQL

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

Tagged:
EdStevensFrank Kulashandrewmy_jumuser13328581_Dylan_
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 5, 2019 9:06AM
    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
    BluShadow Member, Moderator Posts: 41,479 Red Diamond
    edited Mar 5, 2019 9:09AM

    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
    EdStevens Member Posts: 28,522 Gold Crown
    edited Mar 5, 2019 10:24AM

    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.

    Frank Kulashandrewmy
  • BluShadow
    BluShadow Member, Moderator Posts: 41,479 Red Diamond
    edited Mar 5, 2019 10:53AM
    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. 

    EdStevens
  • Mike Kutz
    Mike Kutz Member Posts: 5,807 Silver Crown
    edited Mar 5, 2019 5:24PM

    @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

    andrewmy
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 5, 2019 10:15PM
    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
    Billy Verreynne Software Engineer Member Posts: 28,590 Red Diamond
    edited Mar 5, 2019 11:56PM
    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
    Billy Verreynne Software Engineer Member Posts: 28,590 Red Diamond
    edited Mar 6, 2019 12:17AM
    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.

    andrewmy
  • User_DT12P
    User_DT12P Member Posts: 3 Employee
    edited Mar 6, 2019 12:28AM

    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
    John_K Member Posts: 2,498 Gold Trophy
    edited Mar 6, 2019 5:55AM

    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".

    _Dylan_