1 2 Previous Next 16 Replies Latest reply on Apr 1, 2013 2:22 PM by SomeoneElse

    Read Mail Folder

      Hi All
      With the support provided by maildemo8i in the sample code of oracle site i am able to send mail to my smtp server.
      Any tutorial or links,how to read the folders of my mailbox which is in my mail server.

      Suggest any links.

        • 1. Re: Read Mail Folder
          Hi Shashidhar,

          Am looking for a similar requirement, can you share if you have found some solution for this?

          • 2. Re: Read Mail Folder
            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.
            • 3. Re: Read Mail Folder
              wow, thats something new , must say oracle is vast,

              well billy i guess you should start up some on line training courses and get paid also ;-)
              • 4. Re: Read Mail Folder
                > 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. :-)
                • 5. Re: Read Mail Folder
                  understand this i meant it was new to me,

                  and what about online training ;-)
                  • 6. Re: Read Mail Folder

                    I had no idea that pipeline function can be used for this purpose before until i saw this post.


                    Satyaki De.
                    • 7. Re: Read Mail Folder
                      > 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.
                      • 8. Re: Read Mail Folder
                        > and what about online training ;-)

                        The best training IMO is getting your feet wet and hands dirty. SQL*Plus, vim, a dev database, and the manuals... ;-)
                        • 9. Re: Read Mail Folder

                          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:
                          Manohar Setty
                          • 10. Re: Read Mail Folder
                            i have exp on VI but never worked on vim, do you have any links ?
                            • 11. Re: Read Mail Folder
                              Hi All,

                              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.

                              • 12. Re: Read Mail Folder
                                I know you are not allowed to access Google from work so I won't tell you to "just Google it".


                                Here you go.
                                • 13. Re: Read Mail Folder
                                  yes, very true ;-); thanks
                                  • 14. Re: Read Mail Folder
                                    > 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.

                                    SQL> create or replace type TStrings is table of varchar2(4000);
                                    2 /

                                    Type created.

                                    SQL> create or replace function pop3( userName varchar2, password varchar2, msgNum number ) return TStrings pipelined is
                                    2 POP3_SERVER constant varchar2(19) := '';
                                    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);
                                    20 begin
                                    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 );
                                    32 end;
                                    36 begin
                                    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'
                                    44 );
                                    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 ***' );
                                    56 loop
                                    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 );
                                    71 end;
                                    72 /

                                    Function created.

                                    SQL> show errors
                                    No errors.
                                    SQL> select * from TABLE( pop3('billy','my-secret-password-goes-here',1) );

                                    pop3: port:110
                                    +OK You are connected to a test Dovecot POP3 server
                                    USER billy
                                    PASS my-secret-password-goes-here
                                    +OK Logged in.
                                    RETR 1
                                    +OK 548 octets
                                    *** START OF INTERNET MESSAGE BODY ***
                                    Return-path: <oracle-test@oracle.com>
                                    Envelope-to: billy@localhost.com
                                    Delivery-date: Mon, 20 Aug 2007 09:23:38 +0200
                                    Received: from localhost ([])
                                    by localhost.com with smtp (Exim 4.63)
                                    (envelope-from <oracle-test@oracle.com>)
                                    id 1IN1bC-0001BC-9A
                                    for billy@localhost.com; Mon, 20 Aug 2007 09:23:38 +0200
                                    Subject: Test E-Mail
                                    X-Mailer: telnet
                                    Message-Id: <E1IN1bC-0001BC-9A@localhost.com>
                                    From: oracle-test@oracle.com
                                    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.

                                    Note that the trailing period is the end-of-message-body marker and not really part of the message body that the sender created.

                                    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.
                                    1 2 Previous Next