CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle UTL_MAIL
Version 11.1
 
General Information
Purpose Send emails, with or withoout attachments, from inside the database
Source {ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb
Constants invalid_argument_errcode CONSTANT PLS_INTEGER:= -29261;
Dependencies
UTL_ENCODE UTL_RAW UTL_TCP
UTL_MAIL_INTERNAL UTL_SMTP V$VERSION
Exceptions
Exception Description

-29261

Invalid_argument

-44101

Invalid_priority
Initialization Parameter SMTP_OUT_SERVER <port_number>
SMTP_OUT_SERVER = 9090
Mime Types
Value

'text/plain'

'text/plain; charset=us-ascii'

'application/octet'

Required Object Privilege GRANT execute ON utl_mail TO <schema_name>;
GRANT execute ON utl_mail TO uwclass;
Security Model UTL_MAIL runs under Invoker Rights
 
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);
--the SMTP_OUT_SERVER parameter must be set
conn / as sysdba

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

conn uwclass/uwclass

CREATE OR REPLACE PROCEDURE eblast IS
 CURSOR mcur IS
 SELECT per_h_email
 FROM psoug.person
 WHERE per_ok2_email = 'Y'
 AND per_h_email IS NOT NULL;

 vSender VARCHAR2(30) := 'mailsys@psoug.org';
 vSubj   VARCHAR2(50) := 'April PSOUG News';
 vMesg   VARCHAR2(4000);
 vMType  VARCHAR2(30) := 'text/plain; charset=us-ascii';
BEGIN
  SELECT msgcol
  INTO 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);
--the SMTP_OUT_SERVER parameter must be set
conn / 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@psoug.org';
 vSubj     VARCHAR2(50) := 'April PSOUG News';
 vMesg     VARCHAR2(4000);
 vMType    VARCHAR2(30) := 'text/plain; charset=us-ascii';

 CURSOR mcur IS
 SELECT per_h_email
 FROM psoug.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 C
HARACTER SET ANY_CS DEFAULT NULL);
Same as SEND_ATTACH_RAW except that the attachment must be an ASCII text file.
 
Related Topics
UTL_SMTP
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [80 users online]    © 2010 psoug.org