Oracle UTL_MAIL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Send emails, w/wo attachments, from inside the database. Our recommendation is that UTL_MAIL only be used for the most basic email requirements: Instead (click on the link at page bottom) use UTL_SMTP which has far richer functionality.
AUTHID CURRENT_USER
Dependencies
DBMS_OUTPUT UTL_MAIL_INTERNAL UTL_TCP
PLITBLM UTL_RAW V$VERSION
UTL_ENCODE UTL_SMTP  
Documented Yes
Exceptions
Error Code Reason
ORA-29261 INVALID_ARGUMENT
ORA-44101 INVALID_PRIORITY
First Available 2002-2003
Initialization Parameter SMTP_OUT_SERVER <port_number>
SMTP_OUT_SERVER = 9090
Mime Types
Value
'text/plain'
'text/plain; charset=us-ascii'
'text/plain; charset=ISO-8859-1'
'text/html; charset=utf-8'
'text/plain; charset=windows-1254'
'application/octet'
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb
Subprograms
 
SEND
Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients utl_mail.send(
sender     IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc         IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc        IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject    IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message    IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
priority   IN PLS_INTEGER                   DEFAULT 3,
replyto    IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
--the SMTP_OUT_SERVER parameter must be set
conn sys@pdbdev as sysdba

ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;

conn uwclass/uwclass@pdbdev

CREATE TABLE emails_sent (
email_addr VARCHAR2(100));

CREATE OR REPLACE PROCEDURE eblast IS
 CURSOR mcur IS
 SELECT per_h_email
 FROM mlib.person
 WHERE per_ok2_email = 'Y'
 AND per_h_email IS NOT NULL
 AND per_h_email NOT IN (SELECT email_addr FROM emails_sent);

 vSender VARCHAR2(30) := 'mailsys@morganslibrary.org';
 vSubj   msg.subject%TYPE;
 vMesg   VARCHAR2(4000);
 vMType  VARCHAR2(30) := 'text/plain; charset=us-ascii';
BEGIN
  SELECT subject, msgcol
  INTO vSubj, vMesg
  FROM msg;

  FOR mrec IN mcur
  LOOP
    utl_mail.send(vSender, mrec.per_h_email, NULL, NULL, vSubj, vMesg, vMType, NULL);
  END LOOP;
END eblast;
/
 
SEND_ATTACH_RAW
Represents the SEND Procedure overloaded for RAW attachments utl_mail.send_attach_raw (
sender        IN VARCHAR2 CHARACTER SET ANY_CS,
recipients    IN VARCHAR2 CHARACTER SET ANY_CS,
cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
priority      IN PLS_INTEGER                   DEFAULT 3,
attachment    IN RAW,
att_inline    IN BOOLEAN                       DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT  'text/plain; charset=us-ascii',
att_filename  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
--the SMTP_OUT_SERVER parameter must be set
conn sys@pdbdev as sysdba

ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;

conn pm/pm

UPDATE online_media
SET product_text = 'This is a UTL_MAIL demo';
COMMIT;

CREATE OR REPLACE PROCEDURE Mail_Attach (fname VARCHAR2) IS
 vInHandle utl_file.file_type;
 rfile     RAW(32767);
 flen      NUMBER;
 bsize     NUMBER;
 ex        BOOLEAN;

 vSender   VARCHAR2(30) := 'mailsys@morganslibrary.org';
 vSubj     VARCHAR2(50) := 'April Library News';
 vMesg     VARCHAR2(4000);
 vMType    VARCHAR2(30) := 'text/plain; charset=us-ascii';

 CURSOR mcur IS
 SELECT per_h_email
 FROM ml.person
 WHERE per_ok2_email = 'Y'
 AND per_h_email IS NOT NULL;
BEGIN
  vMesg := 'Please print and complete attachment';

  SELECT utl_raw.cast_to_raw(product_text)
  INTO rf
  FROM online_media
  WHERE rownum = 1;

  utl_file.fgetattr('ORALOAD', fname, ex, flen, bsize);
  vInHandle := utl_file.fopen('ORALOAD', fname, 'R');
  utl_file.get_raw(l_output, rfile, flen);
  utl_file.fclose(vInHandle);

  FOR mrec IN mcur
  LOOP
    utl_mail.send_attach_raw(
      sender => vSender,
      recipients => mrec.per_h_email,
      subject => vSubj,
      message => vMesg,
      attachment => rfile,
      att_inline => FALSE,
      att_filename => fname);
  END LOOP;
END;
/
 
SEND_ATTACH_VARCHAR2
Represents the SEND Procedure overloaded for VARCHAR2 attachments utl_mail.send_attach_varchar2(
sender        IN VARCHAR2 CHARACTER SET ANY_CS,
recipients    IN VARCHAR2 CHARACTER SET ANY_CS,
cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
priority      IN PLS_INTEGER                   DEFAULT 3,
attachment    IN VARCHAR2 CHARACTER SET ANY_CS,
att_inline    IN BOOLEAN                       DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii,
att_filename  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULTNULL,
replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
Same as SEND_ATTACH_RAW except that the attachment must be an ASCII text file

Related Topics
Packages
UTL_MAIL_INTERNAL
UTL_SMTP
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved