This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jun 16, 2008 1:42 PM by 622829 RSS

Question about POP3 and PL/SQL

622829 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    JensPetersen Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    >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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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