|
 |
|
Sending e-mail from Oracle
Oracle Database Tips by Donald BurlesonUpdated 10 July 2015 |
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) and the
utl_mail package (first introduced in Oracle 10g) to
facilitate e-mailing from PL/SQL. Your PL/SQL can then be embedded
to any application for a slick and easy to invoke e-mail alert interface.
Before reading this article, make sure to read
these associated notes:
Here is the
PL/SQL code to define the e-mail environment, with references to
working PL/SQL Oracle e-mailing script using utl_smtp by
Rampant author
Dr. Tim Hall and
Dave
Wotton. If you are developing with HTML-DB (APEX) 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 => 'fake@fake.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 in PL/SQL.
create or replace procedure
e_mail_message
(
from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2
)
is
l_mailhost VARCHAR2(64) := 'fake.net';
l_from VARCHAR2(64) := 'someone@happy-days.net';
l_to VARCHAR2(64) := 'boss@fake.net';
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' ;
Another approach to sending e-mail using the utl_mail
package
Staring on Oracle 10g and beyond there is a new option for
sending e-mail using the utl_mail package. See my
full notes here on using the
utl_mail package.
What is
different between utl_mail and utl_smtp? The
utl_mail package is
actually a wrapper over two other packages: utl_tcp and
utl_smtp.
Overall, utl_mail is much easier to use than utl_smtp.
In ancient
times, utl_smtp was Oracle's answer to sending email via PL/SQL.
Introduced in 10g, utl_mail is the way to go because of its overall
simplicity.
Here is
a simple example of how to send email using utl_mail. Replace the
obvious text prompts for real data. If one does not know one's mail
server, send an email to oneself and look at the header data, or ask
the system administrator.
ALTER SYSTEM SET smtp_out_server =
'mailserver.domain.com';
DECLARE
vSender
VARCHAR2(30) := 'sender@somewhere.com';
vRecip
VARCHAR2(30) := 'your.name@domain.com';
vSubj
VARCHAR2(50) := 'Enter the subject here';
vMesg
VARCHAR2(4000) := 'Enter the body';
vMType
VARCHAR2(30) := 'text/plain; charset=us-ascii';
BEGIN
utl_mail.send
(vSender,
vRecip, NULL, NULL, vSubj, vMesg, vMType, NULL);
END;
/
This
example is based on sending email from a PC using Windows XP. As
mentioned, once the mail package is compiled, the
smtp_out_server parameter can be set in the SPFILE.
Limitations on sending e-mail in Oracle with utl_mail
There are several limitations in utl_mail for sending
e-mail messages from inside Oracle:
The utl_mail package can only handle a RAW
datatype, and hence a maximum value of 32k for a 32k mail
message.
|
and include the URL for the page.
