Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Displaying the hex value of a string from a oracle varchar2?

We are having problems with text that is encoded in some different ways but kept in a single column in a table. Long story. On MySQL, I can do "select hex(str) from table where" and I see the bytes of the string exactly as I set them.

On Oracle, I have a string which starts with the Turkish character İ, which is the Unicode character 0x0130 "LATIN CAPITAL LETTER WITH DOT ABOVE". This is in my printed copy of the Unicode Version 2.0 book. In UTF-8, this character is 0xc4b0.

We have very old client apps we need to support. They would send us this text in "windows-1254". We used to just close our eyes, store it, and hand it back later. Now we need the Unicode, or are being given the Unicode.

So I have:

SQL> select id, name from table where that thing;

ID     NAME
------ ------------------------
746    Ý

This makes sense because the "İ" is 0xdd in windows-1254 and 0xdd in wondows-1252 is "Ý". My terminal is presumably set to the usual windows-1252.

But:

SQL> select id, rawtohex(name) from table where that thing;

ID     RAWTOHEX(NAME)
------ ------------------------
746    C39D

There seems to be no equivalent to the hex(name) function in MySQL. But I must be missing something. What am I missing here?

My java code has to take the utf8 that I am supplied and save a utf8 copy and a windows-1252 copy. The java code gives me:

bytes (utf8):  c4 b0
bytes (1254):  dd

Yet, when I save it, the client does not get the correct character. And when I try to see what Oracle has actually stored, i get the garbage seen above. I have no idea where the C39D is coming from. Any suggestions?

We have ojdbc14.jar built into all of our applications and we are connecting to a database that says it is "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production".

like image 379
Ray Kiddy Avatar asked Sep 09 '13 15:09

Ray Kiddy


People also ask

What does VARCHAR2 255 mean?

Recently a fellow database architect claimed that in Oracle the type VARCHAR2(255) means a string of 255 bytes, not characters. There is not much difference between the two in the English-speaking world. It matters though if you want to handle people with names like Kołłątaj.

How do I find a character in a string in Oracle?

The Oracle INSTR function is used to search string for substring and find the location of the substring in the string. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring.

How does VARCHAR2 work in Oracle?

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum column length (in bytes, not characters) between 1 and 2000 for the VARCHAR2 column.

What is the default value for VARCHAR2 in Oracle?

A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes. It means that for a single-byte character set, you can store up to 4000 characters in a VARCHAR2 column. By default, Oracle uses BYTE if you don't explicitly specify BYTE or CHAR after the max_size .


2 Answers

I have bytes in UTF-8 to begin.

String strFromUTF8 = new String(bytes, "UTF8");
byte[] strInOldStyle = strFromUTF8.getBytes("Cp1254");

With MySQL, I am done. I takes these bytes, turn them into a hex string and do an update with unhex(hexStr). This allows me to put the legacy bytes into a varchar column.

With Oracle, I must do:

String again = new String(strInOldStyle, "Cp1254");
byte[] nextOldBytes = again.getBytes("UTF8");

Now, I can do an update and get the bytes into a varchar2 column with:

update table set colName = UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('hexStr')) where ...

Strange, no? I am sure I have made this more complex than it needed to be.

What we see is this, though,

"İ" in UTF-8 == 0xc4d0
"İ" in Cp1254 == 0xdd == "Ý" in Cp1252
"Ý" in UTF-8 == 0xc3d9

So, if I get the string "İ" and do:

update table set name = UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('C3D9')) where ...

Then our legacy client gives us a "İ". Yep. It works.

like image 40
Ray Kiddy Avatar answered Oct 22 '22 13:10

Ray Kiddy


Use the dump function to see how Oracle stores data internally.

You seem to have a misunderstanding on how Oracle treats VARCHAR2 characters set conversions: you can't influence how Oracle stores its data physically. (Also if you haven't already, it's helpful to read: The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets).

Your client speaks to Oracle only in binary. In fact all systems exchange information in binary only. To understand each others, it is necessary that both systems know what language (character set) is being used.

In your case we can reconstruct what happens:

  1. Your client sends the byte dd to Oracle and says it is windows-1252 (instead of 1254).
  2. Oracle looks up its character set table and sees that this data is translated to the symbol Ý in this character set.
  3. Oracle logically stores this information in its table.
  4. Since Oracle is setup in UTF-8, it converts this data to the UTF-8 binary reprensentation of Ý:

    SQL> SELECT rawtohex('Ý') FROM dual;
    
    RAWTOHEX('Ý')
    --------------
    C39D
    
  5. Oracle stores C39D internally.

As you can see, the problem comes from the first step: there is a problem of setup. As long as you don't fix this, the systems won't be able to successfully dialogue.

The conversion is automatic when you use VARCHAR2 because this datatype is a logical text symbol interface (you have next to no control over forcing the actual binary data being stored).

like image 184
Vincent Malgrat Avatar answered Oct 22 '22 14:10

Vincent Malgrat