1 2 Previous Next 27 Replies Latest reply on Apr 8, 2013 11:43 AM by AlbertoFaenza Go to original post
      • 15. Re: Need to read Email from Lotusnotes and Outlook using Oracle
        San
        Hi,
        I am using SSL Connection only . I have executed the below code
        begin
        
          SETPROPERTY();
          
        MAIL_CLIENT.CONNECT_SERVER(
          p_hostname => 'xxxx',
          P_PORT     => 110,
          P_PROTOCOL => MAIL_CLIENT.PROTOCOL_imap,
          P_USERID   => 'xxx',
          P_PASSWD   => 'password',
          p_ssl      => true
        );
        END;
        when i execute the above query i am getting the below error
        Error report:
        ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: Connect failed;
          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 33
        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
        • 16. Re: Need to read Email from Lotusnotes and Outlook using Oracle
          San
          Thanks Billy . Sorry to ask this.

          Can you please provide me some samples for storing data in clob or array usiing retr function.
          • 17. Re: Need to read Email from Lotusnotes and Outlook using Oracle
            AlbertoFaenza
            San wrote:
            Hi,
            I am using SSL Connection only . I have executed the below code
            begin
            
            SETPROPERTY();
            
            MAIL_CLIENT.CONNECT_SERVER(
            p_hostname => 'xxxx',
            P_PORT     => 110,
            P_PROTOCOL => MAIL_CLIENT.PROTOCOL_imap,
            P_USERID   => 'xxx',
            P_PASSWD   => 'password',
            p_ssl      => true
            );
            END;
            when i execute the above query i am getting the below error
            Error report:
            ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: Connect failed;
            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 33
            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, are you sure about the port?
            Standard Incoming POP3 port: 110
            Standard Incoming IMAP port: 143
            SSL Incoming POP3 port: 995
            SSL Incoming IMAP port: 993
            If you are using port 110 then you have to set:
            begin
             
              SETPROPERTY();
              
            MAIL_CLIENT.CONNECT_SERVER(
              p_hostname => 'xxxx',
              P_PORT     => 110,
              P_PROTOCOL => MAIL_CLIENT.PROTOCOL_pop3,
              P_USERID   => 'xxx',
              P_PASSWD   => 'password',
              p_ssl      => false
            );
            END;
            Regards.
            Al

            Edited by: Alberto Faenza on Nov 9, 2012 11:33 AM
            1 person found this helpful
            • 18. Re: Need to read Email from Lotusnotes and Outlook using Oracle
              San
              Hi,

              Finally i connected to my mail server and i am able to read the mails.

              But manytime when i try to connect its giving me the error only. Suddenly its connected and i can read the mail.

              But the package will show the unread mails only. and also its not showing trail mail (forwared mail)

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

                Finally i connected to my mail server and i am able to read the mails.

                But manytime when i try to connect its giving me the error only. Suddenly its connected and i can read the mail.

                But the package will show the unread mails only. and also its not showing trail mail (forwared mail)

                Cheers,
                San.
                Hi San,

                I think POP3 is retrieving new mails by default but I cannot help you further.
                I'm not the author of this package nor I have ever used mail reading in PL/SQL.
                I prefer to use a proper email client for this.
                You may check on the documentation or try with IMAP protocol if supported by your mail server.
                In this case you need to change to IMAP protocol and port.
                If IMAP is using SSL try using port 993 and set p_ssl parameter to true.
                begin
                 
                  SETPROPERTY();
                  
                MAIL_CLIENT.CONNECT_SERVER(
                  p_hostname => 'xxxx',
                  P_PORT     => 143,
                  P_PROTOCOL => MAIL_CLIENT.PROTOCOL_imap,
                  P_USERID   => 'xxx',
                  P_PASSWD   => 'password',
                  p_ssl      => false
                );
                END;
                Regards.
                Al

                Edited by: Alberto Faenza on Nov 10, 2012 2:05 PM
                • 20. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                  1000092
                  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.




                  Hi Please Help Me to Overcome this Authenticate Error because i m also getting the Same Error.
                  after this code :


                  begn
                  mail_client.connect_server( p_hostname => 'mail.****.co.in',
                  p_port => 110,
                  p_protocol => 'pop3',
                  p_userid => 'abc@****.co.in.',
                  p_passwd => 'Abcd#12345', p_ssl => false );
                  mail_client.open_inbox;
                  dbms_output.put_line('Mailbox successfully opened.');
                  end;
                  begin


                  /
                  *
                  ERROR at line 1:
                  ORA-29532: Java call terminated by uncaught Java exception:
                  javax.mail.AuthenticationFailedException: authorization failed
                  ORA-06512: at "CORD.MAIL_CLIENT", line 5
                  ORA-06512: at "CORD.MAIL_CLIENT", line 34
                  ORA-06512: at line 2


                  How To Remove the Error Please Help.

                  Edited by: 997089 on Apr 3, 2013 11:15 PM
                  • 21. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                    APC
                    You are not using an Oracle built-in but a piece of open source software you downloaded from sourceforge. It is "free" but it is not without cost. The price is, you have to sort out any prioblems you have with the package (unless its author is prepared to help you).

                    So first of all, have you read everything in this thread? Have you followed all the advice given?

                    Plus, have you installed the package correctly? Have you got the infrastructure configured properly? Do you have all the necessary permissions?

                    Because, basically this is not a SQL or PL/SQL problem. It is a Java/OS/architecture problem.

                    Cheers, APC
                    • 22. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                      1000092
                      Hi APC,
                      Thank You Very much for your Reply and Help.

                      As Per your advice i read everything in all the thread regarding Read Mail in Oracle
                      I have installed the Package correctly and not getting error while installation.I have also given all permissions using this commands in SYS user

                      begin dbms_java.grant_permission('ABCD','java.net.SocketPermission','mail.****.co.in','connect,resolve'); end;
                      begin dbms_java.grant_permission( 'ABCD', 'SYS:java.util.PropertyPermission','java.security.egd', 'read,write' ); end;
                      begin dbms_java.grant_permission( 'ABCDD', 'java.util.PropertyPermission','java.security.egd', 'read,write' ); end;

                      begin dbms_java.grant_permission('SYS','SYS:java.net.SocketPermission','mail.****.co.in','connect,resolve'); end;
                      begin dbms_java.grant_permission( 'SYS', 'SYS:java.util.PropertyPermission','java.security.egd', 'read,write' ); end;

                      and for the Insfrastucture i can execute this funcation and i can read the mail properly.

                      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 Output after executing but when i am executing this in my ABCD user then i get the error Please Help

                      begn
                      mail_client.connect_server( p_hostname => 'mail.****.co.in',
                      p_port => 110,
                      p_protocol => 'pop3',
                      p_userid => 'abc@****.co.in.',
                      p_passwd => 'Abcd#12345', p_ssl => false );
                      mail_client.open_inbox;
                      dbms_output.put_line('Mailbox successfully opened.');
                      end;
                      begin


                      /
                      *
                      ERROR at line 1:
                      ORA-29532: Java call terminated by uncaught Java exception:
                      javax.mail.AuthenticationFailedException: authorization failed
                      ORA-06512: at "ABCD.MAIL_CLIENT", line 5
                      ORA-06512: at "ABCD.MAIL_CLIENT", line 34
                      ORA-06512: at line 2



                      Please Help me to get error Resolved.

                      Edited by: 997089 on Apr 7, 2013 8:16 PM
                      • 23. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                        sb92075
                        997089 wrote:
                        *
                        ERROR at line 1:
                        ORA-29532: Java call terminated by uncaught Java exception:
                        javax.mail.AuthenticationFailedException: authorization failed
                        ORA-06512: at "ABCD.MAIL_CLIENT", line 5
                        ORA-06512: at "ABCD.MAIL_CLIENT", line 34
                        ORA-06512: at line 2



                        Please Help me to get error Resolved.

                        Edited by: 997089 on Apr 7, 2013 8:16 PM
                        fix is to provide proper credentials

                        root cause & fix are external to Oracle.
                        Oracle is the victim; not the culprit.
                        • 24. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                          1000092
                          Thanks

                          I am Using Same user name , password and port number and domain name for this function and i am getting the output from it all unread mail in inbox.

                          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 want t know the reason for this error

                          ORA-29532: Java call terminated by uncaught Java exception:
                          javax.mail.AuthenticationFailedException: authorization failed
                          ORA-06512: at "ABCD.MAIL_CLIENT", line 5
                          ORA-06512: at "ABCD.MAIL_CLIENT", line 34
                          ORA-06512: at line 2
                          • 25. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                            sb92075
                            Einstein once said that the definition of insanity is doing the same thing over and over and expecting a different result.


                            Why do you post same content & expect different response?
                            • 26. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                              1000092
                              fix is to provide proper credentials




                              Whar Proper credintials you mean, please Explain i brief.
                              • 27. Re: Need to read Email from Lotusnotes and Outlook using Oracle
                                AlbertoFaenza
                                Hi,

                                if you have a problem you should open your own thread and not hijacking an old thread from San.

                                Please read SQL and PL/SQL FAQ
                                Additionally when you put some code or output please enclose it between two lines starting with {noformat}
                                {noformat}
                                
                                i.e.:
                                {noformat}
                                {noformat}
                                SELECT ...
                                {noformat}
                                {noformat}
                                
                                Regards.
                                Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                1 2 Previous Next