Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29279: SMTP permanent error: 501 #5.5.2 syntax error (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
ORA-29279: SMTP permanent error: 501 #5.5.2 syntax error [message #324875] Wed, 04 June 2008 07:09 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I have the below code to send a mail, it works fine when i send a mail to a single person, but throws up the error shown below the code when i am trying to send it to multiple people.

What is the problem?

PROCEDURE SEND(p_mail_host    IN VARCHAR2,
                       p_sender       IN VARCHAR2,
                       p_recp_email   IN VARCHAR2,
                       p_cc_email     IN VARCHAR2,
                       p_subj         IN VARCHAR2,
                       p_message      IN VARCHAR2,
                       send_msg_status  OUT VARCHAR2)
IS
        v_error_text    VARCHAR2(2000);
        n_start         PLS_INTEGER;
        n_end           PLS_INTEGER;
        v_address       VARCHAR2(200);
        p_delim         VARCHAR2(2) := ';';

    conn_rec    UTL_SMTP.Connection;


   PROCEDURE write_data(p_Conn_rec  IN OUT UTL_SMTP.Connection,
                        pv_Text     IN     VARCHAR2)
   IS
   BEGIN
       utl_smtp.write_data(p_Conn_rec, pv_Text || UTL_TCP.CRLF);
   END Write_Data;

BEGIN

      -- Open the SMTP connection.
      Conn_rec := utl_smtp.open_connection(p_mail_host, 25);

      -- Set SMTP server, sender, and recipient(2).

     utl_smtp.helo(Conn_rec, p_mail_host);
     utl_smtp.mail(Conn_rec, p_sender);
     utl_smtp.rcpt(Conn_rec, p_recp_email);

     IF p_recp_email IS NOT NULL THEN
       n_start := 1;
       LOOP
          -- Find next delimiter.
          n_end   := INSTR(p_recp_email||p_delim,p_delim, n_start+1);
          EXIT WHEN n_end = 0;
          -- Extract the next email address.
          v_address := SUBSTR(p_recp_email||p_delim, n_start, n_end-n_start);
          v_address := LTRIM(v_address,p_delim);        -- Strip possible leading delimiters.
          v_address := LTRIM(RTRIM(v_address));         -- Strip spaces.

          -- Set SMTP recipient.
          utl_smtp.rcpt(Conn_rec, v_address);

          n_start := n_end + 1;
       END LOOP;

   END IF;

    IF p_cc_email IS NOT NULL THEN
       n_start := 1;
       LOOP
          -- Find next delimiter.
          n_end   := INSTR(p_cc_email||p_delim,p_delim, n_start+1);
          EXIT WHEN n_end = 0;
          -- Extract the next email address.
          v_address := SUBSTR(p_cc_email||p_delim, n_start, n_end-n_start);
          v_address := LTRIM(v_address,p_delim);        -- Strip possible leading delimiters.
          v_address := LTRIM(RTRIM(v_address));         -- Strip spaces.

          -- Set SMTP recipient.
          utl_smtp.rcpt(Conn_rec, v_address);

          n_start := n_end + 1;
       END LOOP;
    END IF;

     utl_smtp.open_data(Conn_rec);

     utl_smtp.write_data(conn_rec, 'From' || ': ' || p_sender || utl_tcp.CRLF);
     utl_smtp.write_data(conn_rec, 'To' || ': ' || p_recp_email || utl_tcp.CRLF);

      IF p_cc_email IS NOT NULL
      THEN
         Write_Data(Conn_rec, 'Cc: ' || p_cc_email);
      END IF;

     utl_smtp.write_data(conn_rec, 'Subject' || ': ' || p_subj || utl_tcp.CRLF);
     utl_smtp.write_data(conn_rec,'content-type: text/html; charset=us-ascii');
     utl_smtp.write_data(conn_rec, utl_tcp.CRLF || p_message);
     utl_smtp.close_data(Conn_rec);
     utl_smtp.quit(Conn_rec);

     send_msg_status := 'PASS';

EXCEPTION
   when others then
        send_msg_status := 'FAIL';
        v_error_text := SQLERRM;
        dbms_output.put_line(v_error_text);
--        LOG_ERRORS(null,v_error_text,null,null,null,sysdate);

END;


I get this error when i try to send it to multiple people delimited by a ';'

for ex:abc@AS.com;abcde@AS.com;ghij@AS.com

ORA-29279: SMTP permanent error: 501 #5.5.2 syntax error
Re: ORA-29279: SMTP permanent error: 501 #5.5.2 syntax error [message #324897 is a reply to message #324875] Wed, 04 June 2008 08:07 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Check this might give a solution to your problem
Re: ORA-29279: SMTP permanent error: 501 #5.5.2 syntax error [message #324898 is a reply to message #324875] Wed, 04 June 2008 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I get this error when i try to send it to multiple people delimited by a ';'
stop poking yourself in the eye.
use comma as the delimiter.
Re: ORA-29279: SMTP permanent error: 501 #5.5.2 syntax error [message #482842 is a reply to message #324898] Tue, 16 November 2010 03:17 Go to previous message
rohinisimha
Messages: 1
Registered: November 2010
Junior Member
Semicolons and Commas dont help. Use Angle Brackets.. Eg. '<abc@gmail.com>,<xyz@gmail.com>' No need of looping Smile
Previous Topic: Reconciling 2 values with a tolerance - best formula?
Next Topic: Question on SQL using union
Goto Forum:
  


Current Time: Wed Dec 07 18:42:49 CST 2016

Total time taken to generate the page: 0.11603 seconds