Dear Reader
We may encounter situations where we need to send neatly formatted e-mail alerts from a custom process containing things like tables etc. which needs HTML formatting to be done
The below code provides a means to send HTML formatted content using UTL_SMTP
In this code, you would need to simply replace the text in red with the actual HTML content as per your requirements
DECLARE
lv_html_start VARCHAR2(240) := '<html>
<body>
<p style=''font-size:10.0pt;font-family:"Arial","serif"''>';
lv_html VARCHAR2(32000) := NULL;
lv_html_cont VARCHAR2(32000) := NULL;
lv_subject VARCHAR2(240);
lv_body VARCHAR2(2000);
lv_boundary VARCHAR2(255) default 'a1b2c3d4e3f2g1';
lv_mail_conn utl_smtp.connection;
lv_body_html CLOB := EMPTY_CLOB; --This LOB will be the email message
lv_offset NUMBER;
lv_amount NUMBER;
lv_temp VARCHAR2(32767) DEFAULT NULL;
gc_email_host VARCHAR2(240) := 'localhost';
gc_email_port NUMBER := 25;
gc_from_emailid VARCHAR2(100) := 'testhtmlemail@mycompany.com';
p_email_id VARCHAR2(240) := 'anand.shankar@mycompany.com';
BEGIN
lv_subject := 'This is a HTML email from Oracle!!';
lv_body := 'Dear User,
Below is the HTML content of this mail
';
lv_html_cont := lv_html_cont || '<table border=0 cellspacing=0 cellpadding=0 style=''border-collapse:collapse;mso-yfti-tbllook:1184;mso-padding-alt:0in 0in 0in 0in''>'
|| '<tr>'
|| '<td style=''border:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;font-size:10.0pt;font-family:"Arial","serif"''><b>Column 1</b></td>'
|| '<td style=''border:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;font-size:10.0pt;font-family:"Arial","serif"''><b>Column 2</b></td>'
|| '</tr>';
lv_html_cont := lv_html_cont || '</table>';
lv_html := lv_html_start || lv_body || lv_html_cont;
lv_mail_conn := utl_smtp.open_connection(gc_email_host, gc_email_port);
utl_smtp.helo(lv_mail_conn, gc_email_host);
utl_smtp.mail(lv_mail_conn, gc_from_emailid);
utl_smtp.rcpt(lv_mail_conn, p_email_id);
lv_temp := lv_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
lv_temp := lv_temp || 'To: ' || p_email_id || chr(13) || chr(10);
lv_temp := lv_temp || 'From: ' || gc_from_emailid || chr(13) || chr(10);
lv_temp := lv_temp || 'Subject: ' || lv_subject || chr(13) || chr(10);
lv_temp := lv_temp || 'Reply-To: ' || gc_from_emailid || chr(13) || chr(10);
lv_temp := lv_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || lv_boundary || chr(34) || chr(13) ||
chr(10);
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary(lv_body_html, false, 10);
dbms_lob.write(lv_body_html,length(lv_temp), 1, lv_temp);
----------------------------------------------------
-- Write the text boundary
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
lv_temp := '--' || lv_boundary || chr(13)||chr(10);
lv_temp := lv_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(lv_body_html, length(lv_temp), lv_offset, lv_temp);
----------------------------------------------------
-- Write the HTML boundary
lv_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || lv_boundary ||
chr(13) || chr(10);
lv_temp := lv_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
dbms_lob.write(lv_body_html, length(lv_temp), lv_offset, lv_temp);
----------------------------------------------------
-- Write the HTML portion of the message
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
dbms_lob.write(lv_body_html, length(lv_html), lv_offset, lv_html);
----------------------------------------------------
-- Write the final html boundary
lv_temp := chr(13) || chr(10) || '--' || lv_boundary || '--' || chr(13);
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
dbms_lob.write(lv_body_html, length(lv_temp), lv_offset, lv_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
lv_offset := 1;
lv_amount := 1900;
utl_smtp.open_data(lv_mail_conn);
while lv_offset < dbms_lob.getlength(lv_body_html) loop
utl_smtp.write_data(lv_mail_conn, dbms_lob.substr(lv_body_html,lv_amount,lv_offset));
lv_offset := lv_offset + lv_amount ;
lv_amount := least(1900,dbms_lob.getlength(lv_body_html) - lv_amount);
end loop;
utl_smtp.close_data(lv_mail_conn);
utl_smtp.quit( lv_mail_conn );
dbms_lob.freetemporary(lv_body_html);
END;
Hope this helps
Cheers
A
We may encounter situations where we need to send neatly formatted e-mail alerts from a custom process containing things like tables etc. which needs HTML formatting to be done
The below code provides a means to send HTML formatted content using UTL_SMTP
In this code, you would need to simply replace the text in red with the actual HTML content as per your requirements
DECLARE
lv_html_start VARCHAR2(240) := '<html>
<body>
<p style=''font-size:10.0pt;font-family:"Arial","serif"''>';
lv_html VARCHAR2(32000) := NULL;
lv_html_cont VARCHAR2(32000) := NULL;
lv_subject VARCHAR2(240);
lv_body VARCHAR2(2000);
lv_boundary VARCHAR2(255) default 'a1b2c3d4e3f2g1';
lv_mail_conn utl_smtp.connection;
lv_body_html CLOB := EMPTY_CLOB; --This LOB will be the email message
lv_offset NUMBER;
lv_amount NUMBER;
lv_temp VARCHAR2(32767) DEFAULT NULL;
gc_email_host VARCHAR2(240) := 'localhost';
gc_email_port NUMBER := 25;
gc_from_emailid VARCHAR2(100) := 'testhtmlemail@mycompany.com';
p_email_id VARCHAR2(240) := 'anand.shankar@mycompany.com';
BEGIN
lv_subject := 'This is a HTML email from Oracle!!';
lv_body := 'Dear User,
Below is the HTML content of this mail
';
lv_html_cont := lv_html_cont || '<table border=0 cellspacing=0 cellpadding=0 style=''border-collapse:collapse;mso-yfti-tbllook:1184;mso-padding-alt:0in 0in 0in 0in''>'
|| '<tr>'
|| '<td style=''border:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;font-size:10.0pt;font-family:"Arial","serif"''><b>Column 1</b></td>'
|| '<td style=''border:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;font-size:10.0pt;font-family:"Arial","serif"''><b>Column 2</b></td>'
|| '</tr>';
lv_html_cont := lv_html_cont || '</table>';
lv_html := lv_html_start || lv_body || lv_html_cont;
lv_mail_conn := utl_smtp.open_connection(gc_email_host, gc_email_port);
utl_smtp.helo(lv_mail_conn, gc_email_host);
utl_smtp.mail(lv_mail_conn, gc_from_emailid);
utl_smtp.rcpt(lv_mail_conn, p_email_id);
lv_temp := lv_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
lv_temp := lv_temp || 'To: ' || p_email_id || chr(13) || chr(10);
lv_temp := lv_temp || 'From: ' || gc_from_emailid || chr(13) || chr(10);
lv_temp := lv_temp || 'Subject: ' || lv_subject || chr(13) || chr(10);
lv_temp := lv_temp || 'Reply-To: ' || gc_from_emailid || chr(13) || chr(10);
lv_temp := lv_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || lv_boundary || chr(34) || chr(13) ||
chr(10);
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary(lv_body_html, false, 10);
dbms_lob.write(lv_body_html,length(lv_temp), 1, lv_temp);
----------------------------------------------------
-- Write the text boundary
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
lv_temp := '--' || lv_boundary || chr(13)||chr(10);
lv_temp := lv_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(lv_body_html, length(lv_temp), lv_offset, lv_temp);
----------------------------------------------------
-- Write the HTML boundary
lv_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || lv_boundary ||
chr(13) || chr(10);
lv_temp := lv_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
dbms_lob.write(lv_body_html, length(lv_temp), lv_offset, lv_temp);
----------------------------------------------------
-- Write the HTML portion of the message
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
dbms_lob.write(lv_body_html, length(lv_html), lv_offset, lv_html);
----------------------------------------------------
-- Write the final html boundary
lv_temp := chr(13) || chr(10) || '--' || lv_boundary || '--' || chr(13);
lv_offset := dbms_lob.getlength(lv_body_html) + 1;
dbms_lob.write(lv_body_html, length(lv_temp), lv_offset, lv_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
lv_offset := 1;
lv_amount := 1900;
utl_smtp.open_data(lv_mail_conn);
while lv_offset < dbms_lob.getlength(lv_body_html) loop
utl_smtp.write_data(lv_mail_conn, dbms_lob.substr(lv_body_html,lv_amount,lv_offset));
lv_offset := lv_offset + lv_amount ;
lv_amount := least(1900,dbms_lob.getlength(lv_body_html) - lv_amount);
end loop;
utl_smtp.close_data(lv_mail_conn);
utl_smtp.quit( lv_mail_conn );
dbms_lob.freetemporary(lv_body_html);
END;
Hope this helps
Cheers
A
This comment has been removed by the author.
ReplyDelete