UTL_SMTP Procedure Hangs or Slow to Execute and No Email is Sent
(Doc ID 390852.1)
Last updated on MARCH 04, 2022
Applies to:
PL/SQL - Version 9.2.0.6 and laterInformation in this document applies to any platform.
Symptoms
Using the following UTL_SMTP code results in the execution of the stored procedure to hang or to take several minutes to complete execution and the e-mail not being sent.
CREATE OR REPLACE procedure send_mail
(v_recipient_adr IN VARCHAR2, -- Mail recipient email address
v_recipient_na IN VARCHAR2, -- Mail recipient name, - comment
v_sender_adr IN VARCHAR2, -- Sender of email's email address
v_sender_na IN VARCHAR2, -- Sender of email's name - a comment
v_subject IN VARCHAR2, -- Text to appear in subject line
v_mesg IN VARCHAR2) -- Variable for message text.
IS
v_err_action VARCHAR2(50);
v_mailhost VARCHAR2(30) := 'exchangesvr.asdd.com'; -- Mail server
v_headr VARCHAR2(1000);
v_mesg_body VARCHAR2(4000);
v_crlf VARCHAR2(2) := CHR( 13 ) || CHR ( 10 ); -- Carriage
return line feed characters
mail_conn utl_smtp.connection;
BEGIN
v_err_action := 'utl_smtp.open_connection';
mail_conn := utl_smtp.open_connection(v_mailhost , 25);
v_err_action := 'utl_smtp.helo';
utl_smtp.helo(mail_conn, v_mailhost);
v_err_action := 'utl_smtp.mail';
utl_smtp.mail(mail_conn, v_sender_adr);
v_err_action := 'utl_smtp.rcpt';
utl_smtp.rcpt(mail_conn, v_recipient_adr || v_crlf);
v_headr := 'Date: ' || TO_CHAR( SYSDATE + 5/24, 'dd Mon yy hh24:mi:ss' ) ||
v_crlf ||
'From: ' || v_sender_na || v_crlf ||
'To: ' || v_recipient_na || v_crlf ||
'Subject: ' || v_subject || v_crlf;
v_mesg_body := v_headr || '' || v_crlf || v_mesg;
v_err_action := 'utl_smtp.data';
utl_smtp.data(mail_conn, v_mesg_body);
v_err_action := 'utl_smtp.quit';
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(v_err_action);
NULL;
END;
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |