3 Replies Latest reply on Dec 27, 2008 7:43 AM by UttamSingh

    ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)

    UttamSingh
      Hi,

      Can someone help me to find out the issue in the given pakg.
      I am tring to send email through this package but i am getting some errors:-

      create or replace package body mail_pkg
      as
      function address_email( p_string in varchar2,
      p_recipients in array ) return varchar2
      is
      l_recipients long;
      begin
      for i in 1 .. p_recipients.count
      loop
      utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
      if ( l_recipients is null )
      then
      l_recipients := p_string || p_recipients(i) ;
      else
      l_recipients := l_recipients || ', ' || p_recipients(i);
      end if;
      end loop;
      return l_recipients;
      end;

      procedure writeData( p_text in varchar2 )
      as
      begin
      if ( p_text is not null )
      then
      utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
      end if;
      end;

      procedure send( p_sender_email in varchar2,
      p_from in varchar2 ,
      p_to in array default array(),
      p_cc in array default array(),
      p_bcc in array default array(),
      p_subject in varchar2 ,
      p_body in long )
      is
      l_to_list long;
      l_cc_list long;
      l_bcc_list long;
      l_date     varchar2(255) default TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || ' +8:00 (GMT+8:00)';

      begin
           SELECT PARAM_VALUE INTO g_mailhost FROM IHR_PARAM_DATA
           WHERE PARAM_CODE = 'MAIL_HOST' AND
           ( ( CATEGORY = 'S' ) ) AND
           ( ( ( FROM_DATE IS NULL ) AND ( TO_DATE IS NULL ) ) OR
           ( ( FROM_DATE IS NULL ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) OR
           ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE IS NULL ) ) OR
           ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) ) AND
           SEQUENCE = ( SELECT MIN(SEQUENCE) FROM IHR_PARAM_DATA
                WHERE PARAM_CODE = 'MAIL_HOST' AND
                ( ( CATEGORY = 'S' ) ) AND
                ( ( ( FROM_DATE IS NULL ) AND ( TO_DATE IS NULL ) ) OR
                ( ( FROM_DATE IS NULL ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) OR
                ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE IS NULL ) ) OR
                ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE >= TRUNC(SYSDATE ) ) ) ) );

      g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
      utl_smtp.helo(g_mail_conn, g_mailhost);
      utl_smtp.command(g_mail_conn , 'auth login');
      utl_smtp.command(g_mail_conn ,
      mail_pkg.f_base64_encode_raw(utl_raw.cast_to_raw('mailuser')));
      utl_smtp.command(g_mail_conn ,
      mail_pkg.f_base64_encode_raw(utl_raw.cast_to_raw('******')));
      utl_smtp.mail(g_mail_conn, p_sender_email);
      l_to_list := address_email( 'To: ', p_to );
      l_cc_list := address_email( 'Cc: ', p_cc );
      l_bcc_list := address_email( 'Bcc: ', p_bcc );
      utl_smtp.open_data(g_mail_conn );

      writeData( 'Date: ' || l_date );
      writeData( 'From: ' || nvl( p_from, p_sender_email ) );
      writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );

      writeData( l_to_list );
      writeData( l_cc_list );

      utl_smtp.write_data( g_mail_conn, '' || g_crlf );
      utl_smtp.write_data(g_mail_conn, p_body );
      utl_smtp.close_data(g_mail_conn );
      utl_smtp.quit(g_mail_conn);
      end;
      end;


      I am getting the given below error when tring to execute the procedure even though my email and password are correct.

      ERROR at line 1:
      ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
      ORA-06512: at "SYS.UTL_SMTP", line 21
      ORA-06512: at "SYS.UTL_SMTP", line 99
      ORA-06512: at "SYS.UTL_SMTP", line 159
      ORA-06512: at "SYSTEM.SEND_MAIL", line 12
      ORA-06512: at line 1

      pls help;

      Thanks,
      Uttam Singh

      Edited by: Uttam Singh on Dec 26, 2008 7:56 PM
        • 1. Re: ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
          OrionNet
          Uttam,

          What's the email address, just an example will be good? You can try enclosing your email in angled brackets <myemail>.

          Regards
          • 2. Re: ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
            Aman....
            uttam,

            This error is due to wrong settings of SMTP server. Oracle , most likely has nothing to do with it. If I remember correctly, you are using Gmail for it. I guess that there is some issue with it. Try out with some other domain settings. I shall try to use the same with my own domain.In the meanwhile, have a read at these two link.
            http://www.williamrobertson.net/code/utilities.pkg.txt
            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2118740358226#9206460145191
            HTH
            Aman....
            • 3. Re: ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
              UttamSingh
              Hi,

              I am tring to send email through uttam@chauhanenterprises.com and my smtp server is mail.chauhanenterprises.com. It need authantication to relay on other domins. I can send email on same domin easily i.e. chauhanenterprises.com but unable to send on other domins like gmail.com and rediffmail.com.


              create or replace package body mail_pkg
              as
              function address_email( p_string in varchar2,
              p_recipients in array ) return varchar2
              is
              l_recipients long;
              begin
              for i in 1 .. p_recipients.count
              loop
              utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
              if ( l_recipients is null )
              then
              l_recipients := p_string || p_recipients(i) ;
              else
              l_recipients := l_recipients || ', ' || p_recipients(i);
              end if;
              end loop;
              return l_recipients;
              end;

              procedure writeData( p_text in varchar2 )
              as
              begin
              if ( p_text is not null )
              then
              utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
              end if;
              end;


              procedure send( p_sender_email in varchar2,
              p_from in varchar2 ,
              p_to in array default array(),
              p_cc in array default array(),
              p_bcc in array default array(),
              p_subject in varchar2 ,
              p_body in long )
              is
              l_to_list long;
              l_cc_list long;
              l_bcc_list long;
              l_date varchar2(255) default TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || ' +8:00 (GMT+8:00)';


              begin
              SELECT PARAM_VALUE INTO g_mailhost FROM IHR_PARAM_DATA --- value of PARAM_VALUE is mail.chauhanenterprises.com
              WHERE PARAM_CODE = 'MAIL_HOST' AND
              ( ( CATEGORY = 'S' ) ) AND
              ( ( ( FROM_DATE IS NULL ) AND ( TO_DATE IS NULL ) ) OR
              ( ( FROM_DATE IS NULL ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) OR
              ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE IS NULL ) ) OR
              ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) ) AND
              SEQUENCE = ( SELECT MIN(SEQUENCE) FROM IHR_PARAM_DATA
              WHERE PARAM_CODE = 'MAIL_HOST' AND
              ( ( CATEGORY = 'S' ) ) AND
              ( ( ( FROM_DATE IS NULL ) AND ( TO_DATE IS NULL ) ) OR
              ( ( FROM_DATE IS NULL ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) OR
              ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE IS NULL ) ) OR
              ( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE >= TRUNC(SYSDATE ) ) ) ) );


              g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
              utl_smtp.helo(g_mail_conn, g_mailhost);
              utl_smtp.command(g_mail_conn , 'auth login');
              utl_smtp.command(g_mail_conn ,
              mail_pkg.f_base64_encode_raw(utl_raw.cast_to_raw('uttam@chauhaneterprises.com')));
              utl_smtp.command(g_mail_conn ,
              mail_pkg.f_base64_encode_raw(utl_raw.cast_to_raw('******')));
              utl_smtp.mail(g_mail_conn, p_sender_email);
              l_to_list := address_email( 'To: ', p_to );
              l_cc_list := address_email( 'Cc: ', p_cc );
              l_bcc_list := address_email( 'Bcc: ', p_bcc );
              utl_smtp.open_data(g_mail_conn );


              writeData( 'Date: ' || l_date );
              writeData( 'From: ' || nvl( p_from, p_sender_email ) );
              writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );


              writeData( l_to_list );
              writeData( l_cc_list );


              utl_smtp.write_data( g_mail_conn, '' || g_crlf );
              utl_smtp.write_data(g_mail_conn, p_body );
              utl_smtp.close_data(g_mail_conn );
              utl_smtp.quit(g_mail_conn);
              end;
              end;

              Thanks,
              Uttam Singh