Sending e-mail from Oracle

Sending e-mail from Oracle

Many systems have online e-mail alerts and Oracle provides the utl_smtp PL/SQL package (first introduced in Oracle8i) to facilitate e-mailing from PL/SQL. The PL/SQL can then be embedded to any application for a slick e-mail alert interface. Here is the PL/SQL code to define the e-mail environment, with references to working PL/SQL Oracle e-mailing script by Dr. Tim Hall and Dave Wotton. If you are developing with HTML-DB you can also send Oracle e-mail with the htmldb_mail package.

Also, Stephen Rea suggests his e-mail package, which was derived from his and other's work, and now allows attachments from Oracle LOB objects (CLOB, BLOB), in addition to text and binary file attachments.

Step 1: Run the Jserver code for PL/SQL e-mail

You must install the utl_smtp package and Jserver by running the following DBA scripts as SYSDBA (SYS user):
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql



Step 2: Prototype the PL/SQL e-mail stored procedure

The next step is to write the a prototype for the PL/SQL to process the e-mail message. We will call our procedure e_mail_message, and this will be a sample invocation:

e_mail_message
(
from_name => 'oracle' ,
to_name => 'info@remote-dba.net' ,
subject => 'A test',
message => 'A test message'
);



Step 3: Create your PL/SQL e-mail stored procedure

The following is modified from Dr. Hall's script, and another outstanding and well-documented Oracle emailing script published by Dave Wotton. Wotton has graciously published his sophisticated Oracle e-mailing PL/SQL procedure here.

create or replace procedure
e_mail_message
(
from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2
)
is
l_mailhost VARCHAR2(64) := 'burleson.cc';
l_from VARCHAR2(64) := 'linda@remote-dba.net';
l_to VARCHAR2(64) := 'don@burleson.cc';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

FOR i IN 1 .. 10 LOOP
UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || To_Char(i) || Chr(13));
END LOOP;

UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/



Invoking multi-line Oracle e-mail messages

You can invoke multiple line Oracle email messages by using the PL/SQL assignment operator and concatenating the desired message lines together:

mesg := 'This is a text message' || CHR(13)|| CHR(10) ||
'split over two lines' ;


Oracle utl_mail e-mail PL/SQL package

Oracle Database 10g has made it easier than ever before to interface PL/SQL with e-mail.

The new DBMS package is called utl_mail, and it makes it easier than ever before to send e-mail from inside PL/SQL. Unlike the cumbersome utl_smtp package, the new utl_mail package does not require any knowledge of the underlying protocols. OTN has a great summary of the features:

"This new package makes it possible for a PL/SQL programmer to send programmatically composed emails. It requires only the normal mental model of a user of a GUI email client rather than an understanding of the underlying protocol (SMTP) features. This distinguishes it from Utl_Smtp which was introduced in Oracle8i Database. Utl_Smtp requires that the programmer understands the details of the SMTP protocol. Utl_Mail is much simpler to use because it supports just a limited, but very common, subset of the functionality that Utl_Smtp provides."