Forum Stats

  • 3,816,290 Users
  • 2,259,165 Discussions
  • 7,893,442 Comments

Discussions

Character changing when inserting into Oracle

979621
979621 Member Posts: 6
edited Dec 15, 2012 6:50PM in Globalization Support
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.
Tagged:

Answers

  • orafad
    orafad Member Posts: 8,035 Silver Trophy
    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
    979621 Member Posts: 6
    edited Dec 12, 2012 9:43PM
    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
    orafad Member Posts: 8,035 Silver Trophy
    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
    orafad Member Posts: 8,035 Silver Trophy
    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
    979621 Member Posts: 6
    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
    orafad Member Posts: 8,035 Silver Trophy
    Run that code with the insert of temperature string, then query per above with dump() on the column value and post the result.
  • 979621
    979621 Member Posts: 6
    Here's the result
    +15.0¿C Typ=1 Len=7 CharacterSet=WE8ISO8859P1: 2d,31,38,2e,30,bf,43
  • Sergiusz Wolicki-Oracle
    Sergiusz Wolicki-Oracle Member Posts: 2,019 Employee
    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
    orafad Member Posts: 8,035 Silver Trophy
    edited Dec 15, 2012 6:50PM
    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.
This discussion has been closed.