Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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