Home » SQL & PL/SQL » SQL & PL/SQL » Send HTML email from Oracle (using UTL_SMTP)
Send HTML email from Oracle [message #436047] |
Mon, 21 December 2009 03:42  |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
Hi,
Am trying to send email from Oracle using utl_smtp.
My issue is regarding Arabic character.
I set the charset to windows-1256 and even tried with other different charset like UTF-8.
I used also: utl_smtp.write_raw_data(l_connection,utl_raw.cast_to_raw(
dbms_lob.substr(data).
Before i set all these changes to the procedure i was receiving the Arabic text as '??????'
However, after above modifications, the text display changed to something like this : ÇäÇÓààååÑêå
Does the problem still in the DB side or it can be coming from mail server ??
Thanks,
Meim
|
|
|
|
Re: Send HTML email from Oracle [message #436162 is a reply to message #436159] |
Mon, 21 December 2009 22:41   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
Hi ramoradba,
Thanks for your reply.
1. I am using the same procedure posted in the link you pasted here.
2. For now am getting this display ÇäÇÓààååÑêå after the changes i mentioned before.
3. My DB support Arabic character, the NLS is AR8ISO8859P6.
You can see me posting the same qsn to Tom ( one of the link you but here) and he answered:
SMTP uses 7bit ascii data. You would have to encode other data.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_smtp.htm#CHDCFFDI
Single byte data is all it can do, you'd have to send multibyte data as an attachment - base 64 encoded.
I have already read this , but already saying that for multibyte use to convert the data into RAW, and that's what i did.
Regards.
|
|
|
|
Re: Send HTML email from Oracle [message #436166 is a reply to message #436165] |
Mon, 21 December 2009 23:32   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
It is fine, thanks anyway for your help.
Well, what he suggests is to send as attachment which i don't want to do. the email that i want to send will consists of two parts a msg in English followed by Arabic msg.
But you know, the confused is that what is mentioned in the UTL_SMTP documentation that direct and there is nothing saying that we need an attachment.
Note that the original SMTP protocol communicates using 7-bit ASCII. Using UTL_SMTP, all text data (in other words, those in VARCHAR2) will be converted to US7ASCII before it is sent over the wire to the server. Some implementations of SMTP servers that support SMTP extension 8BITMIME [RFC1652] support full 8-bit communication between client and server. The body of the DATA command may be transferred in full 8 bits, but the rest of the SMTP command and response should be in 7 bits. When the target SMTP server supports 8BITMIME extension, users of multibyte databases may convert their non-US7ASCII, multibyte VARCHAR2 data to RAW and use the WRITE_RAW_DATA subprogram to send multibyte data using 8-bit MIME encoding.
my email server is support the multibyte as we can easily send arabic text without any problem in displaying issue.
You can see one of the review in tom page that someone did same steps i follow and he can success sending arabic text, and he didn't mention anything about attachment.
Thanks alot sriram 
|
|
|
Re: Send HTML email from Oracle [message #436169 is a reply to message #436166] |
Mon, 21 December 2009 23:50   |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
i didn` say like follow exactly whatever on the link.i asked you to post what you tried with a sample text(some part with english and some part with arabic) As asample text
And provide the exact out put from the session you executed that.And if you are not interested posting here some other will help you
sriram
[Updated on: Tue, 22 December 2009 00:06] Report message to a moderator
|
|
|
Re: Send HTML email from Oracle [message #436177 is a reply to message #436169] |
Tue, 22 December 2009 00:50   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
Ok then here is what am doing
1- Am using this procedure to send email, as mentioned in Tom page, with changes i mentioned in first post:
create or replace procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
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;
begin
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 );
utl_smtp.rcpt( l_connection, p_to );
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-1256' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Write the plain text portion of the email
/**
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);
**/
----------------------------------------------------
-- 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;' ||
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_raw_data(l_connection,
utl_raw.cast_to_raw(
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);
end;
/
2- Then i am doing simple test to use this procedure:
DECLARE
english_msg NVARCHAR2 (2000);
arabic_msg NVARCHAR2 (2000);
msg NVARCHAR2 (2000);
BEGIN
english_msg := '<br> <br>This is a friendly reminder for your contract expiration. your information is as following:
<br>Name: XXXXX
<br>Employee ID: 98347534
<br>Contract Expiration: 30-Dec-2009';
arabic_msg := '<br> <br> <p align="right" dir="rtl">
هذه الرسالة عبارة عن تذكير باقتراب وقت انتهاء عقدك لدينا . معلوماتك كالأتي:
<br> الاسم : ءءءءءءء
<br> الرقم الوظيفي : 38475348
<br> تاريخ انتهاء العقد : 30 ديسمبر 2009
</p>';
msg := english_msg || arabic_msg;
p_html_email('my_email@domain.com',--to 'my_email@domain.com', --from
'This is a test for seding arabic text via email',
NULL,
msg, -- body
NULL,
NULL);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
/
3- What I receive in my Inbox is this :
Quote:
This is a friendly reminder for your contract expiration. your information is as following:
Name: XXXXX
Employee ID: 98347534
Contract Expiration: 30-Dec-2009
çÐç ÇäÑÓÇäÉ ÙÈÇÑÉ Ùæ ÊÐãêÑ ÈÇâÊÑÇÈ èâÊ ÇæÊçÇÁ ÙâÏã äÏêæÇ . åÙäèåÇÊã ãÇäÃÊê:
ÇäÇÓå : ÁÁÁÁÁÁÁ
ÇäÑâå ÇäèØêáê : 38475348
ÊÇÑêÎ ÇæÊçÇÁ ÇäÙâÏ : 30 ÏêÓåÈÑ 2009
Regards.
|
|
|
Re: Send HTML email from Oracle [message #436188 is a reply to message #436177] |
Tue, 22 December 2009 01:14   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
- Is your email app capable of displaying arab characters?
- Does your email display as html or as text?
- If you save the message and load it into a browser, does it show the correct characters?
- Can you see any refeference to the used character encoding in the source of the received mail?
|
|
|
Re: Send HTML email from Oracle [message #436194 is a reply to message #436188] |
Tue, 22 December 2009 01:27   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
Frank wrote on Tue, 22 December 2009 01:14
- Is your email app capable of displaying arab characters?
- Does your email display as html or as text?
- If you save the message and load it into a browser, does it show the correct characters?
- Can you see any refeference to the used character encoding in the source of the received mail?
1- yes it can display arabic character, we do exchange arabic msg without any problem
2- Html
3- No
4- I am not sure about your 4th qsn, but after saving the email, i check the source of the page, it is using charset = windows-1256 which is supporting arabic.
Thanks.
|
|
|
Re: Send HTML email from Oracle [message #436196 is a reply to message #436194] |
Tue, 22 December 2009 01:33   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Ok, that might rule out one part of the problem, being the mailclient. Next step would be to check if the database is capable of storing/handling these characters. Gradually work your way around the problem until you find the root-cause.
For example, try to save the message you want to send in a table and see how it is stored; log the message to a file/table/output to check the contents. Do this at multiple phases of the process to track where the error occurs.
This is debugging a problem, which can be hard work. But the joy of finding the culprit will make it all worth it! (even if it turns out to be some stupid mistake)
|
|
|
Re: Send HTML email from Oracle [message #436202 is a reply to message #436196] |
Tue, 22 December 2009 01:49   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
I don't mind if the cause of the problem is stupid at all xD, i just want to figure it out.
I did many things just to figure out what is causing the problem.
1- i have test this with other ppl, and all getting the same result.
2- i have already did what you are suggesting. my database is supporting arabic and we have this working find with forms, reports, and working through SQL*plus.
in another example am working in, actually what i did is selecting some arabic data from database and concatenate it with hard coded arabic text and save them all in a one variable, then i send this variable content to my email and save it at the same time to a table.
in the table the data displays fine, but the email display as junk!
|
|
|
Re: Send HTML email from Oracle [message #436204 is a reply to message #436202] |
Tue, 22 December 2009 01:53   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Did you also try what I suggested? Log the contents of the message at different stages in the process?
One thing that jumps out for me for example is that you use varchar2 in the html_email procedure, whereas you use nvarchar2 in your calling procedure.
Now, if you would log the contents of p_html, you might find something. I really don't know for sure where your problem is, I just wanted to show you how to get to it: try to exclude parts that are not causing the problem, so you can focus on the important parts.
|
|
|
Re: Send HTML email from Oracle [message #436209 is a reply to message #436204] |
Tue, 22 December 2009 02:15   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
Is saving the content of p_html in different stages into table is enough ? i did that and it looks file.
but if you mean log by different way can you give a hint please.
one more question, the p_html later on is included in l_body_html which have the type CLOB, do you think this will cause any problem?
Really thanks Frank, appreciate your suggestion at least there is another brain thinking with me 
Many thanks.
EDIT:
I have change to nvarchar2 in both side but no luck , even with returning things to varcahr2 in both side as well.
[Updated on: Tue, 22 December 2009 02:20] Report message to a moderator
|
|
|
Re: Send HTML email from Oracle [message #436210 is a reply to message #436209] |
Tue, 22 December 2009 02:35   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
meim wrote on Tue, 22 December 2009 09:15
one more question, the p_html later on is included in l_body_html which have the type CLOB, do you think this will cause any problem?
Maybe, maybe not. See if using an NCLOB helps.
Again, store or log the contents to see if they still represent arabic characters
|
|
|
Re: Send HTML email from Oracle [message #436280 is a reply to message #436210] |
Tue, 22 December 2009 11:53   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
Frank wrote on Tue, 22 December 2009 02:35meim wrote on Tue, 22 December 2009 09:15
one more question, the p_html later on is included in l_body_html which have the type CLOB, do you think this will cause any problem?
Maybe, maybe not. See if using an NCLOB helps.
Again, store or log the contents to see if they still represent arabic characters
Hi again 
NCLOB brings null msg! eh!
i stored the message in different stages, even after i send it, the arabic characters display is fine.
I was even trying to send the message to different account ( rather than my work account) like to gmail or hotmail account, however, i couldn't proceed with this since it gave me error with something about relay permitted, which i read somewhere else that the smtp server doesn't let the msg go outside the domain, any idea about this ?
Thanks.
|
|
|
Re: Send HTML email from Oracle [message #436399 is a reply to message #436280] |
Wed, 23 December 2009 06:14   |
meim
Messages: 35 Registered: July 2009
|
Member |
|
|
I can send arabic text now with more simpler procedure 
CREATE OR REPLACE PROCEDURE send_email(
pi_from IN Varchar,
pi_to IN VARCHAR,
pi_cc IN Varchar,
pi_subj IN VARCHAR,
pi_msg CLOB
)
IS
conn utl_smtp.connection;
lv_mailhost varchar2(1000);
lv_port number;
BEGIN
lv_mailhost := 'mydomain';
lv_port := 25;
conn := utl_smtp.open_connection(lv_mailhost, lv_port);
utl_smtp.helo(conn, lv_mailhost);
utl_smtp.mail(conn, pi_from);
utl_smtp.rcpt(conn, pi_to);
IF pi_cc is not null THEN
utl_smtp.rcpt(conn, pi_cc);
END IF;
utl_smtp.open_data(conn);
utl_smtp.write_data(conn, 'MIME-version: 1.0' || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Type: text/html; charset=iso-8859-6' ||
utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' ||
utl_tcp.CRLF);
utl_smtp.write_data(conn, 'From:' ||pi_from || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'To:' ||pi_to || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Cc:' ||pi_cc || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Reply-To:' ||pi_from || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Subject:' ||pi_subj|| utl_tcp.CRLF);
utl_smtp.write_data(conn, utl_tcp.crlf);
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(pi_msg));
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
/
|
|
|
|
Re: Send HTML email from Oracle [message #531188 is a reply to message #436399] |
Mon, 14 November 2011 05:47   |
 |
aallan
Messages: 150 Registered: October 2011
|
Senior Member |
|
|
meim wrote on Wed, 23 December 2009 06:14I can send arabic text now with more simpler procedure 
CREATE OR REPLACE PROCEDURE send_email(
pi_from IN Varchar,
pi_to IN VARCHAR,
pi_cc IN Varchar,
pi_subj IN VARCHAR,
pi_msg CLOB
)
IS
conn utl_smtp.connection;
lv_mailhost varchar2(1000);
lv_port number;
BEGIN
lv_mailhost := 'mydomain';
lv_port := 25;
conn := utl_smtp.open_connection(lv_mailhost, lv_port);
utl_smtp.helo(conn, lv_mailhost);
utl_smtp.mail(conn, pi_from);
utl_smtp.rcpt(conn, pi_to);
IF pi_cc is not null THEN
utl_smtp.rcpt(conn, pi_cc);
END IF;
utl_smtp.open_data(conn);
utl_smtp.write_data(conn, 'MIME-version: 1.0' || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Type: text/html; charset=iso-8859-6' ||
utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' ||
utl_tcp.CRLF);
utl_smtp.write_data(conn, 'From:' ||pi_from || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'To:' ||pi_to || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Cc:' ||pi_cc || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Reply-To:' ||pi_from || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Subject:' ||pi_subj|| utl_tcp.CRLF);
utl_smtp.write_data(conn, utl_tcp.crlf);
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(pi_msg));
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
/
Dear,
i user this procedure but has a problem with arabic ??
please help..
|
|
|
|
Re: Send HTML email from Oracle [message #533021 is a reply to message #531188] |
Sun, 27 November 2011 06:48   |
rakutly
Messages: 6 Registered: November 2008 Location: Saudi Arabia
|
Junior Member |
|
|
Dear,
I am getting some junk characters in email as follows when I used the procedure send_email which was explained earlier posts
شرا رد لتزل
can you please help me in this.
Thanks & Regards
Rakesh Mukundan
|
|
|
|
Re: Send HTML email from Oracle [message #533056 is a reply to message #533026] |
Mon, 28 November 2011 01:04   |
rakutly
Messages: 6 Registered: November 2008 Location: Saudi Arabia
|
Junior Member |
|
|
Hi Michel,
I never used UTL_MAIL package. If you have an example can you please help me with that. The example what I got is as
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
UTL_MAIL.send(sender => 'me@address.com',
recipients => 'you@address.com',
subject => 'Test Mail',
message => 'Hello World',
mime_type => 'text; charset=us-ascii');
END;
Thanks & Regards
Rakesh Mukundan
|
|
|
|
Re: Send HTML email from Oracle [message #533059 is a reply to message #533057] |
Mon, 28 November 2011 01:17   |
rakutly
Messages: 6 Registered: November 2008 Location: Saudi Arabia
|
Junior Member |
|
|
Michel Cadot wrote on Mon, 28 November 2011 01:11Quote:The example what I got is as
It is correctbut for the character set if you want arabic characters.
Regards
Michel
Hi,
This is the arabic character set charset=iso-8859-6. As UTL_MAIL is not enabled, I have requested DBA to enable that. Once it is done I will test and will give you the updates if it was success or errored.
Thanks & Regards
Rakesh Mukundan
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 00:26:11 CST 2025
|