Oracle UTL_MAIL SMTP EMAIL POP


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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

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 CHARACTER 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