Tuesday, November 15, 2011

Sending UTF-8 encoded HTML Email using UTL_SMTP

The PL/SQL procedure below can send html email using the UTL_SMTP Oracle built in package. It handles utf-8 strings in the subject, To/From Name fields and in the message itself.
It was tested on an Oracle 11g R2 database with UTF-8 NLS_CHARACTERSET set and an Exchange smtp server.
CREATE OR REPLACE PROCEDURE send_html_email(p_sender_name VARCHAR2, -- name of person sending email
                                            p_sender_mail VARCHAR2, -- email of person sending email
                                            p_recipient   VARCHAR2, -- recipient of contact email
                                            p_subject     VARCHAR2, -- subject of email
                                            p_message     VARCHAR2 -- message of email
                                            ) is

  conn UTL_SMTP.CONNECTION;

  v_smtp_hostname varchar(200) := 'smtp_server';
  v_smtp_uname    VARCHAR2(200) := 'smtp_server_uname';
  v_smtp_passwd   VARCHAR2(200) := 'smtp_server_password';
  v_sender        VARCHAR2(200) := null;
 
  CHAR_SET constant varchar(200) := 'Content-Type: text/html;charset=UTF-8' ||UTL_TCP.CRLF;
  MIME_VERSION constant varchar(200) := 'MIME-version: 1.0' || UTL_TCP.CRLF;
  CONT_ENCODING constant varchar(200) := 'Content-Transfer-Encoding: quoted-printable ' ||UTL_TCP.CRLF;

BEGIN

  if p_message is null then
    return;
  end if;

  conn := utl_smtp.open_connection(v_smtp_hostname, 25);

  if v_smtp_uname is not null then
    UTL_SMTP.ehlo(conn, v_smtp_hostname);
  
    UTL_SMTP.command(conn, 'AUTH LOGIN');
    UTL_SMTP.command(conn,
                     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_uname))));
    UTL_SMTP.command(conn,
                     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_passwd))));
  
  else
    UTL_SMTP.helo(conn, v_smtp_hostname);
  
  end if;

  UTL_SMTP.Helo(conn, v_smtp_hostname);
  UTL_SMTP.Mail(conn, p_sender_mail);
  UTL_SMTP.rcpt(conn, p_recipient);
  UTL_SMTP.OPEN_DATA(conn);

  UTL_SMTP.WRITE_DATA(conn, MIME_VERSION);
  UTL_SMTP.WRITE_DATA(conn, CHAR_SET);
  UTL_SMTP.WRITE_DATA(conn, CONT_ENCODING);

  UTL_SMTP.WRITE_DATA(conn,
                      'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') ||
                      ' -0800 (GMT)' || UTL_TCP.CRLF);
  UTL_SMTP.write_raw_data(conn,
                          utl_raw.cast_to_raw('Subject:' || p_subject));
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); -- this crlf is for 'Subject' field

  if p_sender_name is null then
    v_sender := p_sender_mail;
  else
    v_sender := '"' || p_sender_name || '" <' || p_sender_mail || '>';
  end if;
  UTL_SMTP.write_raw_data(conn, utl_raw.cast_to_raw('From:' || v_sender));
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); -- this crlf is for 'From' field
  UTL_SMTP.WRITE_DATA(conn, 'To: ' || p_recipient || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF);
  UTL_SMTP.WRITE_RAW_DATA(conn,
                          UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message)));
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF);
  UTL_SMTP.CLOSE_DATA(conn);
  UTL_SMTP.QUIT(conn);
END;

7 comments:

  1. Designing HTML email is one of the key steps in email marketing. A good looking HTML email will bring you a higher open rate than a plain text message. But when a HTML email comes in a distorted format which makes it unpleasant or even unreadable, you can't expect a high response from your subscribers.

    HTML email software

    ReplyDelete
  2. Great!! It helped me a lot! Thank you.

    ReplyDelete
  3. Really Great it is working supreb. Thank you for keeping the script. Thanks a lot.

    ReplyDelete
  4. This code works well at sending emails. However, the emails can suffer from "dot stuffing". Has anyone already coded a PL/SQL solution to "dot stuffing". Basically, when the 76th character on a line begins with a '.', the period is removed.

    ReplyDelete
    Replies
    1. I have the same problem - dot/period is removed when it's the 76th character. Is there any PL/SQL function in the UTL_SMTP package that offers a solution for this problem? What was your solution?

      Delete
    2. (make sure your l_buffer raw(...) is large enough)

      l_buffer := UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message));
      l_buffer := replace(l_buffer,'D0D0A2E','D0D0A2E2E');--dot stuffing
      utl_smtp.write_raw_data(g_connection, l_buffer);

      Delete