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) := '';
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.
1
My english is really bad.
I tried this example into an Oracle 9 and it worked perfectly until I wrote a subject greater than 60 characters (approximattely). It truncates the subject at this position.
Any idea?
2
Ah yes. This is because quoted printable lines are not supposed to be over 60 characters long so oracle places an =\n after every 60 characters. This is the correct behavior, but breaks the subject.
A quick hack to fix it is:
This smashes the line back together again. It breaks the spec, and code that correctly constructs the quoted printable string should be used — but this worked everywhere I tried it.
3
Hi,
I have a stored procedure for sending email in html format and my database server is installed on unix platform.
I wanna insert a banner image in the email. I was told that I cannot put that banner image on the web server, so I cannot set a specific URL for the image in my stored procedure.
I tried to put that image on my database server, e.g /home/dev/images/banner.jpg. However, would it work if I specific that image link like /home/dev/images/banner.jpg in my SP? What should I do if i wanna make use of the image on that path? I saw some other websites that the others will specific the link like c:\dev\images\banner.jpg so they can send the image from that path.
What I should do? I got no ideas, thanks in advance for your help!
Cheers
Manfred
4
Awesome.. the solution worked like piece of cake.. Since yesterday I was sratching my head why the Characters from Frech/Spanish/Russian are getting garbled in the Subject line this did wonders..
Thanks for the solution.. I will to some more testing and post the results..
5
Thank you so much ~!!
I resolved my chronic sendmail problem.
I really appreciate your Blog ~!!
6
I absolutely admire your blog……the code is working well in many oracle version ,Thanks for sharing this nice info!!!!
7
i tried in oracle 10g, and the special characters are not the same in the final email. can anyone help me? any reason?
8
Thanks so much!
You saved my day!