Inserting French character into Oracle gets converted into some junk characters

3
votes

Using PL/SQL Developer, I'm able to insert French character in my Oracle database without any error.

Querying:

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_NCHAR_CHARACTERSET';

Output: AL16UTF16

But when i retreive the data using select statement it get converted into some junk characters, For eg:

système gets converted to système and so on....

Any suggestion/workaround will be appreciated.

1
oracleunicodeplsqldeveloper
What is your OS? What is your locale-specific NLS characterset? it is a display issue, your client is unable to display it properly. - Lalit Kumar B
I'm using Windows 7. Pl/SQL Character Sets Character size: 4 byte(s) CharSetID: 873 NCharSetID: 2000 Unicode Support: True NLS_LANG: AMERICAN_AMERICA.WE8MSWIN1252 NLS_CHARACTERSET: AL32UTF8 NLS_NCHAR_CHARACTERSET: AL16UTF16 - donstack
How are you inserting it? How are you querying it? What is the column data type? What does dump() show is actually stored? - Alex Poole
initially it was only english, updated to add other languages, using Update statement. Column type is SYS.XMLTYPE, dump() is showing some numeric data Typ=58 Len=118: - donstack
Looks like you saved yor sql file as UTF-8. In this case your NLS_LANG must also be UTF8. - Wernfried Domscheit

1 Answers

4
votes

The issue was due to different values in NLS_LANGUAGE at client and server.

At server it was: AMERICAN use following query to read the parameters:

SELECT * FROM nls_database_parameters

At client it was: AMERICAN_AMERICA.WE8MSWIN1252

In PL/SQL Developer Help->About, click on Additional Info button and scroll down.

What i observed other thing, while trying to fix the issue: The characters were not converting to junk characters in first update. But when i retreive them(which contains non-ascii characters) and update again, then they are converting to junk characters.