Mission Data Blog
Louisville-based Web Development & Software Engineering

I have seen several examples of sending email using oracle with non-ascii characters floating around the Internet, but few seem to tackle sending these ’special’ characters in both the subject and the body. Many also take a shortcut and use 8bit transfer encoding for the body. I am 99.9% sure that this will work on today’s mail servers, but basic SMTP only really needs to support 3 transfer encodings - 7bit ascii, base64, and quoted-printable.

For my example I chose the latter. Quoted-printable has two distinct advantages: 1) It should be supported by all servers and 2) Unlike base64 it will keep characters that are 7bit ascii in ascii and only encode characters it needs to.

CREATE OR REPLACE PROCEDURE MAIL_TEST IS
  lv_server      VARCHAR2(255) := '<your mail server>';
  lv_rcpt        VARCHAR2(255) := 'to@example.com';
  lv_from        VARCHAR2(255) := 'from@example.com';
  lv_subject     VARCHAR2(255) := 'Le chien paresseux a sauté';
  lv_message     VARCHAR2(255) := 'Voix ambiguë d''un coeur qui au zéphyr préfère les jattes de kiwis';
  lv_conn        UTL_SMTP.CONNECTION;
BEGIN
  lv_Conn := UTL_SMTP.Open_Connection(lv_server);
  UTL_SMTP.Helo(lv_conn, lv_server);
  UTL_SMTP.Mail(lv_conn, lv_from);
  UTL_SMTP.Rcpt(lv_conn, lv_rcpt);
  UTL_SMTP.OPEN_DATA(lv_conn);
  UTL_SMTP.WRITE_DATA(lv_conn, 'Subject: =?UTF-8?Q?' ||
                                UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(lv_subject))) ||
                                '?=' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn, 'MIME-version: 1.0' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn, 'Content-Type: text/html;charset=utf-8' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn, 'Content-Transfer-Encoding: quoted-printable '|| UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn, 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss' ) ||' -0800 (GMT)' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn, 'From: ' || lv_from || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn, 'To: ' || lv_rcpt || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(lv_conn,  UTL_TCP.CRLF);
  UTL_SMTP.WRITE_RAW_DATA(lv_conn, UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(lv_message)));
  UTL_SMTP.WRITE_DATA(lv_conn, UTL_TCP.CRLF);
  UTL_SMTP.CLOSE_DATA(lv_conn);
  UTL_SMTP.QUIT(lv_conn);
END;

If you wanted to use 8bit ascii you would change the data and transfer encoding header to this:

UTL_SMTP.WRITE_DATA(lv_conn, 'Content-Transfer-Encoding: 8bit'|| UTL_TCP.CRLF);
UTL_SMTP.WRITE_RAW_DATA(lv_conn, UTL_RAW.CAST_TO_RAW(UTL_TCP.CRLF || lv_message || UTL_TCP.CRLF));

Finally, you can also use base64 encoding for the subject:

  UTL_SMTP.WRITE_DATA(lv_conn, 'Subject: =?UTF-8?B?' ||
                                UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(lv_subject))) ||
                                '?=' || UTL_TCP.CRLF);

Calling me a PL/SQL developer would be an insult to PL/SQL developers everywhere. I apologize in advance for any perceived lack of style. Most of the variables would be sourced from parameters passed in to the procedure or database values.

               

This entry was posted on Wednesday, April 18th, 2007 at 2:45 pm by steveny and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.