3 Replies Latest reply: Feb 10, 2013 11:54 PM by Billy~Verreynne RSS

    ORA-29279 Error using SYS.UTL_SMTP package

    EZGms
      Hi,

      Im having a lot of problems when I try to send an email message using SYS.UTL_SMTP package:
      my code is
      create or replace
      procedure P_EMAIL_DW_OPERATORS 
       is
                  type TStrings is table of varchar2(1000);
          
                  smtpSender      varchar2(100)   := 'sender.es@company.com';
                  smtpRecipient   varchar2(100)   := 'myemail@company.com';
                  mailSubject     varchar2(100)   := 'CRM-Alert';
                  smtpConn         UTL_SMTP.connection;
         
                  cursor cur is
                  
                  
                         select  '<tr bgcolor = ''silver'' align = ''center'' ><td>'||'OPERATOR'||'</td><td>'||
                                 'ID_CFG_OPERATOR'||'</td><td>'||'DW_TIMESTAMP'||
                                 '</td></tr>'
                                 from dual
                            union all   
                         select  '<tr><td>'||operator||'</td><td>'||
                                 id_cfg_operator||'</td><td>'||dw_timestamp||
                                 '</td></tr>'
                         from    dw_bi_operators
                         where to_char(dw_timestamp,'dd-mm-yy') = to_char(sysdate,'dd-mm-yy') or
                         to_char(dw_timestamp,'dd-mm-yy') = to_char(sysdate - 1,'dd-mm-yy');
         
                 mailBody        TStrings;
         
         BEGIN
                 smtpConn := UTL_SMTP.open_connection( 'Server name', port);
                 
                 UTL_SMTP.helo( smtpConn, 'Server name' ); -- // this needs to be your Oracle server hostname or IP!
                 
                 UTL_SMTP.command (smtpConn, 'AUTH LOGIN');
                 UTL_SMTP.command ( smtpConn, UTL_RAW.cast_to_varchar2 ( UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('sender.es@company.com')) ) );
                 UTL_SMTP.command ( smtpConn, UTL_RAW.cast_to_varchar2 ( UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('password')) ) );
                 
                 UTL_SMTP.mail( smtpConn, smtpSender );
                 UTL_SMTP.rcpt( smtpConn, smtpRecipient );
         
                 -- can be done using an implicit cursor instead
                 open cur;
                 fetch cur bulk collect into mailBody;
                 close cur;
         
                 -- start sending mail body using the data command
                 UTL_SMTP.open_data( smtpConn );
         
                 -- write header
                 UTL_SMTP.write_data( smtpConn, 'MIME-Version: 1.0'||utl_tcp.CRLF );
                 UTL_SMTP.write_data( smtpConn, 'Content-Type: text/html'||utl_tcp.CRLF );
                 UTL_SMTP.write_data( smtpConn, 'From: CRM Iberia Alert'||utl_tcp.CRLF);
                 --||smtpSender||utl_tcp.CRLF );
                 UTL_SMTP.write_data( smtpConn, 'To: '||smtpRecipient||utl_tcp.CRLF );
                 UTL_SMTP.write_data( smtpConn, 'Subject: '||mailSubject||utl_tcp.CRLF );
         
                 -- empty line between header and rest of mail body
                 UTL_SMTP.write_data( smtpConn, utl_tcp.CRLF );
         
                 -- now write the HTML
                 UTL_SMTP.write_data( smtpConn, '<html><table border = 2>'||utl_tcp.CRLF );
                 for i in 1..mailBody.Count
                 loop
                         UTL_SMTP.write_data( smtpConn, mailBody(i)||utl_tcp.CRLF );
                end loop;
                 UTL_SMTP.write_data( smtpConn, '</table></html>'||utl_tcp.CRLF );
         
                 -- close the data command
                 UTL_SMTP.close_data( smtpConn );
         
                 UTL_SMTP.quit( smtpConn );
         end P_EMAIL_DW_OPERATORS;
      and the output error is:
      Conectando a la base de datos .
      ORA-29279: error permanente de SMTP: 504 5.7.4 Unrecognized authentication type
      ORA-06512: en "SYS.UTL_SMTP", línea 21
      ORA-06512: en "SYS.UTL_SMTP", línea 99
      ORA-06512: en "SYS.UTL_SMTP", línea 159
      ORA-06512: en "DWARE.P_EMAIL_DW_OPERATORS", línea 33
      ORA-06512: en línea 2
      El proceso ha terminado.
      Desconectando de la base de datos.
      the system administrator said to me that the server is using an Encryption method: TLS
      but I dont kow what can I do to resolve the issue

      Could anyone help me

      Thanks in advanced
        • 1. Re: ORA-29279 Error using SYS.UTL_SMTP package
          Billy~Verreynne
          EZGms wrote:

          ORA-29279: error permanente de SMTP: 504 5.7.4 Unrecognized authentication type
          The SMTP server does not seem to support the AUTH LOGIN command.
          the system administrator said to me that the server is using an Encryption method: TLS
          but I dont kow what can I do to resolve the issue
          If Transport Layer Security, then UTL_SMTP cannot be used as far as I know.

          SMTP application protocol is clear text. Which is why it is unsecure. The entire conversation can be read (in clear text) by another party that eavesdrop on the conversation. The AUTH LOGIN command is to verify the user for sending mail. However, the actual wire data is not encrypted and still send as clear text.

          TLS encrypts the wire traffic. This is however more complex ito handshaking and authentication. The UTL_HTTP package support TLS/SSL (https protocol). But beyond that, I'm not aware of other application interface packages in PL/SQL that Oracle supplies, that support TLS.
          • 2. Re: ORA-29279 Error using SYS.UTL_SMTP package
            stefan nebesnak
            EZGms wrote:

            the system administrator said to me that the server is using an Encryption method: TLS
            but I dont kow what can I do to resolve the issue
            As of Oracle version _11.2.0.2_, the OPEN command was extended to allow use of wallets and introduced the UTL_SMTP.STARTTLS procedure which allows us to implement SSL/TLS.
            declare
                l_connection utl_smtp.connection;
            begin
                l_connection := utl_smtp.open_connection(
                                    host => 'smtp.gmail.com',
                                    port => 587,
                                    wallet_path => 'file:/oracle/email',
                                    wallet_password => 'email.password',
                                    secure_connection_before_smtp => false);
            
                utl_smtp.ehlo( l_connection, 'smtp.gmail.com');
                utl_smtp.starttls(l_connection);
            
                utl_smtp.auth(l_connection,
                               'google_user',
                               'google_password');
            end;
            
            --http://arkatec.wordpress.com/2011/08/15/sending-email-using-oracle-database-and-google-mail-service/
            <li>docs.oracle.com</li>
            <li>experts-exchange.com</li>
            • 3. Re: ORA-29279 Error using SYS.UTL_SMTP package
              Billy~Verreynne
              Thanks Stefan - was not aware that the newer version of UTL_SMTP has been updated for TLS support.