ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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.
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
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;
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;
/
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;
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;
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