Oracle UTL_MAIL
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 18c
What's New In 19c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx