1 Reply Latest reply: Nov 19, 2012 10:51 AM by JustinCave RSS

    Email Configuration

    user568657
      Dear All,
      I am trying to send a mail using pl/sql package, but mail is not going, its throwing the below error.
      The configured ip is 192.168.5.44 . The same i am calling in the procedure

      ORA-29278: SMTP transient error: 421 Service not available
      ORA-06512: at "SYS.UTL_SMTP", line 20
      ORA-06512: at "SYS.UTL_SMTP", line 96
      ORA-06512: at "SYS.UTL_SMTP", line 138
      ORA-06512: at "APPS.XX_SEND_MAIL", line 21
      ORA-06512: at line 2

      If anyone provide a solution it will be much helpful.

      Coding used:



      --------------------------------------------------------------------------------
      CREATE OR REPLACE PROCEDURE APPS.xx_send_mail (
      p_to IN VARCHAR2,
      p_from IN VARCHAR2,
      p_subject IN VARCHAR2,
      p_text_msg IN VARCHAR2 DEFAULT NULL,
      p_html_msg IN VARCHAR2 DEFAULT NULL,
      p_smtp_host IN VARCHAR2,
      p_smtp_port IN NUMBER DEFAULT 25
      )
      AS
      l_mail_conn UTL_SMTP.connection;
      l_boundary VARCHAR2 (50) := '----=*#abc1234321cba#*=';
      BEGIN


      utl_tcp.close_all_connections;

      dbms_output.put_line('first begin ');



      l_mail_conn := UTL_SMTP.open_connection (p_smtp_host, p_smtp_port);
      dbms_output.put_line('before smtp host ');
      UTL_SMTP.helo (l_mail_conn, p_smtp_host);
      dbms_output.put_line('before from ');
      UTL_SMTP.mail (l_mail_conn, p_from);
      dbms_output.put_line('before second begin ');
      begin


      dbms_output.put_line('before to mail address ');


      UTL_SMTP.rcpt (l_mail_conn, p_to);


      dbms_output.put_line('after to mail address ');


      exception
      when others then
      dbms_output.put_line('sqlcode '||sqlcode||' '||' sqlerrm '||sqlerrm);
      end;


      dbms_output.put_line('after exception');


      UTL_SMTP.open_data (l_mail_conn);
      UTL_SMTP.write_data (l_mail_conn,
      'Date: '
      || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
      || UTL_TCP.crlf
      );
      UTL_SMTP.write_data (l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
      --UTL_SMTP.write_data (l_mail_conn, 'Cc: ' || p_cc || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn,
      'Content-Type: multipart/alternative; boundary="'
      || l_boundary
      || '"'
      || UTL_TCP.crlf
      || UTL_TCP.crlf
      );

      IF p_text_msg IS NOT NULL
      THEN
      UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data
      (l_mail_conn,
      'Content-Type: text/plain; charset="iso-8859-1"'
      || UTL_TCP.crlf
      || UTL_TCP.crlf
      );
      UTL_SMTP.write_data (l_mail_conn, p_text_msg);
      UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
      END IF;

      IF p_html_msg IS NOT NULL
      THEN
      UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn,
      'Content-Type: text/html; charset="iso-8859-1"'
      || UTL_TCP.crlf
      || UTL_TCP.crlf
      );
      UTL_SMTP.write_data (l_mail_conn, p_html_msg);
      UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
      END IF;

      UTL_SMTP.write_data (l_mail_conn,
      '--' || l_boundary || '--' || UTL_TCP.crlf
      );
      UTL_SMTP.close_data (l_mail_conn);
      UTL_SMTP.quit (l_mail_conn);
      END;
        • 1. Re: Email Configuration
          JustinCave
          The error is coming from your mail server. The error implies that there is no SMTP server listening on port P_SMTP_PORT on the machine P_SMTP_HOST. If you are saying that you are passing 192.168.5.44 as the P_SMTP_HOST and that you are letting P_SMTP_PORT be the default of 25, the error implies that there is no SMTP server running on port 25 on whatever machine has the IP address 192.168.5.44 on your network.

          Note that this thread is probably more appropriate for the PL/SQL forum. Hopefully, one of the forum mods will move it there at some point.

          Justin