| General Information |
| Purpose |
Send emails, w/wo 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 |
| DBMS_OUTPUT |
UTL_ENCODE |
UTL_RAW |
UTL_TCP |
| PLITBLM |
UTL_MAIL_INTERNAL |
UTL_SMTP |
V$VERSION |
|
| Exceptions |
| Exception |
Description |
| ORA-29261 |
INVALID_ARGUMENT |
| ORA-44101 |
INVALID_PRIORITY |
|
| 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' |
|
| Required Object Privilege |
GRANT execute ON utl_mail TO <schema_name>; |
| GRANT execute ON utl_mail TO uwclass; |
| Security Model |
Owned by SYS with no privileges granted. Runs under Invoker Rights. A PUBLIC SYNONYM is created. |
| Subprograms |
|
| |
SEND (new 11.2.0.1 parameter)  |
| 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 / as sysdba
ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;
conn uwclass/uwclass
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 (new 11.2.0.1 parameter) |
| 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 / 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 (new 11.2.0.1 parameter) |
| 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 |
