Character changing when inserting into Oracle

979621

I am debugging an old web service which is using Enterprise Services. The service is sitting in IIS 6 using .NET 4. A new problem appeared when an insert statement was generated that attempted to insert a string which included a degree symbol. When executed, the degree symbol is changed to an upside down question mark. No error messages were generated.

The strange thing is that I took a copy of the assemblies and dropped them on a test machine (same OS and IIS) and the insert statement worked. I've been trying to find the difference between the two machines to determine why this is happening. It's the same code running so I know the code is not the issue. I also know that it's using the same Microsoft Oracle provider. I've examined the region/language settings and the config files in the core framework folders and everything matches.

Anyone have any idea what I'm missing or where I can look for additional settings?

Additional information:

NLS_CHARACTERSET - WE8ISO8859P1
NLS_LANGUAGE - AMERICAN
NLS_TERRITORY - AMERICA
NLS_CALENDAR - GREGORIAN
NLS_DATE_FORMAT - DD-MON-RR
NLS_DATE_LANGUAGE - AMERICAN
NLS_SORT - BINARY
NLS_TIME_FORMAT - HH.MI.SSXFF AM
This is the connection string from the object: OleDbConnection("Provider=MSDAORA.1;...

And the oracle client installed is version 10.

orafad

If you are using the MS provider, why post in this forum? (for OraOLEDB provider) Also, if dotNet,, why does it use Ole db provider and not ODP.Net?

If the character in question is the Degree sign, U+00B0, it does seem to exist in character repertoire of WE8ISO8859P1 i.e. Iso Latin-1.
What is the char set part of NLS_LANG for the Oracle Client on the web service/IIS host? What is the langiage environment and ACP on the same Windows host?

To verify what character data is actually stored in the db, use

select column, dump(column, 1016) from table where suitable_condition_for_row_selection... ;

979621

I'll check out what you've suggested but to your question why does it use one technology versus the other, I don't know. The person who did this is long gone and there is no documentation or explanation for the way anything is done - it's horrible!

As far as posting in the correct forum, I'm not sure how my post ended up in the Globalization Support area - it was in the Oracle Database -> Windows and .NET -> Oracle Provider for OLE DB.

Thanks

Edited by: 976618 on Dec 12, 2012 8:41 PM

orafad

John_B wrote:
I'll check out what you've suggested but to your question why does it use one technology versus the other, I don't know. The person who did this is long gone and there is no documentation or explanation for the way anything is done - it's horrible!

Sounds like same as how it usually looks when one gets to take over some system setup

As far as posting in the correct forum, I'm not sure how my post ended up in the Globalization Support area - it was in the Oracle Database -> Windows and .NET -> Oracle Provider for OLE DB.

As it relates to character conversion, the post was incorrectly placed in the first place, why I noted that. Later I guess the thread was moved by a moderator to this forum (Globalization).
So, please continue here and close the re-post in Ole db forum.


Look into NLS_LANG settings per above. There we'll likely find a "mismatch" and cause of character replacement.

orafad

John_B wrote:
... insert statement was generated that attempted to insert a string which included a degree symbol.

Please explain how statements are "generated" and include and example.

But I still believe that a correct indication to Oracle of client/application character set will take care of this issue.

I've examined the region/language settings

Which are?

And the oracle client installed is version 10.

Post full version, at least four positions, please.

979621

How statements are generated:

Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand

Dim insertScript As String = "Insert into CONTAINERS (TEMP) VALUES ('+15.0°C')"
myConnection = New OleDbConnection("Provider=MSDAORA.1;Data Source=oracle.world;User ID=username; Password=password;")
myConnection.Open()
myCommand = New OleDbCommand(insertScript)
myCommand.Connection = myConnection
myCommand.ExecuteNonQuery()

regional/language: United States/English (United States) - US

Oracle client version: 10.2.0.1.0

orafad

Run that code with the insert of temperature string, then query per above with dump() on the column value and post the result.

979621

Here's the result
+15.0¿C Typ=1 Len=7 CharacterSet=WE8ISO8859P1: 2d,31,38,2e,30,bf,43

Sergiusz Wolicki-Oracle

As orafad already asked you to, check the value of NLS_LANG in the Registry (HKLM\SOFTWARE\Oracle\<branch for your Oracle Home>) and also check whether NLS_LANG is defined among environment variables:

C:\> set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\someuser\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
.
.
.

-- Sergiusz

orafad

A little demo to show why we need to know client side (IIS, in your case) env and NLS_LANG setting.

First insert a character value with a NLS_LANG setting that lies to Oracle about client environment, followed by a proper insert. In the former case "input" is cp 1252, but Oracle is told it is ASCII (7-bit). Char set indicator could be set to ASCII or default to same setting.

C:\>chcp
Active code page: 1252

C:\>set nls_lang=.us7ascii

C:\>sqlplus test/test

SQL*Plus: Release 11.2.0.2.0 Production on Sun Dec 16 00:36:51 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> desc testchar
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(7 CHAR)

SQL> insert into testchar values('-12°C');

1 row created.

SQL> select a from testchar;

A
-------
-12?C

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

C:\>chcp
Active code page: 1252

C:\>set nls_lang=.we8mswin1252

C:\>sqlplus test/test

SQL*Plus: Release 11.2.0.2.0 Production on Sun Dec 16 00:38:00 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> insert into testchar values('-12°C');

1 row created.

SQL> select a from testchar;

A
-------
-12¿C
-12°C


SQL> select dump(a,1016) from testchar;

DUMP(A,1016)
--------------------------------------------------------------------------------
Typ=1 Len=7 CharacterSet=AL32UTF8: 2d,31,32,ef,bf,bd,43
Typ=1 Len=6 CharacterSet=AL32UTF8: 2d,31,32,c2,b0,43

Note how the 'replacement character' (? and ¿) differs depending on expected client environment with NLS_LANG.
And also since this db was UTF-8, stored character is 0xef,bf,bd instead of 0xbf as in a single-byte character set.