Oracle UTL_MAIL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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

The Library recommends that the grant to PUBLIC be revoked immediately following installation as it poses a significant security risk. Also be sure that you have created a Network Access Control list restricting or eliminating network access by this package.
Source {ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb
SQL> @?/rdbms/admin/utlmail.sql

Package created.

Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb

SP2-0808: Package created with compilation warnings

SP2-0810: Package Body created with compilation warnings


Grant succeeded.

SP2-0810: Package Body created with compilation warnings

Errors for PACKAGE BODY UTL_MAIL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
94/24 PLW-07203: parameter 'ADDR_LIST' may benefit from use of the
NOCOPY compiler hint

126/28 PLW-06010: keyword "DATA" used as a defined name
137/23 PLW-06010: keyword "DATA" used as a defined name
145/26 PLW-06010: keyword "DATA" used as a defined name
305/29 PLW-07203: parameter 'SMTP_SERVERS' may benefit from use of the
NOCOPY compiler hint

306/29 PLW-07203: parameter 'SMTP_PORTS' may benefit from use of the
NOCOPY compiler hint

351/10 PLW-06002: Unreachable code
354/9 PLW-06002: Unreachable code
356/9 PLW-06002: Unreachable code
429/14 PLW-06009: procedure "SEND_I" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
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 DEFAULT NULL,
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
Built-in Functions
Built-in Packages
UTL_MAIL_INTERNAL
UTL_SMTP
What's New In 12cR2
What's New In 18cR3

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