首页 > 代码库 > How to Send an Email Using UTL_SMTP with Authenticated Mail Server

How to Send an Email Using UTL_SMTP with Authenticated Mail Server

In this Document

 Goal
 Solution
 References

APPLIES TO:

PL/SQL - Version 9.2.0.1 to 12.1.0.1 [Release 9.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 07-Apr-2014***

GOAL

The UTL_SMTP package is designed for sending electronic mails (emails) over Simple Mail Transfer Protocol (SMTP) as specified by RFC821. 
Some mail servers require a username and password to be supplied. The following error: 530 Authentication required , would occur if the username and password for the SMTP server is needed to use UTL_SMTP.

The sample code below shows how to include the username/password for the Mail server.

SOLUTION

Create or replace procedure testmail(fromm varchar2,too varchar2,sub varchar2,body varchar2,port number) 
is 
objConnection utl_smtp.connection; 
username varchar2(20):= ‘<username>‘; 
password varchar2(20):= ‘<password>‘; 
vrData varchar2(32000); 
BEGIN 
objConnection := UTL_smtp.open_connection(‘<your domain server name>‘,port); 
UTL_smtp.helo(objConnection, ‘<your domain name server>‘); 
utl_smtp.command(objConnection, ‘AUTH LOGIN‘); 
utl_smtp.command(objConnection,UTL_RAW.CAST_TO_VARCHAR2(utl_encode.base64_encode(utl_raw.cast_to_raw(username)))); 
utl_smtp.command(objConnection,UTL_RAW.CAST_TO_VARCHAR2(utl_encode.base64_encode(utl_raw.cast_to_raw(password)))); 

UTL_smtp.mail(objConnection, fromm); 
UTL_smtp.rcpt(objConnection, too); 
UTL_smtp.open_data(objConnection); 
/* ** Sending the header information */ 
UTL_smtp.write_data(objConnection, ‘From: ‘||fromm || UTL_tcp.CRLF); 
UTL_smtp.write_data(objConnection, ‘To: ‘||too || UTL_tcp.CRLF); 

UTL_smtp.write_data(objConnection, ‘Subject: ‘ || sub || UTL_tcp.CRLF); 
UTL_smtp.write_data(objConnection, ‘MIME-Version: ‘ || ‘1.0‘ || UTL_tcp.CRLF); 
UTL_smtp.write_data(objConnection, ‘Content-Type: ‘ || ‘text/html;‘); 

UTL_smtp.write_data(objConnection, ‘Content-Transfer-Encoding: ‘ || ‘"8Bit"‘ ||UTL_tcp.CRLF); 
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF); 
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||‘<HTML>‘); 
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||‘<BODY>‘); 
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||‘<FONT COLOR="red" FACE="Courier New">‘||body||‘</FONT>‘); 
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||‘</BODY>‘); 
UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||‘</HTML>‘); 
UTL_smtp.close_data(objConnection); 
UTL_smtp.quit(objConnection); 
EXCEPTION 
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN 
UTL_smtp.quit(objConnection); 
dbms_output.put_line(sqlerrm); 
WHEN OTHERS THEN 
UTL_smtp.quit(objConnection); 
dbms_output.put_line(sqlerrm); 
END testmail; 
/

 

DECLARE 
Vdate Varchar2(25); 
BEGIN 
Vdate := to_char(sysdate,‘dd-mon-yyyy HH:MI:SS AM‘); 
TESTMAIL(‘xxx.xxx@xxx.com‘, ‘xxx.xxx@xxx.com‘, ‘TESTMAIL‘,‘This is a UTL_SMTP-generated email at ‘|| Vdate,25); 
END; 
/

REFERENCES

NOTE:317301.1 - How to Test SMTP Authentication from a Telnet Session (for OES and OCS)
NOTE:604763.1 - Check SMTP Server Availability for ORA-29278 or ORA-29279 errors using UTL_SMTP to Send Email.
NOTE:730746.1 - FAQ and Known Issues While Using UTL_SMTP and UTL_MAIL
NOTE:201639.1 - How to Use UTL_SMTP Package With a Mail Server That Needs a Username and Password?

How to Send an Email Using UTL_SMTP with Authenticated Mail Server