0 Replies Latest reply: Nov 16, 2012 5:47 AM by user568657 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;