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) := '[email protected]';
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) := '[email protected]';
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. |