1 2 Previous Next 20 Replies Latest reply: Jun 16, 2008 3:42 PM by 622829 RSS

    Question about POP3 and PL/SQL

    622829
      OK. I think I got the bulk of my questions answered in this post (regarding how to access email information from a POP3 server):

      Re: Read Mail Folder


      What we're trying to do is check one of our email accounts. If an email is received because it was returned because it was an undeliverable email that we sent out, we just want to take/pop it off the POP3 server. If it's an actual reply from a user, then we also want to take/pop it off the POP3 server, but also we want to get the from address, subject, body, etc., and store this info in a table. I think that link from above shows me how to access the contents of the email itself, but what's the process for removing an email from the stack? I did searches on this forum and google, but couldn't find anything. Thanks!
        • 1. Re: Question about POP3 and PL/SQL
          229023
          DELE msg# removes the email from the POP3 server. This link gives the mail commands that can be executed against the POP server.
          but also we want to get the from address, subject, body, etc., and store this info in a table.
          You've to parse the message. I don't think there's an easy way to do this.
          • 2. Re: Question about POP3 and PL/SQL
            622829
            Thanks for the reply. Hmmmm. I guess I had this backwards. I thought getting the relevant information from the email itself would be easier than manipulating emails on the POP server.

            A couple of follow-ups. I just looked at the link that you included and I can see the various commands I can use, however, it seems that I would have to use telnet to open a connection to my POP server, and then execute these commands manually, right? How can I automate executing these commands from a PL/SQL procedure though?

            And secondly, it shouldn't be too bad to parse through the message, right? It seems that the message will follow a strict pattern, right? Like the message would resemble something like (from that link in my original post):

            *** 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 ([127.0.0.1])
            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 ***


            It seems like I should be able to use combinations of length and substr to retrieve the information that I want, right?
            • 3. Re: Question about POP3 and PL/SQL
              Jens Petersen
              Did you consider using javamail?

              See here for an example
              URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
              • 4. Re: Question about POP3 and PL/SQL
                229023
                Whatever commands you type over the telnet is the same that you use with UTL_TCP to interact with the pop3 server. If you check Billy's code, you'll realise that.
                • 5. Re: Question about POP3 and PL/SQL
                  622829
                  Whatever commands you type over the telnet is the
                  same that you use with UTL_TCP to interact with the
                  pop3 server
                  Got it, so it will be something like:

                  utl_tcp.write_line(connection_string, 'DELE 1');
                  • 6. Re: Question about POP3 and PL/SQL
                    622829
                    Did you consider using javamail?

                    See here for an example
                    URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
                    Thanks! I don't have any experience with Java, but it seems like this package can do exactly what I need, right? It can parse through the email and get the relevant parts that I need as well as being able to delete the email itself, correct?
                    • 7. Re: Question about POP3 and PL/SQL
                      Billy~Verreynne
                      > Got it, so it will be something like:
                      utl_tcp.write_line(connection_string, 'DELE 1');

                      Yes. But it is not that simple.. The code I posted assumed that you are familiar with the POP3 protocol (like one needs to know the SMTP protocol to use UTL_SMTP).

                      POP3 is detailed in [url http://www.faqs.org/rfcs/rfc1939.html]RFC 1939.

                      You basically need to know how to act like a typical mail reader in order to get the applicable messages from the POP3 server.

                      If your code is looking for a specific e-mail, it may be the 11th or 32nd mail in the POP3 mail box. Using RETR 1 to only look at the first e-mail is pretty meaningless.

                      So you need to decide how you are going to find the applicable mail in the POP3 box. Are you going to use STAT to get the number (and size) of the mail drop and the use the TOP command to check the headers, looking for the applicable e-mail response?

                      I would not touch a JavaMail API.. simply because TCP programming in PL/SQL using UTL_TCP is NOT difficult. I prefer to actually control HOW my code is interacting with a server and know exactly WHAT my code is doing.

                      POP3 is one of the easier application protocols to deal with. But it do require you to know just how to talk POP3 to a mail drop. Which makes it important to read and understand the RFC1939. And experiment (using telnet) how the interaction works and then applying that knowledge to your code.

                      IMO you would become a far better skilled and experienced programmer this way, then to install some 3rd part API to do what you could have done better yourself.
                      • 8. Re: Question about POP3 and PL/SQL
                        Billy~Verreynne
                        > It seems like I should be able to use combinations of length and substr to retrieve the
                        information that I want, right?

                        Pretty much so. Regular expression matching may even do better.

                        Just remember that POP3 is only a delivery protocol. It delivers an e-mail from a mail drop to you. That's it.

                        The e-mail itself should (must) be a valid Internet Message (MIME) Body. You need to process and parse that. Even though this can be complex, dealing with a text/plain response e-mail (which I assume you want to do), should be quite easy.

                        The MIME (Multipurpose Internet Mail Extensions) messabe bodies are spec'ed in a number of RFCs (at http://www.faqs.org/rfcs/rfc-titles.html):
                        - RFC 2045 - Multipurpose Internet Mail Extensions (MIME) Part One: Format of Internet Message Bodies
                        - RFC 2046 - Multipurpose Internet Mail Extensions (MIME) Part Two: Media Types
                        - RFC 2047 - MIME (Multipurpose Internet Mail Extensions) Part Three: Message Header Extensions for Non-ASCII Text
                        - RFC 2048 - Multipurpose Internet Mail Extensions (MIME) Part Four: Registration Procedures
                        - RFC 2049 - Multipurpose Internet Mail Extensions (MIME) Part Five: Conformance Criteria and Examples

                        My suggestion is to look at RFC2045 to get an idea of the basic standard and then have a look (using your regular mail reader) at some full blown raw mail messages in your mailbox. Most e-mail readers have an option to display an e-mail in its original or raw format.
                        • 9. Re: Question about POP3 and PL/SQL
                          622829
                          I think you're right. I wanted to do this quickly, but I guess doing it better is more important. I'll probably go the UTL_TCP route. So I got a couple of follow-ups.

                          If your code is looking for a specific e-mail, it may
                          be the 11th or 32nd mail in the POP3 mail box. Using
                          Not any more. I have to go through all of them. If the email's a returned undeliverable email, I just delete it. If the email's a genuine reply to the address that I'm checking, then I take all the relevant info (i.e., From, Subject, Body, etc.), add it to a table, then delete the email.



                          So looking at your function and the POP RFC, you basically pass in the username, password, and message number, and it retrieves the contents of the email, right? Interesting. So I could create a procedure then that calls your function in a loop, gets the email, looks at it to see if it's a returned undeliverable email (if so delete it), if not, get the relevant info and delete it. Is that right?

                          Question though, I'm not familiar with this bit of code:

                          return TStrings pipelined


                          What exactly is that doing?
                          • 10. Re: Question about POP3 and PL/SQL
                            229023
                            return TStrings pipelined. What exactly is that doing?
                            You may have to read on Pipelined table functions
                            • 11. Re: Question about POP3 and PL/SQL
                              Billy~Verreynne
                              >return TStrings pipelined
                              What exactly is that doing?

                              It returns the SQL user defined data type called TStrings. This is defined as:
                              create or replace type TStrings as table of varchar2(4000);

                              A collection (or dynamic array) of strings.

                              The function is a pipeline table - meaning that it looks like a SQL table to the SQL engine when accessed via the SQL TABLE() function.

                              I would however not implement a POP3 interface in Oracle as a pipeline table function. The SQL varchar2 data type is restricted to 4000 bytes - and this will be a severe limitation dealing with some e-mails (especially attachments).

                              Using a plain PL/SQL interface and writing the e-mail into a CLOB would be the correct approach.

                              One can then perhaps wrap that into a pipeline table for the odd adhoc and quick SQL SELECT access to display all/some of an e-mail in a POP3 mail drop.

                              I used a pipeline in the sample code as it provides the easiest way for me to debug the code without having to use the primitive and restrictive DBMS_OUTPUT interface.
                              • 12. Re: Question about POP3 and PL/SQL
                                622829
                                Cool. The TStrings stuff now makes sense (and also why not to use them in this situation).

                                I would however not implement a POP3 interface in
                                Oracle as a pipeline table function. The SQL varchar2
                                data type is restricted to 4000 bytes - and this will
                                be a severe limitation dealing with some e-mails
                                (especially attachments).
                                Makes sense. We're not dealing with attachments, but I can still see how the 4k limit could be a problem.


                                Using a plain PL/SQL interface and writing the e-mail
                                into a CLOB would be the correct approach.
                                Makes sense. And actually, after reading through that RFC, taking out all the tstring and pipe stuff makes that code pretty readable and straightforward now.

                                I'll get cracking on this now.
                                • 13. Re: Question about POP3 and PL/SQL
                                  622829
                                  OK. I started with your code and made some modifications. Made it a procedure instead of a function. Got rid of all the pipelining stuff. Threw in some extra checks to make sure there are messages in the queue, but I'm getting an error: Why would these lines be causing errors while trying to compile my procedure:

                                  E_POP3_ERROR exception;
                                  E_READ_TIMEOUT exception;
                                  pragma exception_init( E_READ_TIMEOUT, -29276 );


                                  Here's the error:

                                  Compilation failed,line 6 (12:38:20)
                                  PLS-00103: Encountered the symbol "; E_POP3_ERROR exception; E_READ_TIMEOUT exception; pragm" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || member SUBMULTISET_ The symbol ". was inserted before "; E_POP3_ERROR exception; E_READ_TIMEOUT exception; pragm" to continue. Compilation failed,line 12 (12:38:20)

                                  PLS-00103: Encountered the symbol "FROM" when expecting one of the following: ( - + case mod new null avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe 12 (12:38:20)

                                  PLS-00103: Encountered the symbol "; password varchar2 := " when expecting one of the following: . ( * @ % & = - + ; < / > at in i



                                  Here's my procedure (a version of your function):

                                  create or replace procedure SER_POP3_PROCESSING
                                  is
                                  POP3_SERVER constant varchar2(50) := 'mail-from.blah.com';
                                  POP3_PORT constant number := 110;
                                  POP3_TIMEOUT constant number := 5;
                                  POP3_OK constant varchar2(10) := '+OK';

                                  E_POP3_ERROR exception;
                                  E_READ_TIMEOUT exception;
                                  pragma exception_init( E_READ_TIMEOUT, -29276 );

                                  userName varchar2 := 'sent@mail-from.blah.com';
                                  password varchar2 := 'blahblahblah';
                                  msgNum number := 1;
                                  total_msg number := 0;

                                  socket UTL_TCP.connection;
                                  line varchar2(4000);
                                  msg_tmp clob;
                                  bytes integer;

                                  -- send a POP3 command
                                  -- (we expect each command to respond with a +OK)
                                  function WriteToPop( command varchar2 ) return varchar2 is
                                  len integer;
                                  resp varchar2(4000);

                                  begin
                                  len := UTL_TCP.write_line( socket, command );
                                  UTL_TCP.Flush( socket );

                                  -- using a hack to check the popd response
                                  len := UTL_TCP.read_line( socket, resp );

                                  if (SUBSTR(resp,1,3) != POP3_OK) then
                                  raise E_POP3_ERROR;
                                  end if;

                                  return( resp );
                                  end;



                                  begin

                                  -- open a socket connection to the POP3 server
                                  socket := UTL_TCP.open_connection(
                                  remote_host => POP3_SERVER,
                                  remote_port => POP3_PORT,
                                  tx_timeout => POP3_TIMEOUT,
                                  charset => 'US7ASCII'
                                  );


                                  -- read the server banner/response from the pop3 daemon
                                  line := UTL_TCP.get_line(socket);


                                  -- Testing whether the connection was made successfully
                                  if (SUBSTR(line,1,3) != POP3_OK) then
                                  raise E_POP3_ERROR;

                                  else

                                  -- authenticate with the POP3 server using the USER and PASS commands
                                  line := WriteToPop('USER ' || userName);
                                  line := WriteToPop('PASS ' || password);


                                  -- this section of code retrieves the total number of messages in the queue
                                  line := WriteToPop('STAT');
                                  line := substr(line, 5, instr(line, ' ', 5)-5);
                                  total_msg := to_number(line);


                                  --
                                  -- This is where we retrieve the specific email message and start
                                  -- parsing through the data to determine what to do with the message
                                  -- (i.e., delete it (if it's a returned undeliverable message), copy
                                  -- contents (i.e., SUBJECT, FROM, BODY, etc.), etc.), assuming that there
                                  -- are messages in the queue.
                                  --
                                  if (total_msg > 0) then
                                  LOOP

                                  line := WriteToPop('RETR ' || msgNum);
                                  loop
                                  bytes := UTL_TCP.Available( socket );
                                  if bytes > 0 then
                                  bytes := UTL_TCP.read_line( socket, line );
                                  msg_tmp := msg_tmp || ':::::' || line;
                                  end if;

                                  exit when bytes = 0;
                                  end loop;


                                  -- this is where we parse through the message to get the contents






                                  if (msgNum >= total_msg) then
                                  exit;
                                  end if;

                                  msgNum := msgNum + 1; -- incrementing message counter var
                                  msg_tmp := ''; -- resetting var that stores message contents

                                  END LOOP;
                                  end if; -- checking if (total_msg > 0)



                                  -- close connection.
                                  response := WriteToPop('QUIT');

                                  UTL_TCP.close_connection( socket );

                                  end if; -- checking whether connection was made successfully

                                  end;

                                  Message was edited by:
                                  taneal

                                  Disregard this last post. I found the typo causing my error. I had an extra single quote after the server name in my variable declaration.
                                  • 14. Re: Question about POP3 and PL/SQL
                                    622829
                                    Well, I got sidetracked with another project I had to finish, but just came back and finished this bit of code up. Thanks to Billy for the initial bit of code which I took and modified to have it do what I wanted. Basically, just plug in the appropriate server and login info, and this procedure will pluck out the To part, From part, Subject, and the Body of the email message....

                                    create or replace procedure POP3_PROCESSING
                                    is
                                    POP3_SERVER constant varchar2(50) := 'mail.from-blahblahblah.com';
                                    POP3_PORT constant number := 110;
                                    POP3_TIMEOUT constant number := 5;
                                    POP3_OK constant varchar2(10) := '+OK';

                                    E_POP3_ERROR exception;
                                    E_READ_TIMEOUT exception;
                                    pragma exception_init( E_READ_TIMEOUT, -29276 );

                                    userName varchar2(4000) := 'sent@from-blahblahblah.com';
                                    password varchar2(4000) := 'blahblahblah';
                                    msgNum number := 1;
                                    total_msg number := 0;

                                    socket UTL_TCP.connection;
                                    line varchar2(4000);
                                    msg_id number;
                                    msg_from varchar2(4000) := '';
                                    msg_to varchar2(4000) := '';
                                    msg_sub varchar2(4000) := '';
                                    msg_body clob := NULL;
                                    bytes integer;
                                    hyphen_checker number := 0;
                                    subject_read_indicator number := 0;
                                    marked_for_deletion number := 0;


                                    -- send a POP3 command
                                    -- (we expect each command to respond with a +OK)
                                    function WriteToPop( command varchar2 ) return varchar2 is
                                    len integer;
                                    resp varchar2(4000);

                                    begin
                                    len := UTL_TCP.write_line( socket, command );
                                    UTL_TCP.Flush( socket );

                                    -- using a hack to check the popd response
                                    len := UTL_TCP.read_line( socket, resp );

                                    if (SUBSTR(resp,1,3) != POP3_OK) then
                                    raise E_POP3_ERROR;
                                    end if;

                                    return( resp );
                                    end;



                                    begin

                                    -- open a socket connection to the POP3 server
                                    socket := UTL_TCP.open_connection(
                                    remote_host => POP3_SERVER,
                                    remote_port => POP3_PORT,
                                    tx_timeout => POP3_TIMEOUT,
                                    charset => 'US7ASCII'
                                    );


                                    -- read the server banner/response from the pop3 daemon
                                    line := UTL_TCP.get_line(socket);


                                    -- Testing whether the connection was made successfully
                                    if (SUBSTR(line,1,3) != POP3_OK) then
                                    raise E_POP3_ERROR;

                                    else

                                    -- authenticate with the POP3 server using the USER and PASS commands
                                    line := WriteToPop('USER ' || userName);
                                    line := WriteToPop('PASS ' || password);


                                    -- this section of code retrieves the total number of messages in the queue
                                    line := WriteToPop('STAT');
                                    line := substr(line, 5, instr(line, ' ', 5)-5);
                                    total_msg := to_number(line);


                                    --
                                    -- This is where we retrieve the specific email message and start
                                    -- parsing through the data to determine what to do with the message
                                    -- (i.e., delete it (if it's a returned undeliverable message), copy
                                    -- contents (i.e., SUBJECT, FROM, BODY, etc.), etc.), assuming that there
                                    -- are messages in the queue.
                                    --
                                    if (total_msg > 0) then
                                    LOOP -- loop through all messages

                                    line := WriteToPop('RETR ' || msgNum);
                                    loop -- loop through all parts of current message
                                    bytes := UTL_TCP.Available( socket );

                                    if bytes > 0 then
                                    bytes := UTL_TCP.read_line( socket, line );

                                    -- If FROM part matches, then we know it was a returned
                                    -- undeliverable email, so we delete it, and move to the
                                    -- next message.
                                    if instr(upper(trim(line)), upper(trim('From: Mail Delivery System <serveradmin@Mailer-Daemon>'))) > 0 then
                                    line := WriteToPop('DELE ' || msgNum);
                                    marked_for_deletion := 1;

                                    else -- extracting the various parts of the email message

                                    if instr(upper(trim(substr(line, 1, 13))), upper(trim('Return-path: '))) > 0 then
                                    line := trim(replace(line, 'Return-path: '));
                                    line := replace(line, '<');
                                    line := replace(line, '>');
                                    msg_from := line;

                                    elsif instr(upper(trim(substr(line, 1, 13))), upper(trim('Envelope-to: '))) > 0 then
                                    line := trim(replace(line, 'Envelope-to: '));
                                    msg_to := line;

                                    elsif instr(upper(trim(substr(line, 1, 9))), upper(trim('Subject: '))) > 0 then
                                    line := trim(replace(line, 'Subject: '));
                                    msg_sub := line;
                                    subject_read_indicator := 1;

                                    end if; -- performing extractions for email message components

                                    end if; -- checking if email is returned undeliverable or an actual reply from user

                                    end if; -- checking if bytes > 0


                                    -- We set this flag inside of the if statement that processes the Subject
                                    -- line because we know the next line will be the beginning of the Body
                                    if (subject_read_indicator > 0) then

                                    if (msg_body is NULL) then
                                    msg_body := 'Body of message: ';
                                    else

                                    if ((upper(trim(line)) != '') or (instr(upper(trim(substr(line, 1, 8))), upper(trim('Content-'))) = 0)) then

                                    if ((upper(substr(trim(line), 1, 2)) = '--') and (length(line) > 10)) then
                                    hyphen_checker := hyphen_checker + 1;

                                    else
                                    if (hyphen_checker = 1) then
                                    msg_body := msg_body || ' ' || line;
                                    end if;

                                    end if; -- checking if line begins with "--"

                                    end if; -- only if it passes our checks to we add the contents to our msg body var

                                    end if; -- checking if msg_body var has already been set

                                    end if; -- checking if (subject_read_indicator > 0)


                                    exit when bytes = 0;
                                    end loop; -- loop through all parts of current message



                                    if (marked_for_deletion = 0) then

                                    -- insert code here to take our variables and do whatever you
                                    -- want with them

                                    end if; -- adding email message to queue if it wasn't marked for deletion



                                    -- Resetting our variables that store the parts of the email
                                    msg_from := '';
                                    msg_to := '';
                                    msg_sub := '';
                                    msg_body := NULL;
                                    subject_read_indicator := 0;
                                    marked_for_deletion := 0;
                                    hyphen_checker := 0;


                                    if (msgNum >= total_msg) then
                                    exit;
                                    end if;

                                    msgNum := msgNum + 1; -- incrementing message counter var


                                    END LOOP; -- loop through all messages
                                    end if; -- checking if (total_msg > 0)



                                    -- close connection.
                                    line := WriteToPop('QUIT');

                                    UTL_TCP.close_connection( socket );

                                    end if; -- checking whether connection was made successfully

                                    end;
                                    1 2 Previous Next