Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Sending e-mail from Oracle


Oracle Tips by Burleson Consulting

Updated 10 July 2010

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.


 

 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.