Home » SQL & PL/SQL » SQL & PL/SQL » Send HTML email from Oracle (using UTL_SMTP)
icon4.gif  Send HTML email from Oracle [message #436047] Mon, 21 December 2009 03:42 Go to next message
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 #436159 is a reply to message #436047] Mon, 21 December 2009 22:07 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Am trying to send email from Oracle using utl_smtp

Ok.
whats the procedure you are executing?
what are your current character settings?
Quote:
i was receiving the Arabic text as '??????'
.
On which position you are getting this ?
What are the NLS parameter you have right now ?

Here are the two links hope those will help you out.
Good luck Smile

Html Mail

From asktom

Sriram Smile
Re: Send HTML email from Oracle [message #436162 is a reply to message #436159] Mon, 21 December 2009 22:41 Go to previous messageGo to next message
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 #436165 is a reply to message #436162] Mon, 21 December 2009 23:15 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
sorry ,i did n`t read the latest one before posting.

So now you already got the solution By tom referencing a link right?
Then what you tried?
Compare with this atleast...Demo mail


Thank you
sriram Smile

[Updated on: Mon, 21 December 2009 23:16]

Report message to a moderator

Re: Send HTML email from Oracle [message #436166 is a reply to message #436165] Mon, 21 December 2009 23:32 Go to previous messageGo to next message
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 Smile


Re: Send HTML email from Oracle [message #436169 is a reply to message #436166] Mon, 21 December 2009 23:50 Go to previous messageGo to next message
ramoradba
Messages: 2452
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 Smile

[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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7877
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7877
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7877
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 Go to previous messageGo to next message
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 Smile
Many thanks.


EDIT:

I have change to nvarchar2 in both side but no luck Smile, 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 Go to previous messageGo to next message
Frank
Messages: 7877
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 Go to previous messageGo to next message
meim
Messages: 35
Registered: July 2009
Member
Frank wrote on Tue, 22 December 2009 02:35
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



Hi again Smile

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 Go to previous messageGo to next message
meim
Messages: 35
Registered: July 2009
Member
I can send arabic text now with more simpler procedure Smile


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 #436400 is a reply to message #436399] Wed, 23 December 2009 06:16 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
congrats and thanks for the feedback

sriram Smile
Re: Send HTML email from Oracle [message #531188 is a reply to message #436399] Mon, 14 November 2011 05:47 Go to previous messageGo to next message
aallan
Messages: 150
Registered: October 2011
Senior Member
meim wrote on Wed, 23 December 2009 06:14
I can send arabic text now with more simpler procedure Smile


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 #531195 is a reply to message #531188] Mon, 14 November 2011 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post what is your problem we can't help to solve it.

Regards
Michel
Re: Send HTML email from Oracle [message #533021 is a reply to message #531188] Sun, 27 November 2011 06:48 Go to previous messageGo to next message
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 #533026 is a reply to message #533021] Sun, 27 November 2011 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use this procedure use UTL_MAIL package.

Regards
Michel
Re: Send HTML email from Oracle [message #533056 is a reply to message #533026] Mon, 28 November 2011 01:04 Go to previous messageGo to next message
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 #533057 is a reply to message #533056] Mon, 28 November 2011 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The example what I got is as

It is correctbut for the character set if you want arabic characters.

Regards
Michel
Re: Send HTML email from Oracle [message #533059 is a reply to message #533057] Mon, 28 November 2011 01:17 Go to previous messageGo to next message
rakutly
Messages: 6
Registered: November 2008
Location: Saudi Arabia
Junior Member
Michel Cadot wrote on Mon, 28 November 2011 01:11
Quote:
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
Re: Send HTML email from Oracle [message #533085 is a reply to message #533059] Mon, 28 November 2011 03:47 Go to previous message
rakutly
Messages: 6
Registered: November 2008
Location: Saudi Arabia
Junior Member
Hi Michel,
Thanks a lot. ARabic email is working now using UTL_MAIL

Thanks & Regards
Rakesh Mukundan
Previous Topic: Re: trigger problem (split from hijacked thread by bb)
Next Topic: Bucket Table for my Requirement !! (2 threads merged by bb)
Goto Forum:
  


Current Time: Fri Aug 29 15:06:19 CDT 2014

Total time taken to generate the page: 0.22308 seconds