REM REM maildemo.sql - A PL/SQL package to demonstrate how to use the UTL_SMTP REM package to send emails in ASCII and non-ASCII character sets, emails REM with text or binary attachments. REM REM Note: this package relies on the UTL_ENCODE PL/SQL package in Oracle 9i. CREATE OR REPLACE PACKAGE demo_mail IS ----------------------- Customizable Section ----------------------- -- Customize the SMTP host, port and your domain name below. smtp_host VARCHAR2(256) := 'smtp-server.some-company.com'; smtp_port PLS_INTEGER := 25; smtp_domain VARCHAR2(256) := 'some-company.com'; -- 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; / 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; <> 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 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; /