This discussion is archived
1 Reply Latest reply: Nov 19, 2012 8:51 AM by Justin Cave RSS

Email Configuration

user568657 Newbie
Currently Being Moderated
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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points