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

Billy Verreynne

Here is a simplistic example of a POP3 PL/SQL interface - posted this some years ago, but the original message poorly survived the Jives forum upgrade, resulting in very badly formatted code.

Here is that code: [edit: TStrings are create or replace type TStrings is table of varchar2(4000);]

<p>-- create or replace type TStrings as table of varchar2(4000)</p><p><br></p><p>SQL> create or replace function pop3( msgNum number ) return TStrings pipelined is</p><p>&nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;POP3_SERVER&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; constant varchar2(19) := '10.2.2.2';</p><p>&nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;POP3_PORT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; constant number := 110;</p><p>&nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;POP3_TIMEOUT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;constant number := 30;</p><p>&nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;POP3_OK&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; constant varchar2(10) := '+OK';</p><p>&nbsp; 6&nbsp;</p><p>&nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E_POP3_ERROR&nbsp; &nbsp; exception;</p><p>&nbsp; 8&nbsp;</p><p>&nbsp; 9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;socket&nbsp; UTL_TCP.connection;</p><p>10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; line&nbsp; &nbsp; varchar2(4000);</p><p>11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bytes&nbsp; integer;</p><p>12&nbsp;</p><p>13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; userName varchar2(20) := 'username';</p><p>14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; password varchar2(20) := 'password';</p><p>15&nbsp;</p><p>16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- send a POP3 command</p><p>17&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- (we expect each command to respond with a +OK)</p><p>18&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; function&nbsp; WriteToPop( command varchar2 ) return varchar2 is</p><p>19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; len&nbsp; &nbsp; integer;</p><p>20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resp&nbsp; &nbsp; varchar2(4000);</p><p>21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin</p><p>22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; len := UTL_TCP.write_line( socket, command );</p><p>23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UTL_TCP.Flush( socket );</p><p>24&nbsp;</p><p>25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- using a hack to check the popd response</p><p>26&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; len := UTL_TCP.read_line( socket, resp );</p><p>27&nbsp;</p><p>28&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if substr(resp,1,3) != POP3_OK then</p><p>29&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; raise E_POP3_ERROR;</p><p>30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end if;</p><p>31&nbsp;</p><p>32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return( resp );</p><p>33&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;</p><p>34&nbsp;</p><p>35&nbsp; begin</p><p>36&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( 'pop3:'||POP3_SERVER||' port:'||POP3_PORT );</p><p>37&nbsp;</p><p>38&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- open a socket connection to the POP3 server</p><p>39&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; socket := UTL_TCP.open_connection(</p><p>40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; remote_host => POP3_SERVER,</p><p>41&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; remote_port => POP3_PORT,</p><p>42&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tx_timeout&nbsp; => POP3_TIMEOUT,</p><p>43&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; charset&nbsp; &nbsp; => 'US7ASCII'</p><p>44&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; );</p><p>45&nbsp;</p><p>46&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- read the server banner/response from the pop3 daemon</p><p>47&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( UTL_TCP.get_line(socket) );</p><p>48&nbsp;</p><p>49&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- authenticate with the POP3 server using the USER and PASS commands</p><p>50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( 'USER '||userName ); pipe row( WriteToPop('USER '||userName) );</p><p>51&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( 'PASS '||password ); pipe row( WriteToPop('PASS '||password) );</p><p>52&nbsp;</p><p>53&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- retrieve the specific message</p><p>54&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( 'RETR '||msgNum ); pipe row( WriteToPop('RETR '||msgNum) );</p><p>55&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( '*** START OF INTERNET MESSAGE BODY ***' );</p><p>56&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; loop</p><p>57&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bytes := UTL_TCP.Available( socket );</p><p>58&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if bytes > 0 then</p><p>59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bytes := UTL_TCP.read_line( socket, line );</p><p>60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; line := REPLACE( line, chr(13)||chr(10), '' );</p><p>61&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if length(line) = 1 and line = '.' then</p><p>62&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( '*** END OF INTERNET MESSAGE BODY ***' );</p><p>63&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else</p><p>64&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( line );</p><p>65&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end if;</p><p>66&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end if;</p><p>67&nbsp;</p><p>68&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; exit when length(line) = 1 and line = '.' ;</p><p>69&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end loop;</p><p>70&nbsp;</p><p>71&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- close connection</p><p>72&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pipe row( 'QUIT' );&nbsp; &nbsp; pipe row( WriteToPop('QUIT') );</p><p>73&nbsp;</p><p>74&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UTL_TCP.close_connection( socket );</p><p>75&nbsp;</p><p>76&nbsp; exception when OTHERS then</p><p>77&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UTL_TCP.close_connection( socket );</p><p>78&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; raise;</p><p>79&nbsp; end;</p><p>80&nbsp; /</p><p><br></p><p>Function created.</p><p><br></p><p>SQL></p><p>SQL> col LINE format a100 wrapped</p><p>SQL> select</p><p>&nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; length(column_value)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; as "SIZE",</p><p>&nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; column_value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; as "LINE"</p><p>&nbsp; 4&nbsp; from&nbsp; &nbsp; &nbsp; table( pop3(1) )</p><p>&nbsp; 5&nbsp; /</p><p><br></p><p>&nbsp; &nbsp; &nbsp; SIZE LINE</p><p>---------- ----------------------------------------------------------------------------------------------------</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 27 pop3:10.2.2.2 port:110</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 67 +OK DavMail 4.7.1-2416 POP ready at Wed Mar 06 14:11:45 SAST 2019</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 12 USER username</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 20 +OK USER : username</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 14 PASS password</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 10 +OK PASS</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 6 RETR 1</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 6 +OK</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 38 *** START OF INTERNET MESSAGE BODY ***</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 53 Received: from POP3-XMB01.domain.com ([::1]) by</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 76&nbsp; POP3-XHT02.domain.com ([::1]) with mapi id 14.03.0439.000; Wed, 6 Mar</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; 2019 14:11:02 +0200</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 51 From: "Billy" <[email protected]></p><p>&nbsp; &nbsp; &nbsp; &nbsp; 34 To: "Billy" <[email protected]></p><p>&nbsp; &nbsp; &nbsp; &nbsp; 20 Subject: test e-mail</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 25 Thread-Topic: test e-mail</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 46 Thread-Index: AQHU1BWqNZAyhxg0ckWyNqV8XQPgEA==</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 36 Date: Wed, 6 Mar 2019 12:11:01 +0000</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 52 Message-ID: <[email protected]></p><p>&nbsp; &nbsp; &nbsp; &nbsp; 29 Accept-Language: en-ZA, en-US</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 23 Content-Language: en-US</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 43 X-MS-Exchange-Organization-AuthAs: Internal</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 44 X-MS-Exchange-Organization-AuthMechanism: 04</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 66 X-MS-Exchange-Organization-AuthSource: POP3-XHT02.domain.com</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 16 X-MS-Has-Attach:</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 34 X-MS-Exchange-Organization-SCL: -1</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 21 X-MS-TNEF-Correlator:</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 46 Content-Type: text/plain; charset="iso-8859-1"</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 43 Content-Transfer-Encoding: quoted-printable</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 17 MIME-Version: 1.0</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 27 what do you want, universe?</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 36 *** END OF INTERNET MESSAGE BODY ***</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 4 QUIT</p><p>&nbsp; &nbsp; &nbsp; &nbsp; 9 +OK Bye</p><p><br></p><p>35 rows selected.</p>

This will only work if there is a mail to retrieve - but do show the very basics of POP3 integration in PL/SQL.

user6439693

output is this comming

    SIZE LINE

--------- ----------------------------------------------------

      24 pop3:10.126.2.10 port:26

      46 220 PostMaster Enterprise SMTP Server Ready

      23 USER [email protected]

with error

500 PostMaster Enterprise Syntax error, readLineData unrecognized: USER

Why this error is comming.

who can i correct this ,

please help me

Billy Verreynne

You are connecting to a SMTP server, not a POP3 server.

Marcelo Osorio Kosky

Well, question from long time ago , anyway here my answer. Yes it is feasible. One of the best uses of email reading is convert the emails as part of the approval process (for instance). The big difference using or placing buttons within the email with apis (pointing to api url with tokens and apikeys ) is when you receive an email with urls, you could send the email to another person (forward or reply) then, the persons could execute or call the apis , that were sent to you. In other hand, when you are reading email you can write a hash code within the email text and validate the sender against the hash code (as text) within the email.

I know this is feasible since I have created my own working version, starting from here http://plsqlmailclient.sourceforge.net

now I am working in order this work within Oracle OCI.

Best regards,

Marcelo