create or replace package demo_mail is ----------------------- customizable div ----------------------- -- customize the smtp host, port and your domain name below. smtp_host varchar2(256) := 'smtp.eygle.com' ; smtp_port pls_integer := 25;
create or replace package demo_mail is
----------------------- customizable div -----------------------
-- customize the smtp host, port and your domain name below.
smtp_host varchar2(256) := 'smtp.eygle.com';
smtp_port pls_integer := 25;
smtp_domain varchar2(256) := 'eygle.com';
-- customize the signature that will appear in the email's mime header.
-- useful for versioning.
mailer_id constant varchar2(256) := 'mailer by eygle';
--------------------- end customizable div ---------------------
-- a unique string that demarcates boundaries of parts in a multi-part email
-- the string should not appear inside the body of any part of the email.
-- customize this if needed or generate this randomly dynamically.
boundary constant varchar2(256) := '-----7d81b75ccc90d2974f7a1cbd';
first_boundary constant varchar2(256) := '--' || boundary || utl_tcp.crlf;
last_boundary constant varchar2(256) := '--' || boundary || '--' ||
utl_tcp.crlf;
-- a mime type that denotes multi-part email (mime) messages.
multipart_mime_type constant varchar2(256) := 'multipart/mixed; boundary='||
boundary || '';
max_base64_line_width constant pls_integer := 76 / 4 * 3;
-- a simple email api for sending email in plain text in a single call.
-- the format of an email address is one of these:
-- someone@some-domain
-- someone at some domain
-- someone at some domain
-- the recipients is a list of email addresses separated by
-- either a , or a ;
procedure mail(sender in varchar2,
recipients in varchar2,
subject in varchar2,
message in varchar2);
-- extended email api to send email in html or plain text with no size limit.
-- first, begin the email by begin_mail(). then, call write_text() repeatedly
-- to send email in ascii piece-by-piece. or, call write_mb_text() to send
-- email in non-ascii or multi-byte character set. end the email with
-- end_mail().
function begin_mail(sender in varchar2,
recipients in varchar2,
subject in varchar2,
mime_type in varchar2 default 'text/plain',
priority in pls_integer default null)
return utl_smtp.connection;
-- write email body in ascii
procedure write_text(conn in out nocopy utl_smtp.connection,
message in varchar2);
-- write email body in non-ascii (including multi-byte). the email body
-- will be sent in the database character set.
procedure write_mb_text(conn in out nocopy utl_smtp.connection,
message in varchar2);
-- write email body in binary
procedure write_raw(conn in out nocopy utl_smtp.connection,
message in raw);
-- apis to send email with attachments. attachments are sent by sending
-- emails in multipart/mixed mime format. specify that mime format when
-- beginning an email with begin_mail().
-- send a single text attachment.
procedure attach_text(conn in out nocopy utl_smtp.connection,
data in varchar2,
mime_type in varchar2 default 'text/plain',
inline in boolean default true,
filename in varchar2 default null,
last in boolean default false);
-- send a binary attachment. the attachment will be encoded in base-64
-- encoding format.
procedure attach_base64(conn in out nocopy utl_smtp.connection,
data in raw,
mime_type in varchar2 default 'application/octet',
inline in boolean default true,
filename in varchar2 default null,
last in boolean default false);
-- send an attachment with no size limit. first, begin the attachment
-- with begin_attachment(). then, call write_text repeatedly to send
-- the attachment piece-by-piece. if the attachment is text-based but
-- in non-ascii or multi-byte character set, use write_mb_text() instead.
-- to send binary attachment, the binary content should first be
-- encoded in base-64 encoding format using the demo package for 8i,
-- or the native one in 9i. end the attachment with end_attachment.
procedure begin_attachment(conn in out nocopy utl_smtp.connection,
mime_type in varchar2 default 'text/plain',
inline in boolean default true,
filename in varchar2 default null,
transfer_enc in varchar2 default null);
-- end the attachment.
procedure end_attachment(conn in out nocopy utl_smtp.connection,
last in boolean default false);
-- end the email.
procedure end_mail(conn in out nocopy utl_smtp.connection);
-- extended email api to send multiple emails in a session for better
-- performance. first, begin an email session with begin_session.
-- then, begin each email with a session by calling begin_mail_in_session
-- instead of begin_mail. end the email with end_mail_in_session instead
-- of end_mail. end the email session by end_session.
function begin_session return utl_smtp.connection;
-- begin an email in a session.
procedure begin_mail_in_session(conn in out nocopy utl_smtp.connection,
sender in varchar2,
recipients in varchar2,
subject in varchar2,
mime_type in varchar2 default 'text/plain',
priority in pls_integer default null);
-- end an email in a session.
procedure end_mail_in_session(conn in out nocopy utl_smtp.connection);
-- end an email session.
procedure end_session(conn in out nocopy utl_smtp.connection);
end;
/
create or replace package body demo_mail is
-- return the next email address in the list of email addresses, separated
-- by either a , or a ;. the format of mailbox may be in one of these:
-- someone@some-domain
-- someone at some domain
-- someone at some domain
function get_address(addr_list in out varchar2) return varchar2 is
addr varchar2(256);
i pls_integer;
function lookup_unquoted_char(str in varchar2,
chrs in varchar2) return pls_integer as
c varchar2(5);
i pls_integer;
len pls_integer;
inside_quote boolean;
begin
inside_quote := false;
i := 1;
len := length(str);
while (i
c := substr(str, i, 1);
if (inside_quote) then
if (c = '') then
inside_quote := false;
elsif (c = '\') then
i := i + 1; -- skip the quote character
end if;
goto next_char;
end if;
if (c = '') then
inside_quote := true;
goto next_char;
end if;
if (instr(chrs, c) >= 1) then
return i;
end if;
>
i := i + 1;
end loop;
return 0;
end;
begin
addr_list := ltrim(addr_list);
i := lookup_unquoted_char(addr_list, ',;');
if (i >= 1) then
addr := substr(addr_list, 1, i - 1);
addr_list := substr(addr_list, i + 1);
else
addr := addr_list;
addr_list := '';
end if;
i := lookup_unquoted_char(addr, 'if (i >= 1) then
addr := substr(addr, i + 1);
i := instr(addr, '>');
if (i >= 1) then
addr := substr(addr, 1, i - 1);
end if;
end if;
return addr;
end;
-- write a mime header
procedure write_mime_header(conn in out nocopy utl_smtp.connection,
name in varchar2,
value in varchar2) is
begin
utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.crlf);
end;
-- mark a message-part boundary. set to true for the last boundary.
procedure write_boundary(conn in out nocopy utl_smtp.connection,
last in boolean default false) as
begin
if (last) then
utl_smtp.write_data(conn, last_boundary);
else
utl_smtp.write_data(conn, first_boundary);
end if;
end;
------------------------------------------------------------------------
procedure mail(sender in varchar2,
recipients in varchar2,
subject in varchar2,
message in varchar2) is
conn utl_smtp.connection;
begin
conn := begin_mail(sender, recipients, subject);
write_text(conn, message);
end_mail(conn);
end;
------------------------------------------------------------------------
function begin_mail(sender in varchar2,
recipients in varchar2,
subject in varchar2,
mime_type in varchar2 default 'text/plain',
priority in pls_integer default null)
return utl_smtp.connection is
conn utl_smtp.connection;
begin
conn := begin_session;
begin_mail_in_session(conn, sender, recipients, subject, mime_type,
priority);
return conn;
end;
------------------------------------------------------------------------
procedure write_text(conn in out nocopy utl_smtp.connection,
message in varchar2) is
begin
utl_smtp.write_data(conn, message);
end;
------------------------------------------------------------------------
procedure write_mb_text(conn in out nocopy utl_smtp.connection,
message in varchar2) is
begin
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
end;
------------------------------------------------------------------------
procedure write_raw(conn in out nocopy utl_smtp.connection,
message in raw) is
begin
utl_smtp.write_raw_data(conn, message);
end;
------------------------------------------------------------------------
procedure attach_text(conn in out nocopy utl_smtp.connection,
data in varchar2,
mime_type in varchar2 default 'text/plain',
inline in boolean default true,
filename in varchar2 default null,
last in boolean default false) is
begin
begin_attachment(conn, mime_type, inline, filename);
write_text(conn, data);
end_attachment(conn, last);
end;
------------------------------------------------------------------------
procedure attach_base64(conn in out nocopy utl_smtp.connection,
data in raw,
mime_type in varchar2 default 'application/octet',
inline in boolean default true,
filename in varchar2 default null,
last in boolean default false) is
i pls_integer;
len pls_integer;
begin
begin_attachment(conn, mime_type, inline, filename, 'base64');
-- split the base64-encoded attachment into multiple lines
i := 1;
len := utl_raw.length(data);
while (i if (i + max_base64_line_width utl_smtp.write_raw_data(conn,
utl_encode.base64_encode(utl_raw.substr(data, i,
max_base64_line_width)));
else
utl_smtp.write_raw_data(conn,
utl_encode.base64_encode(utl_raw.substr(data, i)));
end if;
utl_smtp.write_data(conn, utl_tcp.crlf);
i := i + max_base64_line_width;
end loop;
end_attachment(conn, last);
end;
------------------------------------------------------------------------
procedure begin_attachment(conn in out nocopy utl_smtp.connection,
mime_type in varchar2 default 'text/plain',
inline in boolean default true,
filename in varchar2 default null,
transfer_enc in varchar2 default null) is
begin
write_boundary(conn);
write_mime_header(conn, 'content-type', mime_type);
if (filename is not null) then
if (inline) then
write_mime_header(conn, 'content-disposition',
'inline; filename='||filename||'');
else
write_mime_header(conn, 'content-disposition',
'attachment; filename='||filename||'');
end if;
end if;
if (transfer_enc is not null) then
write_mime_header(conn, 'content-transfer-encoding', transfer_enc);
end if;
utl_smtp.write_data(conn, utl_tcp.crlf);
end;
------------------------------------------------------------------------
procedure end_attachment(conn in out nocopy utl_smtp.connection,
last in boolean default false) is
begin
utl_smtp.write_data(conn, utl_tcp.crlf);
if (last) then
write_boundary(conn, last);
end if;
end;
------------------------------------------------------------------------
procedure end_mail(conn in out nocopy utl_smtp.connection) is
begin
end_mail_in_session(conn);
end_session(conn);
end;
------------------------------------------------------------------------
function begin_session return utl_smtp.connection is
conn utl_smtp.connection;
begin
-- open smtp connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
return conn;
end;
------------------------------------------------------------------------
procedure begin_mail_in_session(conn in out nocopy utl_smtp.connection,
sender in varchar2,
recipients in varchar2,
subject in varchar2,
mime_type in varchar2 default 'text/plain',
priority in pls_integer default null) is
my_recipients varchar2(32767) := recipients;
my_sender varchar2(32767) := sender;
begin
-- specify sender's address (our server allows bogus address
-- as long as it is a full email address (xxx@yyy.com).
utl_smtp.mail(conn, get_address(my_sender));
-- specify recipient(s) of the email.
while (my_recipients is not null) loop
utl_smtp.rcpt(conn, get_address(my_recipients));
end loop;
-- start body of email
utl_smtp.open_data(conn);
-- set from mime header
write_mime_header(conn, 'from', sender);
-- set to mime header
write_mime_header(conn, 'to', recipients);
-- set subject mime header
write_mime_header(conn, 'subject', subject);
-- set content-type mime header
write_mime_header(conn, 'content-type', mime_type);
-- set x-mailer mime header
write_mime_header(conn, 'x-mailer', mailer_id);
-- set priority:
-- high normal low
-- 1 2 3 4 5
if (priority is not null) then
write_mime_header(conn, 'x-priority', priority);
end if;
-- send an empty line to denotes end of mime headers and
-- beginning of message body.
utl_smtp.write_data(conn, utl_tcp.crlf);
if (mime_type like 'multipart/mixed%') then
write_text(conn, 'this is a multi-part message in mime format.' ||
utl_tcp.crlf);
end if;
end;
------------------------------------------------------------------------
procedure end_mail_in_session(conn in out nocopy utl_smtp.connection) is
begin
utl_smtp.close_data(conn);
end;
------------------------------------------------------------------------
procedure end_session(conn in out nocopy utl_smtp.connection) is
begin
utl_smtp.quit(conn);
end;
end;
/