0

I'm trying to retrieve data from an Oracle database into MS Access or Excel on to a Windows 7 PC. The data includes characters such as degree and diameter symbols. These are not displayed correctly on the PC. I can see that the data is stored correctly using the dump function in my query, and that it is the translation to the client character set that not bringing them in.

By looking at this query, and also querying the database parameters with SELECT * FROM NLS_database_PARAMETERS, I can see that the NLS_CHARACTERSET = US7ASCII. I think this is cause of the problem as the diameter symbol etc is not included in this character set.

But, I've checked the registry to see what NLS_LANG is set to, and it is ENGLISH_UNITED KINGDOM.WE8MSWIN1252. I know that I'm looking in the correct registry key because when I change the language/territory (to GERMAN_GERMANY for example) it does change for my queries.

I've also checked for an environment variable that is overriding this setting and couldn't find one. I trying creating an NLS_LANG environment variable with the same settings as above but that didn't make any difference.

My Windows code page is set to 1252. The database NLS_NCHAR_CHARACTERSET setting is AL16UTF16.

Any ideas of what to do next?

Christian Gollhardt
  • 16,510
  • 17
  • 74
  • 111
Steve Watkins
  • 11
  • 1
  • 6

1 Answers1

0

If NLS_CHARACTERSET is set to US7ASCII then you will never get any special characters from it. With NLS_NCHAR_CHARACTERSET=AL16UTF16 you should get any character, provided the data type of your column is NVARCHAR2 or NCHAR (not VARCHAR2)

Set your local NLS_LANG to ENGLISH_UNITED KINGDOM.AL32UTF8 then it should work.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks for the response Wernfried. My question is how do I set NLS_LANG to the correct characterset on the client? Nothing I've tried has worked. – Steve Watkins May 01 '15 at 20:33
  • In your registry or as environment variable as you did already – Wernfried Domscheit May 01 '15 at 20:55
  • Thanks Wernfried. I think I'll try it on a different PC. Editing the registry or creating an environment variable hasn't worked on this one... – Steve Watkins May 05 '15 at 09:05
  • Note, there are two locations: `HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\NLS_LANG` for 64 Bit and `HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\NLS_LANG` for 32 Bit. Set the correct one. – Wernfried Domscheit May 05 '15 at 09:45
  • Thanks for the additional information @Wernfried. I only have an HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1\ key in my registry. That makes sense I guess as I only have a 32 bit version of the ODBC driver. If I make an edit to the language and/or territory in this registry setting then the change is picked up in the query the next time I close and reopen Access. But the language stays as US7ASCII whatever I put in the registry entry. I don't understand why! – Steve Watkins May 06 '15 at 11:38
  • Sorry, I mean to say the characterset stays as US7ASCII (not the language). – Steve Watkins May 06 '15 at 11:45
  • The `NLS_CHARACTERSET` is a database value, this you cannot change (at least not in an easy way). `NLS_LANG` on your client is needed to properly translate your local characters (eg. Windows CP1252) to database characters (e.g. AL32UTF8). Only stuff like weekday names or error messages are defined by language and territory values. – Wernfried Domscheit May 06 '15 at 11:51
  • Ok, I understand a bit more now. I mistakenly thought the NLS_CHARACTERSET was the local client character set as defined by the last part of the NLS_LANG parameter. It looks like I can't display what client character set is actually being used as, according to the nls_lang_faq on the Oracle website, the part of NLS_LANG is NOT shown in any system table or view. So, if I assume that it is changing when I alter the value of NLS_LANG in my registry, why is the query not displaying the correct characters? – Steve Watkins May 07 '15 at 11:55