Oracle UTL_MAIL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Send emails, w/wo attachments, from inside the database
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

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