| ASCII in oracle 11g [message #630191] |
Mon, 22 December 2014 10:13  |
 |
swati.pale
Messages: 3
Registered: December 2014
Location: india
|
Junior Member |
|
|
I have two oracle 11g databases.
I am checking special character 'é' in both.
In UTF16 database, if I select the value from table, I get ASCII code as 233 but if I directly query the special character , it shows 50089.
In UTF8 database, its 50089 in all the cases.
From where first database is showing 233 value?
Database1 is AL16UTF16.
-----------------------
select chr(233) from dual; --> null
select chr(50089) from dual; --> é
select ascii('é') from dual; --> 50089
select ascii(substr(product_desc,2,1)) from htg_product_service where pr_level = 6; --> 233
Database2 is UTF8.
------------------------
select chr(233) from dual; --> null
select chr(50089) from dual; --> é
select ascii('é') from dual; --> 50089
select ascii(substr(productdescription,2,1)) from prdservice.init_prod_setup_reqd_attrib where productcode = 'dowe64'; --> 50089
|
|
|
|
| Re: ASCII in oracle 11g [message #630192 is a reply to message #630191] |
Mon, 22 December 2014 10:59  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
post results from both database from SQL below using COPY & PASTE
select asciistr(product_desc), product_desc from htg_product_service where pr_level = 6;
select asciistr(productdescription), productdescription from prdservice.init_prod_setup_reqd_attrib where productcode = 'dowe64';
[Updated on: Mon, 22 December 2014 11:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: ASCII in oracle 11g [message #630332 is a reply to message #630229] |
Wed, 24 December 2014 10:42 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
It's really important to understand that asciistr(table_column) or dump(table_column) tell you about what's actually stored in Oracle. When you quote a literal like select ascii('é') from dual; you're sending some string from your client to Oracle to process. They way the character looks on your screen is dependent on your font, your NLS locale settings etc. By the time it get's to the Oracle server it can already be screwed up and so the result of your function call can be wrong. I break these problems into stages - verify what's actually stored in Oracle using asciistr(some_table_column). Non ASCII7 characters are returned in their Unicode codepoint format (\00E9 is same a U+00E9). This site is excellent for looking up characters http://www.fileformat.info/info/unicode/char/e9/index.htm You'll also see the encoding in UTF-8, UTF-16 etc, and can get that from Oracle using dump(). Try something like:
select col1, vsize(col1), dump(col1, 1010) Decimal_bytes, dump(col1, 1016) Hex_Bytes, asciistr(col1) from tst;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
