Oracle Send HTML Email

This procedure sends and HTML email from Oracle.  This is usable code and can be customized as per required:

PROMPT CREATE OR REPLACE PROCEDURE html_email
CREATE OR REPLACE PROCEDURE html_email
(
p_to IN VARCHAR2, --comma seperated list
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_html IN VARCHAR2 DEFAULT NULL,
p_smtp_hostname IN VARCHAR2,
p_smtp_portnum IN VARCHAR2
)
IS


l_boundary VARCHAR2(255) DEFAULT 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html CLOB := empty_clob; --This LOB will be the email message
l_offset NUMBER;
l_ammount NUMBER;
l_temp VARCHAR2(32767) DEFAULT NULL;
l_dest VARCHAR2(32767);
l_dest_tmp VARCHAR2(32767);
BEGIN
l_dest := p_to;
l_connection := utl_smtp.open_connection(p_smtp_hostname, p_smtp_portnum);
utl_smtp.helo(l_connection, p_smtp_hostname);
utl_smtp.mail(l_connection, p_from);
WHILE instr(l_dest, ',') != 0 LOOP
l_dest_tmp := substr(l_dest, 1, instr(l_dest, ',') - 1);
l_dest := substr(l_dest, instr(l_dest, ',') + 1);
utl_smtp.rcpt(l_connection, l_dest_tmp);
END LOOP;
utl_smtp.rcpt(l_connection, l_dest);
l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) || chr(10);
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary(l_body_html, FALSE, 10);
dbms_lob.WRITE(l_body_html, length(l_temp), 1, l_temp);
----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=windows-1252' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);
----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html; charset=windows-1252' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(p_html), l_offset, p_html);
----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
WHILE l_offset < dbms_lob.getlength(l_body_html) LOOP
utl_smtp.write_data(l_connection, dbms_lob.substr(l_body_html, l_ammount, l_offset));
l_offset := l_offset + l_ammount;
l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
END LOOP;
utl_smtp.close_data(l_connection);
utl_smtp.quit(l_connection);
dbms_lob.freetemporary(l_body_html);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;


/
GRANT EXECUTE ON html_email TO PUBLIC;