Applies:
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, wowould 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.
Solutioncreate 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 | '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 ('[email protected]', '[email protected]', '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?
