• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Special characters converts to ? in Oracle 10g databse

 
Fua Moua
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Our application has a functionality to upload users using a CSV. The code generates XML records from the CSV file to update/create records in the database. I noticed that users with special characters in their name (AARöN) are converted to ? in Oracle 10g database. The datatype for that particular column is VARCHAR2 (40 Byte). I checked the database. NLS_CHARACTERSET is AL32UTF8. NLS_NCHAR_CHARACTERSET is AL16UTF16. Any thoughts? Thanks in advance.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i think it is the AL..16.. that is the problem, I think it needs to also be AL..32.. for varchar to accept unicode characters.
This is based on an old forum disscussion
https://forums.oracle.com/forums/thread.jspa?threadID=356363

but if you can it would be worth trying out.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy Gibbons wrote:i think it is the AL..16.. that is the problem, I think it needs to also be AL..32.. for varchar to accept unicode characters.
This is based on an old forum disscussion
https://forums.oracle.com/forums/thread.jspa?threadID=356363

but if you can it would be worth trying out.

I got exactly the opposite impression from the discussion you referenced (it states that both encodings can accomodate Unicode). In any case, VARCHAR2 datatype uses NLS_CHARACTERSET encoding, while NVARCHAR2 uses NLS_NCHAR_CHARACTERSET. Since the column is declared as VARCHAR2, the AL16UTF16 encoding is not involved at all.

Regarding the original question: which tool are you using to display the data in the database? Make sure that tool can display the characters. For example the Windows console cannot display full Unicode at any time, but even other, GUI based tools might have problems. Check which encoding your client uses (this is one possible source of error) and whether the font used to display the data contains all Unicode characters (other possible source of error). If still in doubt, you can use the DUMP function to display how the data is actually stored in the database, though you might have tough time interpreting it.

If the data can be shown to be stored incorrectly, you should check how they are being written to it. I assume this is a Java process. Check whether the XML file is processed in the correct encoding; you could also display the value before writing it into the database to verify it contains the correct national character(s).

Edit: regarding the encodings, see also http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm and http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch2charset.htm#autoId9
 
Fua Moua
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank for you both for responding.

I'm using Toad to display the data with special characters. Toad can display the special characters correctly. What's odd is that I tested the same code on a different server and it inserted the special characters correctly. Both databases have the same character set.

The way our code works. There is a ScriptInterpreter code in a java class file that executes the javascript code in mapping.js then creates the xml request and then sends the request to the application. The XML is in UTF8. I can't pinpoint why it works on server and it doesn't.


 
Paul Clapham
Sheriff
Posts: 21583
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, so there's a web application involved as well? Then blaming the database could well be premature. This article: Character Conversions from Browser to Database is a bit old now but people are still making the same mistakes that it describes.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic