Skip to content

Instantly share code, notes, and snippets.

/demo_mail.pkb

Created December 30, 2011 15:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save anonymous/1540243 to your computer and use it in GitHub Desktop.
Save anonymous/1540243 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
demo_mail.pkb
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 <= len)
LOOP
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;
<<next_char>>
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 < len)
LOOP
IF (i + max_base64_line_width < len)
THEN
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
SELECT server_address, domain
INTO smtp_host, smtp_domain
FROM fnd_nodes
WHERE support_cp = 'Y' AND status = 'Y';
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;
/
Raw
demo_mail.pks
CREATE OR REPLACE PACKAGE demo_mail
IS
----------------------- Customizable Section -----------------------
-- Customize the SMTP host, port and your domain name below.
smtp_host VARCHAR2 (256);-- := '';
smtp_port PLS_INTEGER := 25;
smtp_domain VARCHAR2 (256);-- := '';
-- Customize the signature that will appear in the email's MIME header.
-- Useful for versioning.
mailer_id CONSTANT VARCHAR2 (256)
:= 'Mailer by Oracle UTL_SMTP';
--------------------- End Customizable Section ---------------------
-- 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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment