This content has been marked as final. Show 16 replies
There are two basic protocols used for e-mailing. SMTP - Simple Mail Transfer Protocol. This is used to deliver an e-mail to a mail server for tranmission. And this is what UTL_SMTP implements.
The other protocol (used for fetching e-mails as oppose to delivering them) is called POP - Post Office Protocol. The current and most widely used version is called POP3.
It is not that difficult to use. Like SMTP is it a protocol to transport the e-mail cargo between client and server. It does not "display" an e-mail - once that e-mail cargo has arrived on your code's doorstep, you and your code need to process it.. determine the attachments, the contents, etc.
The POP protocol is described in [url http://www.faqs.org/rfcs/rfc1939.html]RFC1939.
A couple of comments.
POP3 is being replaced by IMAP (see RFC1730 Internet Message Access Protocol - Version 4). This is a more secure protocol than POP3 and has more features.
Microsoft Exchange can support both as far as I know. However, some anal Microsoft admins will disable both - which means you are pretty much screwed in that respect. In which case your only other alternative is to use MS Exchange's http/https interface. Like Ximian does for Evolution's MS Exchange plug-in (which works pretty well).
Do not start coding right away. First discover and learn POP3 basics. All you need is telnet (with local echo enabled in order to see what you type). A typical conversation with the POP3 server will be:
- logging on
- listing the number of messages
- retrieving a message
- deleting a retrieved message
- signing off
I would have liked to copy and pasted such a session for you, but unfortunately I deal with anal Microsoft Exchange admins. No POP3. No IMAP.
PS. to use telnet, do a telnet <mailserver> 110 and use the commands as described in RFC1939 above.
PPS. once you have the hang of this, use UTL_TCP and do the same sequence of commands.
> wow, thats something new , must say oracle is vast,
Well, it not that "new" when one deals with network services and network applications a lot. And what makes many of the protocols easy to use and to play with, is that they are clear-text protocols. Which means that you can use telnet, connect to that network service, and carry a conversation with it manually.
As for Oracle being vast - true. But POP3 and even SMTP and HTTP, have very little to do with the Oracle database itself. What is kewl is that we can, from inside Oracle, use these application protocols. But I would not call these as "part" of the Oracle concepts and fundamentals.
Oh yeah - what makes Oracle so great is that you can develop a pipeline table function in Oracle that makes use of POP3 to retrieve an e-mail... and then you can simply use SQL to display an e-mail.
E.g. display e-mail message number 2 from scott's POP server account (using username, password and message number as parameters):
select * from TABLE( pop3('scott','tiger', 2) );
One of the many reasons I like Oracle - flexibility to do some pretty neat stuff. :-)
> I had no idea that pipeline function can be used for this purpose before until i saw this post.
I mostly use pipeline table functions in this fashion - SQL wrappers for other services.
This make it easy for the developers to use as they simply need to know and use SQL - and not be bothered with the protocols and other complexities of the service they're actually dealing with, under the hood.
Pipeline tables in this fashion is simply another form of external tables, but instead of a SQL*Loader wrapper for loading a CSV, it wraps a service like HTTP, POP3 and so on.
The information you have provided here looks great,
Can you provide me some references for the same? (sample inplementation or study material)
Currently I'm looking for a PL/SQL function which can read mails and insert the contents into a table.
This would be of great help to me.
Thanks in Advance,
Message was edited by:
How did I miss this thread....?
I am currently working on a package called UTL_IMAP which I will be releasing publicly very soon (i.e. within the next week).
The first version uses Java stored procedures and the JavaMail API to provide a fairly simple interface for accessing mailboxes.
I plan to make V2 a more complete implementation of RFC3501 using UTL_TCP to implement the protocol from the ground up. Hopefully this will improve the performance as my java implementation suffers from a fairly hefty initialisation delay.
I'll post on this board with any developments.
> Can you provide me some references for the same? (sample inplementation or study
Well, here is a very basic example of a PL/SQL pipelined table function that wraps a POP3 mail account into a SQL table - allowing you to run SQLs directly against the e-mail account.
I did a few basic tests using a local POP3 server on my Linux box and it seems to work fine.
Note that the trailing period is the end-of-message-body marker and not really part of the message body that the sender created.
SQL> create or replace type TStrings is table of varchar2(4000);
SQL> create or replace function pop3( userName varchar2, password varchar2, msgNum number ) return TStrings pipelined is
2 POP3_SERVER constant varchar2(19) := '127.0.0.1';
3 POP3_PORT constant number := 110;
4 POP3_TIMEOUT constant number := 5;
5 POP3_OK constant varchar2(10) := '+OK';
7 E_POP3_ERROR exception;
8 E_READ_TIMEOUT exception;
9 pragma exception_init( E_READ_TIMEOUT, -29276 );
11 socket UTL_TCP.connection;
12 line varchar2(4000);
13 bytes integer;
15 -- send a POP3 command
16 -- (we expect each command to respond with a +OK)
17 function WriteToPop( command varchar2 ) return varchar2 is
18 len integer;
19 resp varchar2(4000);
21 len := UTL_TCP.write_line( socket, command );
22 UTL_TCP.Flush( socket );
24 -- using a hack to check the popd response
25 len := UTL_TCP.read_line( socket, resp );
27 if SUBSTR(resp,1,3) != POP3_OK then
28 raise E_POP3_ERROR;
29 end if;
31 return( resp );
37 PIPE ROW( 'pop3:'||POP3_SERVER||' port:'||POP3_PORT );
38 -- open a socket connection to the POP3 server
39 socket := UTL_TCP.open_connection(
40 remote_host => POP3_SERVER,
41 remote_port => POP3_PORT,
42 tx_timeout => POP3_TIMEOUT,
43 charset => 'US7ASCII'
46 -- read the server banner/response from the pop3 daemon
47 PIPE ROW( UTL_TCP.get_line(socket) );
49 -- authenticate with the POP3 server using the USER and PASS commands
50 PIPE ROW( 'USER '||userName ); PIPE ROW( WriteToPop('USER '||userName) );
51 PIPE ROW( 'PASS '||password ); PIPE ROW( WriteToPop('PASS '||password) );
53 -- retrieve the specific message
54 PIPE ROW( 'RETR '||msgNum ); PIPE ROW( WriteToPop('RETR '||msgNum) );
55 PIPE ROW( '*** START OF INTERNET MESSAGE BODY ***' );
57 bytes := UTL_TCP.Available( socket );
58 if bytes > 0 then
59 bytes := UTL_TCP.read_line( socket, line );
60 PIPE ROW( line );
61 end if;
63 exit when bytes = 0;
64 end loop;
65 PIPE ROW( '*** END OF INTERNET MESSAGE BODY ***' );
67 -- close connection
68 PIPE ROW( 'QUIT' ); PIPE ROW( WriteToPop('QUIT') );
70 UTL_TCP.close_connection( socket );
SQL> show errors
SQL> select * from TABLE( pop3('billy','my-secret-password-goes-here',1) );
+OK You are connected to a test Dovecot POP3 server
+OK Logged in.
+OK 548 octets
*** START OF INTERNET MESSAGE BODY ***
Delivery-date: Mon, 20 Aug 2007 09:23:38 +0200
Received: from localhost ([127.0.0.1])
by localhost.com with smtp (Exim 4.63)
for email@example.com; Mon, 20 Aug 2007 09:23:38 +0200
Subject: Test E-Mail
Date: Mon, 20 Aug 2007 09:23:21 +0200
This is a test message.
Blah blah blah...
*** END OF INTERNET MESSAGE BODY ***
+OK Logging out.
31 rows selected.
PS. Oh yes - I defined a timeout exception and tried catching that to determine the eof for the message body, but ran into what seems to be an Oracle bug. However, simply checking whether there is data to read, using the UTL_TCP.Available() function, works great.