9 Replies Latest reply: Dec 15, 2012 5:50 PM by orafad RSS

    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.
        • 1. Re: Character changing when inserting into Oracle
          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... ;
          • 2. Re: Character changing when inserting into Oracle
            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
            • 3. Re: Character changing when inserting into Oracle
              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.
              • 4. Re: Character changing when inserting into Oracle
                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.
                • 5. Re: Character changing when inserting into Oracle
                  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
                  • 6. Re: Character changing when inserting into Oracle
                    orafad
                    Run that code with the insert of temperature string, then query per above with dump() on the column value and post the result.
                    • 7. Re: Character changing when inserting into Oracle
                      979621
                      Here's the result
                      +15.0¿C Typ=1 Len=7 CharacterSet=WE8ISO8859P1: 2d,31,38,2e,30,bf,43
                      • 8. Re: Character changing when inserting into Oracle
                        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
                        • 9. Re: Character changing when inserting into Oracle
                          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.