The utl_smtp utility enables e-mail messages to be sent from the database (PL/SQL) to any valid e-mail address. This can be very useful in database monitoring since e-mails can be sent to production support personnel when certain events occur. These events could be anything ranging from space deficits to unauthorized database access. Anything that can be monitored can be sent in an e-mail.
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. Figure 5.2 displays the actual e-mail as delivered to my inbox.
Figure 5.2 – Email Received From Oracle
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.