This discussion is archived
9 Replies Latest reply: Dec 15, 2012 3:50 PM by orafad RSS

Character changing when inserting into Oracle

979621 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points