The utl_smtp Utility

September 2,  2003
Don Burleson
 

The procedure below (sendmail.sql) provides simple e-mail capability. It requires an SMTP host port (25) to be opened. The host can be specified by host name or IP address, with 127.0.0.1 representing the local host machine. Once the port is opened, the procedure specifies the subject, sender, recipient and body of the message.

sendmail.sql

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host

BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
/

Calling the sendmail procedure can be performed like below:

exec send_mail(msg_to=>'dave@oracleutilities.com', -
msg_subject => 'Hello from Oracle', -
msg_text => 'This is the body of the message'-
);

The e-mail will be sent from the database and received by the recipient.

E-mail is a powerful feature that can enhance any monitoring environment. E-mails can be sent from any PL/SQL code to any valid e-mail address including wireless devices that are e-mail enabled. Database content can be included in the body of the message thereby giving the DBA all of the information they need to resolve the problem. The following events are certainly worthy of an e-mail when they occur:

• A tablespace running out of space

• The archive destination directory running out of space

• An object unable to allocate the next extent

• Unauthorized access to, or within, the database

• A query taking n seconds of CPU

• New errors in the alert log

utl_smtp is a powerful utility that should be utilized in a monitoring environment.

Deep inside the operating system executables there are many utilities at the fingertips of Oracle professionals, but until now there has been no advice on how to use these utilities. From tnsping.exe to dbv.exe to wrap.exe, Dave Moore describes each utility and has working examples in the online code depot. Your time savings from a single script is worth the price of this great book.

Get your copy of Oracle Utilities: Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More today and receive immediate access to the Online Code Depot!

http://www.rampant-books.com/book_2003_1_utils.htm

Regards,

    Don Burleson
    www.dba-oracle.com
    www.remote-dba.net