Home » SQL & PL/SQL » SQL & PL/SQL » Interesting unicode problem
Interesting unicode problem [message #220636] Wed, 21 February 2007 08:17 Go to next message
shamil
Messages: 3
Registered: November 2006
Location: Azerbaijan
Junior Member
Hope someone will help me to solve this problem.

I have a stored procedure called sendmail which i use it to send unicode mail to outlook users.

sendmail(p_sender_email => :p_sender_email,
p_sender_alias => :p_sender_alias,
p_recepient_email => :p_recepient_email,
p_recepient_alias => :p_recepient_alias,
p_subj => :p_subj,
p_body => :p_body,
p_mailserv => :p_mailserv);

Before now i used it directly from php script. And the unicode p_subj and p_body variables where selected from oracle database within php script.

As you see it is not advantageous to do so(select data from database and pass same data to database again).
I have a table called mails which stores mail subjects and contents.
Now i have created another procedure called send and i give the row id of the mails table as parameter to procedure instead of p_subj and p_body. In this procedure i select subject and body of mail into p_subj,p_body and then call sendmail. But now the mail doesn't display unicode characters correctly.

What is wrong here?
When selecting p_subj and p_body from databse with php it works but when from another stored procedure it doesnt work.

Thanks in advance.
Re: Interesting unicode problem [message #220741 is a reply to message #220636] Wed, 21 February 2007 19:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The MIME type you use in your email should be compatible with your database characterset. This example uses us-ascii.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1739411218448

If your data is in char/varchar2/clob etc, then you need to know the database characterset. If you store your data in nchar/nvarchar/nclob etc, you need to know the NHAR characterset.

select parameter, value
  from v$nls_parameters
  where parameter like '%CHARACTERSET%'


You can't refer to the Oracle characterset names directly - check on the web to see what the name means. e.g. AL32UTF8 means All chatacrters, 32 bit, UTF-8 encoding (I think). You need to see what the equivalent MIME characterset name is.

Re: Interesting unicode problem [message #220742 is a reply to message #220741] Wed, 21 February 2007 19:21 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Aslo - just because a character show correcty in some interface isn't an accurate indication of what character is actually stored in the DB. Do a dump on the data (using substr if necessary).

Select my_col, dump(my_col, 1010), dump(my_col, 1016) from my_tab...
Re: Interesting unicode problem [message #220778 is a reply to message #220636] Thu, 22 February 2007 01:59 Go to previous messageGo to next message
shamil
Messages: 3
Registered: November 2006
Location: Azerbaijan
Junior Member
Thank you for reply.
First i want to correct my mistake on my post but don't know if this will affect your answer. I have said i select
data from database via php but in this case it was static variable in php script. I selected it from database to find if it works but it didn't.

And i want to add that if subject is not uniocode and body part is unicode it works. If mime type is not compatible with oracle database characterset then it wouldn't display body correctly i think. But i have another clue against this. As problem was with only subject part i tried to edit some related procedure code and tested if it works.

i changed the following procedure line
subj raw(2000) := utl_raw.cast_to_raw('Subject: ' || p_subj || UTL_TCP.CRLF);

to
subj raw(2000) := utl_raw.cast_to_raw('Subject: ' || p_subj);

And it worked. It means that oracle adds Carriage Return Line Feed to end of the selected data. But now it doesn't work if i give subject as a parameter from php. Now i changed it like this to solve this problem.
subj raw(2000) := utl_raw.cast_to_raw('Subject: ' || p_subj || ' ' || UTL_TCP.CRLF);
This solves my problem but still i call this a bad way to solve problem and curious about such behaviour of oracle.

Thanks, Shamil
Re: Interesting unicode problem [message #221099 is a reply to message #220778] Fri, 23 February 2007 19:04 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
When you say the subject isn't Unicode - I assume you mean it's plain 7 bit Ascii. If so that's fine. You can't have 2 different charactersets for the subject and body anyway.

UTF-8 is a superset of 7 bit Ascii, so your subject would be valid in UTF-8 encoding of Unicode too. Most/all ANSI, ISO based charactersets (like ISO-1, Microsoft Latin 1) are supersets of 7 bit Ascii.

You should probably define CRLF as chr(10), not chr(10)||chr(13) too.
Re: Interesting unicode problem [message #221125 is a reply to message #221099] Sat, 24 February 2007 01:56 Go to previous message
shamil
Messages: 3
Registered: November 2006
Location: Azerbaijan
Junior Member
andrew again wrote on Sat, 24 February 2007 05:04
When you say the subject isn't Unicode - I assume you mean it's plain 7 bit Ascii. If so that's fine. You can't have 2 different charactersets for the subject and body anyway.

UTF-8 is a superset of 7 bit Ascii, so your subject would be valid in UTF-8 encoding of Unicode too. Most/all ANSI, ISO based charactersets (like ISO-1, Microsoft Latin 1) are supersets of 7 bit Ascii.

You should probably define CRLF as chr(10), not chr(10)||chr(13) too.

Hi again.
I changed the line to
subj raw(2000) := utl_raw.cast_to_raw('Subject: ' || p_subj || chr(10));
But it didn't work.
Previous Topic: Appending blobs
Next Topic: updation from diffrent users
Goto Forum:
  


Current Time: Mon Dec 05 08:51:57 CST 2016

Total time taken to generate the page: 0.18759 seconds