Problem with accented characters encoding between client and oracle server

Question

I've got an oracle database which NLS_CHARACTERSET is AL32UTF8. I've got an application (ASP.NET C#) on a client which NLS_LANG is FRENCH_FRANCE.WE8MSWIN1252.

The accented characters are well displayed on the application. However, when there is an accented character in a WHERE clause, oracle doesn't find the data, although it's there.

I found that "é" was written in the database as "c3a9". This is done on the server (SqlDeveloper) so it works fine because there is no conversion yet between character sets. accented characters preview

However, in my application on the client, an SQL request with a clause WHERE which contains an accented character doesn't work. For exemple I have this request :

string request = "UPDATE fonction SET libelle = '" + new_libelle.Replace("'", "''") + "' WHERE libelle = '" + old_libelle.Replace("'", "''") + "'";

If there is an accented character in old_libelle, oracle doesn't find the data and so the update is not executed.

I cannot change the NLS_CHARACTERSET of the database because it's used by many other applications and I don't want side effects to appear.

How to fix this?

The encoding is correct for UTF8, and your post contains a where clause with accents that works just fine. You'll need to provide more information about what doesn't work. — Mat, Mar 25 '19 at 09:11
And how is that string getting encoded by your application? Also, use bind variables and prepared statements, manually concatenating strings like that hasn't been a good practice since forever. — Mat, Mar 25 '19 at 09:51
As @Mat said, use paramters. Example https://www.devart.com/dotconnect/oracle/docs/Parameters.html — mortb, Mar 25 '19 at 09:54
How did you set your `NLS_LANG`? In Registry or as environment variable? — Wernfried Domscheit, Mar 25 '19 at 09:56
@Wernfried Domscheit I'm using msdaora provider. I didn't set NLS_LANG. But I can see it by using regedit. — Sebriniel, Mar 25 '19 at 10:29
msdaora provider is outdated for ages, you should not use it. It does not support Unicode, however for some french characters it should work. — Wernfried Domscheit, Mar 25 '19 at 10:41
@Wernfried Domscheit Haha I'm a junior developer so I did as other applications are done in my company :) What provider can I use instead? — Sebriniel, Mar 25 '19 at 10:45
Check this list for available drivers: https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999 — Wernfried Domscheit, Mar 25 '19 at 11:51

score 0 · Answer 1 · answered Mar 25 '19 at 10:14

0

Check the encoding settings in your web environment, e.g. Response.ContentEncoding and/or Response.Charset

If you set NLS_LANG=FRENCH_FRANCE.WE8MSWIN1252 then you must set these values Encoding.WindowsCodePage (which should be CP-1252 in your case)

Otherwise, if you prefer Unicode, (most likely UTF-8) then set your NLS_LANG accordingly to FRENCH_FRANCE.AL32UTF8

answered Mar 25 '19 at 10:14

Wernfried Domscheit

54,457
9
76
110

In the web environment, the charset is 'utf-8'. I don't know how to set NLS_LANG directly in my application, can you explain a little more please? I can't change the NLS_LANG of the client because there are several other applications runing on it. – Sebriniel Mar 25 '19 at 10:30
In the command line when you start your application add `SET NLS_LANG=FRENCH_FRANCE.AL32UTF8` at the beginning, then it should be fine. Or use the ODP.NET Managed Driver (`Oracle.ManagedDataAccess.Client`) which is not `NLS_LANG` sensitive. – Wernfried Domscheit Mar 25 '19 at 10:44
But this is to do every time the application is run isn't it? My application will be used by users that are not used with coding, and it's not convenient for them to run a command line in order to make the application work. – Sebriniel Mar 25 '19 at 10:51
If you cannot set `NLS_LANG` for entire machine nor for the process then there is no other way than using the ODP.NET Managed Driver. – Wernfried Domscheit Mar 25 '19 at 11:53
I think I'm not far from using this driver. This is what I have : `conDB = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["oraclecs"].ToString());` `` – Sebriniel Mar 25 '19 at 13:17

Problem with accented characters encoding between client and oracle server

1 Answers1