|
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.
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.
 |
For more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo,
Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly
from
Rampant TechPress.
|
Regards,
Don Burleson
www.dba-oracle.com
www.remote-dba.net
|
