Oracle 8i introduces an e-mail server option that allows you to send mail from within your code. So imagine firing e-mails to concerned persons when a critical parameter is altered, when a huge report is generated, when a night-job process fails, when a database has some issues (for DBAs), when an accounting process is completed, or when a month closing is over and so on. The list is infinite and varies from company to company on what level of alerts/notification and information sharing is to be maintained. What's more, if your local service provider supports SMS, you can even trigger an e-mail to a person's mobile.
What To Do
To get started, we'll need to make use of the UTL_SMTP and UTL_TCP packages. UTL_SMTP is the key package that liaises with the operating system protocol service to trigger the emails. You will have to write a customized routine to make use of these built-in packages and eventually call it in all applicable places in the system. E-mails can be triggered online as an event occurs or can be scheduled as a batch job (using the DBMS_JOB package).
I have created a package available for download here. Compile this package in the required database and call the anonymous block present at the end with the appropriate e-mail IDs. Once successfully tested, analyze how useful it can be to your product. You may also try the simple example given below.
What You Need
- Mail server IP name or IP address. This can be on the intranet or a remote available service on the internet. The word "localhost" can be used to tell Oracle to use the default Operating system SMTP service.
- Your OS should support SMTP. Almost all flavors of Unix support SMTP service. If on Windows 2000, check explicitly for SMT services. The word "localhost" as the mail server name should work. (My system crashed before I could try this, so this is in theory.)
- You need the port at which the SMTP service is listening. This is normally defaulted to 25 on most servers.
- The database should be Java enabled. The Jserver option must be installed using initjvm.sql and initplsj.sql and is necessary to be run. This is normally auto-installed in the Enterprise version.
- The UTL_SMTP package should exist in SYS user.
Key Routines in UTL_SMTP Package
UTL_SMTP.OPEN_CONNECTION: Opens a connection to the SMTP server.
UTL_SMTP.HELLO: Performs initial handshake.
UTL_SMTP.MAIL: Initiates a mail transaction with the server; the destination is a mailbox.
UTL_SMTP.RCPT: Specifies a recipient.
UTL_SMTP.DATA: Specifies the body.
UTL_SMTP.OPEN_DATA/UTL_SMTP.WRITE_DATA/UTL_SMTP.CLOSE_DATA: A finer way of specifying the body with more control.
UTL_SMTP.VRFY: Verifies the validity of an e-mail address.
UTL_SMTP.QUIT: Terminates the SMTP session and disconnects from the server.
Any Disadvantages?
I have come across one disadvantage while testing that isn't related to Oracle. The sender's e-mail ID can be framed, although this should be taken care of by the e-mail service in use. This option is enabled on most Unix servers. So it is up to the developer to instill stringent checks so that e-mails are not faked. E-mails can be sent with a default "system" ID if autogenerated; otherwise, e-mails needs to be pegged with a valid sender ID in order to maintain authenticity. There might be an option that can be set at the OS or e-mail server level, but I am not aware of one presently.
Care should also be taken to maintain a reasonable size for the e-mails. A large number of huge e-mails may overload the server. The size of the e-mail body would depend on what datatype is being used.
Simple example
declare
l_maicon utl_smtp.connection;
begin
l_maicon :=utl_smtp.open_connection('localhost');
utl_smtp.helo(l_maicon,'hostname');
utl_smtp.mail(l_maicon,'ts2017@emirates.com');
utl_smtp.rcpt(l_maicon,'ts2017@emirates.com');
utl_smtp.data(l_maicon,'From: ts2017@emirates.com' || utl_tcp.crlf||
'To: ts2017@emirates.com' || utl_tcp.crlf ||
'Subject: database e-mail option' || utl_tcp.crlf ||
'You have received this mail from database!');
utl_smtp.quit(l_maicon);
end;
Common Errors
If the SMTP service is not available, the following error would occur:
ORA-20001: 421 Service not available
If Java is not installed properly you get the following error:
ORA-29540: class oracle/plsql/net/TCPConnection does not exist
»
See All Articles by Columnist Amar Kumar Padhi
IE 7
Firefox 2.0