1 2 Previous Next 27 Replies Latest reply: Apr 8, 2013 11:43 AM by AlbertoFaenza RSS

    Need to read Email from Lotusnotes and Outlook using Oracle

    San
      Dear All,

      My database version Oracle 10.2

      I need to read Email from Lotusnotes and Outlook using Oracle.

      I have tried with below link
      http://sourceforge.net/projects/plsqlmailclient/
      The problem is i am getting some java error.

      Can anyone suggest me to proceed further.

      Cheers,
      San
        • 1. Re: Need to read Email from Lotusnotes and Outlook using Oracle
          AlbertoFaenza
          San wrote:
          Dear All,

          My database version Oracle 10.2

          I need to read Email from Lotusnotes and Outlook using Oracle.

          I have tried with below link
          http://sourceforge.net/projects/plsqlmailclient/
          The problem is i am getting some java error.

          Can anyone suggest me to proceed further.

          Cheers,
          San
          Hi,

          as reported from AskTom email in PL/SQL
          How can I read email content from PL/SQL..?? Is it possible through Oracle..if possible ..can you
          please guide me through the process...!!!

          <b>Followup</b> June 2, 2009 - 7am Central time zone:
          you would typically load a java jar file into the database (the java mail api for example) as implementing the various email protocols - POP, IMAP - can be very tedious.
          you can do it in plsql (utl_tcp would let you do it), but you'd have to read about the protocols (POP and IMAP) and then implement them.
          I suggest getting a java programmer to do this particular feat.
          Regards.
          Al
          • 2. Re: Need to read Email from Lotusnotes and Outlook using Oracle
            San
            Hi,

            Thanks for your reply. I have checked with that link but nothing in detail. Can anyone give me the steps i need to do for achieve this??


            Cheers,
            San
            • 3. Re: Need to read Email from Lotusnotes and Outlook using Oracle
              AlbertoFaenza
              San wrote:
              Hi,

              Thanks for your reply. I have checked with that link but nothing in detail. Can anyone give me the steps i need to do for achieve this??


              Cheers,
              San
              Hi,

              as Tom Kyte mentioned (I copied in my answer) you have better to do it in Java.
              You can then publish your java class to a stored procedure so you can use it from Oracle.

              JavaMail API provides a platform-independent and protocol-independent framework to build mail and messaging applications. Definitely easier than doing it in PL/SQL.

              Can you give us more information? How are you going to implement this in Oracle?
              Do you expect a procedure/function getting some information?

              You request is quite generic.

              Regards.
              Al

              Edited by: Alberto Faenza on Nov 8, 2012 6:38 PM
              • 4. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                San
                Hi,
                Ya i am planning to develop a CRM application using Oracle APEX. My customer mail will come to my inbox. I need to read that mail and store the same into a table and then need to show the same in My front end screen.My user will read the mail and send the reply.

                I will recieve nearly 200 to 300 mails for a day. i have 10 users in my desk. i am going to share the mail with 10 users in round robin method.

                I am looking for a procedure or function or package , my goal will read the mail and store the data.

                I used the above link which i mentioned, i hope that will work but my bad time i am using SSL connection so its not allowed to read. They also providing the solution for SSL connection, for that solution i need oracle 11g.

                DBMS_JAVA.set_property function will handle the SSL problem, but Still i am using Oracle 10.2 Set_property function is not present in 10g
                • 5. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                  AlbertoFaenza
                  Hi,

                  I had a look at http://plsqlmailclient.sourceforge.net/ and I think this should do the job.

                  Which kind of Java error are you getting?

                  Have you granted the Java permission via DBMS_JAVA.GRANT_PERMISSION procedure?

                  Regards.
                  Al
                  • 6. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                    San
                    I am getting the below error
                    ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: java.security.ProviderException: nextBytes() failed
                    • 7. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                      AlbertoFaenza
                      From http://plsqlmailclient.sourceforge.net/apidoc/:



                      Note for SSL users:
                      SSL users might encounter an error message <b>ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: java.security.ProviderException: nextBytes() failed;</b> after successfully doing some operations via SSL. This most often occurs on server or virtual machines with no keyboard or mouse attached. The reason is that the /dev/random device has not enough "entropy" to generate the random numbers. A workaround is the have the java engine use "/dev/urandom" instead. This can be achieved with ....
                      declare
                        v_property varchar2(32767);
                      begin
                        c_property := dbms_java.set_property('java.security.egd', '/dev/urandom');
                      end;
                      
                      The database user needs a privilege in order to execute this call:
                      
                      begin
                        dbms_java.grant_permission( 
                          grantee           => '{Database Schema}', 
                          permission_type   => 'SYS:java.util.PropertyPermission',
                          permission_name   => 'java.security.egd', 
                          permission_action => 'read,write' 
                        );
                      end;
                      /
                      Regards.
                      Al

                      Edited by: Alberto Faenza on Nov 8, 2012 7:26 PM
                      • 8. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                        San
                        Hi,

                        I am talking about the same on my previous reply.

                        set_property Function avilable only in oracle 11G. i am using oracle 10g.

                        I tried with that soluton i am getting
                        set_property invalid indentifier
                        any other alternate solution for this??


                        Cheers,
                        San
                        • 9. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                          AlbertoFaenza
                          Hi,

                          I'm not really sure that you can do with a separate Java class.

                          You can try in this way:
                          begin
                            dbms_java.grant_permission( 
                              grantee           => '{Database Schema}', 
                              permission_type   => 'SYS:java.util.PropertyPermission',
                              permission_name   => 'java.security.egd', 
                              permission_action => 'read,write' 
                            );
                          end;
                          /
                          
                          CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "JavaSetProperty" AS
                          public class JavaSetProperty {
                              public static void main(String[] args) throws ClassNotFoundException {
                                  System.setProperty("java.security.egd", "/dev/urandom");
                              }
                          }
                          /
                          
                          CREATE PROCEDURE SETPROPERTY AS
                          LANGUAGE JAVA NAME 'JavaSetProperty.main(java.lang.String[])';
                          
                          --
                          -- If this works is setting Java system property and you probably have to execute all the time before calling your mail package
                          --
                          BEGIN
                            SETPROPERTY();
                          END;
                          /
                          If this one is not working then you need to modify the installation script install.sql to add the following line in connectToServerSSL method
                              System.setProperty("java.security.egd", "/dev/urandom");
                          This is how it should appear:
                            public static void connectToServerSSL(String sHost, int iPort, String sProtocol, String sUser, String sPass)
                            throws Exception {
                              if (bIsConnected) {
                                throw new Exception("Already connected to a mailserver - disconnect first");
                              }
                              
                              // Line added here below
                              System.setProperty("java.security.egd", "/dev/urandom");
                              
                              Properties props = new Properties();
                              props.setProperty("mail.store.protocol", sProtocol);
                              props.setProperty("mail."+sProtocol.toLowerCase()+".socketFactory.class", "javax.net.ssl.SSLSocketFactory");
                                          props.setProperty("mail."+sProtocol.toLowerCase()+".socketFactory.fallback", "false");
                          
                              oMailSession = Session.getDefaultInstance(props);
                              oMailStore   = oMailSession.getStore();
                              oMailStore.connect(sHost, iPort, sUser, sPass);
                              bIsConnected = true;
                            }
                          I'm not a Java expert but this might work.

                          Regards.
                          Al
                          • 10. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                            San
                            Hey Really Thanks,

                            Now i got move into my next level i hope

                            because code trying to connect server but i am getting the below error
                            Error report:
                            ORA-29532: Java call terminated by uncaught Java exception: javax.mail.AuthenticationFailedException: LOGIN invalid user name or password
                            ORA-06512: at "SMF_APEXRPS.MAIL_CLIENT", line 13
                            ORA-06512: at "SMF_APEXRPS.MAIL_CLIENT", line 32
                            ORA-06512: at line 4
                            29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
                            *Cause:    A Java exception or error was signaled and could not be
                                       resolved by the Java code.
                            *Action:   Modify Java code, if this behavior is not intended.
                            will validate and update you soon
                            • 11. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                              San
                              Hi ,

                              Now i over come the authentication error. But i am getting the new error which stats that
                              Error report:
                              ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: Unrecognized SSL message, plaintext connection?;
                                nested exception is: 
                                   javax.net.ssl.SSLException: Unrecognized SSL message, plaintext connection?
                              ORA-06512: at "SMF_APEXRPS.MAIL_CLIENT", line 13
                              ORA-06512: at "SMF_APEXRPS.MAIL_CLIENT", line 32
                              ORA-06512: at line 5
                              29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
                              *Cause:    A Java exception or error was signaled and could not be
                                         resolved by the Java code.
                              *Action:   Modify Java code, if this behavior is not intended.
                              cheers,
                              San
                              • 12. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                                San
                                I am using the another function to read an email from pop3 server
                                create or replace
                                FUNCTION pop3 (
                                   username   VARCHAR2,
                                   PASSWORD   VARCHAR2,
                                   msgnum     NUMBER
                                )
                                   RETURN tstrings PIPELINED
                                IS
                                   --POP3_SERVER             constant varchar2(19) := '127.0.0.1';
                                   pop3_server   CONSTANT VARCHAR2 (100)     := 'xxxxxx';
                                   pop3_port     CONSTANT NUMBER             := 110;
                                   --POP3_TIMEOUT            constant number := 10;
                                   pop3_ok       CONSTANT VARCHAR2 (10)      := '+OK';
                                   e_pop3_error           EXCEPTION;
                                   --E_READ_TIMEOUT  exception;
                                   --pragma exception_init( E_READ_TIMEOUT, -29276 );
                                   socket                 UTL_TCP.connection;
                                   line                   VARCHAR2 (30000);
                                   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 (30000);
                                   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
                                
                                 --UTL_TCP.CLOSE_CONNECTION (SOCKET);
                                 
                                   PIPE ROW ('pop3:' || pop3_server || ' port:' || pop3_port);
                                   -- Just to make sure there are no previously opened connections
                                   UTL_TCP.close_all_connections;
                                
                                   -- 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
                                   PIPE ROW (UTL_TCP.get_line (socket));
                                   -- authenticate with the POP3 server using the USER and PASS commands
                                   PIPE ROW ('USER ' || username);
                                   PIPE ROW (writetopop ('USER ' || username));
                                   PIPE ROW ('PASS ' || PASSWORD);
                                   PIPE ROW (writetopop ('PASS ' || PASSWORD));
                                   -- retrieve the specific message
                                   PIPE ROW ('RETR ' || msgnum);
                                   PIPE ROW (writetopop ('RETR ' || msgnum));
                                   --PIPE ROW( 'LIST '||msgNum ); PIPE ROW( WriteToPop('LIST '||msgNum) );
                                   PIPE ROW ('*** START OF INTERNET MESSAGE BODY ***');
                                
                                   LOOP
                                   dbms_output.put_line('entering');
                                      BYTES := UTL_TCP.available (socket);
                                
                                     IF BYTES > 0
                                      THEN
                                         BYTES := UTL_TCP.read_line (socket, line);
                                         line := REPLACE (line, CHR (13) || CHR (10), '');
                                         
                                         -- WILL HAVE TO USE PLSQL FUNCTIONS (HAVE BOOKMARKED) TO GET THE MAIL 
                                         -- IN THE PREFERRED FORMAT. CAN USE "REPLACE()"
                                
                                         IF LENGTH (line) = 1 AND line = '.'
                                         THEN
                                            PIPE ROW ('*** END OF INTERNET MESSAGE BODY ***');
                                         ELSE
                                            PIPE ROW (line);
                                         end if;
                                     END IF;
                                
                                      EXIT when length (LINE) = 1 and LINE = '.';
                                      -- PIPE ROW (line);
                                      
                                   END LOOP;
                                
                                   --PIPE ROW( '*** END OF INTERNET MESSAGE BODY ***' );
                                
                                   -- close connection
                                   PIPE ROW ('QUIT');
                                   PIPE ROW (writetopop ('QUIT'));
                                   UTL_TCP.CLOSE_CONNECTION (SOCKET);
                                EXCEPTION
                                   WHEN e_pop3_error
                                   THEN
                                      PIPE ROW ('There are no mails !');
                                END;
                                I am getting the below output .
                                21     pop3:sbssld1 port:110
                                75     "+OK Lotus Notes POP3 server version Release 8.0.1 ready on SBSSLD1/SBPSS.
                                "
                                47     USER Shanmugam.Doyensys@sundarambnpparibasfs.in
                                69     "+OK Shanmugam.Doyensys@sundarambnpparibasfs.in, your papers please.
                                "
                                13     PASS password
                                63     "+OK Shanmugam.Doyensys@sundarambnpparibasfs.in has 1 message.
                                "
                                6     RETR 1
                                17     "+OK 1546 octets
                                "
                                38     *** START OF INTERNET MESSAGE BODY ***
                                9     Subject: 
                                48     X-KeepSent: A231D6D0:8485FE4B-65257AB1:0022E60F;
                                23      type=4; name=$KeepSent
                                46     To: Shanmugam.Doyensys@sundarambnpparibasfs.in
                                53     X-Mailer: Lotus Notes Release 8.0.1 February 07, 2008
                                95     Message-ID: <OFA231D6D0.8485FE4B-ON65257AB1.0022E60F-65257AB1.0022E850@sundarambnpparibasfs.in>
                                48     From: Shanmugam.Doyensys@sundarambnpparibasfs.in
                                36     Date: Fri, 9 Nov 2012 11:51:14 +0530
                                90     X-MIMETrack: Serialize by POP3 Server on SBSSLD1/SBPSS(Release 8.0.1|February 07, 2008) at
                                23      11/09/2012 11:51:21 AM
                                17     MIME-Version: 1.0
                                37     Content-type: multipart/alternative; 
                                68     "     Boundary="0__=EABBF022DFB16ED68f9e8a93df938690918cEABBF022DFB16ED6""
                                27     Content-Disposition: inline
                                (null)     (null)
                                58     --0__=EABBF022DFB16ED68f9e8a93df938690918cEABBF022DFB16ED6
                                39     Content-type: text/plain; charset=UTF-8
                                33     Content-transfer-encoding: base64
                                (null)     (null)
                                (null)     (null)
                                76     DQoNCg0KUmVnYXJkcywNClNoYW5tdWdhbSBOYXRhcmFqYW4uDQpNb2IgOiA5NjI5MjUyNDI1DQpP
                                76     ZmYgOiAwNDQgMjI1MDQ3MDAgRXh0biA0Nzc5DQoNCiB+SWYgdGhlIGZhY3RzIGRvbuKAmXQgZml0
                                68     IHRoZSB0aGVvcnksIGNoYW5nZSB0aGUgZmFjdHMu4oCdIC0gQWxiZXJ0IEVpbnN0ZWlu
                                (null)     (null)
                                58     --0__=EABBF022DFB16ED68f9e8a93df938690918cEABBF022DFB16ED6
                                38     Content-type: text/html; charset=UTF-8
                                27     Content-Disposition: inline
                                33     Content-transfer-encoding: base64
                                (null)     (null)
                                76     PGh0bWw+PGJvZHk+DQo8cD5SZWdhcmRzLDxicj4NClNoYW5tdWdhbSBOYXRhcmFqYW4uPGJyPg0K
                                76     TW9iIDogOTYyOTI1MjQyNTxicj4NCk9mZiA6IDA0NCAyMjUwNDcwMCBFeHRuIDQ3Nzk8YnI+DQo8
                                76     YnI+DQogfklmIHRoZSBmYWN0cyBkb27igJl0IGZpdCB0aGUgdGhlb3J5LCBjaGFuZ2UgdGhlIGZh
                                60     Y3RzLuKAnSAtIEFsYmVydCBFaW5zdGVpbjxicj4NCjwvYm9keT48L2h0bWw+
                                (null)     (null)
                                60     --0__=EABBF022DFB16ED68f9e8a93df938690918cEABBF022DFB16ED6--
                                (null)     (null)
                                36     *** END OF INTERNET MESSAGE BODY ***
                                4     QUIT
                                42     "+OK Lotus Notes POP3 server signing off.
                                "
                                Now pls suggest me how can i store the sender mail id, receiver mail id, subject and body of the mail in a table.

                                So that i can display the saved data in the above table as a mail in my separate application.

                                Thanks in advance.

                                Cheers,
                                San
                                • 13. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                                  Billy~Verreynne
                                  So you found my old POP3 play code.. ;-)

                                  The only reason it was written as a pipeline was to demonstrate how a POP3 conversation works. There is no need for this approach in production code.

                                  You can simply create a string array and read the response from the RETR command into this array. Or you can create a CLOB and WriteAppend() into it. This is the content of the actual mail - aka the raw e-mail.

                                  Mail is raw and unstructured text data.

                                  Mail readers (like Outlook and Thunderbird and so on), have to parse this. You will need to do the same. The specifications governing the format of a raw e-mail are in memos RFC 2045, RFC 2046, RFC 2047, RFC 4288, RFC 4289 and RFC 2049. See http://en.wikipedia.org/wiki/MIME for details.

                                  Alternatively, you need to use an existing MIME parser. Perhaps there is a Java class that does this and be loaded into Oracle and wrapped for PL/SQL use.
                                  • 14. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                                    AlbertoFaenza
                                    San wrote:
                                    Hi ,

                                    Now i over come the authentication error. But i am getting the new error which stats that
                                    Error report:
                                    ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: Unrecognized SSL message, plaintext connection?;
                                    nested exception is: 
                                         javax.net.ssl.SSLException: Unrecognized SSL message, plaintext connection?
                                    ORA-06512: at "SMF_APEXRPS.MAIL_CLIENT", line 13
                                    ORA-06512: at "SMF_APEXRPS.MAIL_CLIENT", line 32
                                    ORA-06512: at line 5
                                    29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
                                    *Cause:    A Java exception or error was signaled and could not be
                                    resolved by the Java code.
                                    *Action:   Modify Java code, if this behavior is not intended.
                                    cheers,
                                    San
                                    Hi San,

                                    when you have such problems it should be nice if you could post the code you have executed.
                                    As the message suggested it seems you are not using an SSL connection.
                                    Since I don't know what you are passing as parameter, I can just suggest you someting:
                                    The directory sample contains sample code. The code to connect to the mail server is this:
                                    begin
                                      mail_client.connect_server(
                                        p_hostname => '^MAILSERV.',
                                        p_port     => ^MAILPORT.,
                                        p_protocol => mail_client.protocol_^MAILPROT.,
                                        p_userid   => '^MAILUSER.',
                                        p_passwd   => '^MAILPASS.',
                                        p_ssl => true
                                      );
                                    Try to set p_ssl parameter to false to see if that was your problem.

                                    Regards.
                                    Al
                                    1 2 Previous Next