19

I'm working on Windows OS, I know that this setting is stored in the registry. The problem is that the registry path changes from version to version, browsing though that bunch of registry keys is definitly not a good idea.

I can get the NLS_LANG of the server with SELECT USERENV ('language') FROM DUAL.

I'd like to compare that with the client setting and show a warning when they don't match, just like Pl/Sql Developer does.

Ollie
  • 17,058
  • 7
  • 48
  • 59
Rafael Piccolo
  • 2,328
  • 1
  • 18
  • 29
  • Have you looked at NLS_SESSION_PARAMETERS, NLS_DATABASE_PARAMETERS, and NLS_INSTANCE_PARAMETERS? http://docs.oracle.com/cd/E11882_01/server.112/e25513.pdf – Glenn Jul 17 '12 at 13:02
  • 2
    Well, they give me everything... except the NLS_LANG of the client – Rafael Piccolo Jul 17 '12 at 13:10
  • 2
    Does the session view not give what you are looking for? select * from nls_session_parameters where parameter = 'NLS_LANGUAGE'; alter session set nls_language=german; select * from nls_session_parameters where parameter = 'NLS_LANGUAGE'; – Glenn Jul 17 '12 at 13:22
  • What interface are you using on the client? – Ollie Jul 17 '12 at 13:50
  • @Glenn that's just part of the information. NLS_LANG gives me _.. NLS_LANGUAGE brings the language and territory, but not the character set, and that is the most important for me. – Rafael Piccolo Jul 17 '12 at 17:38
  • @Ollie I'm not sure if that's what you asked, it's a windows desktop application using the Client of Oracle Standard. It loads a bunch of DLLs of the Oracle "bin" folder like oraclient10.dll – Rafael Piccolo Jul 17 '12 at 17:39
  • SQL>@[%NLS_LANG%] SP2-0310: unable to open file "[NORWEGIAN_NORWAY.WE8MSWIN1252]" – Bjarte Brandt Jul 17 '12 at 18:28

3 Answers3

9

This is what I do when I troubleshoot encoding-issues. (The NLS_LANG value read by sqlplus): 

SQL>/* It's a hack. I don't know why it works. But it does!*/
SQL>@[%NLS_LANG%]
SP2-0310: unable to open file "[NORWEGIAN_NORWAY.WE8MSWIN1252]"

You will have to extract the NLS_LANG value in current ORACLE_HOME from the registry. All client-side tools (sqlplus, sqlldr, exp, imp, oci, etc...) read this value from registry and determine if any character transcoding should occur.

ORACLE_HOME and registry section:

C:\>dir /s/b oracle.key
C:\Oracle10\BIN\oracle.key

C:\>type C:\Oracle10\BIN\oracle.key
SOFTWARE\ORACLE\KEY_OraClient10204_Home

In times like these I turn to IPython to demonstrate an idea:

A couple of lookups and you are there!

In [36]: OHOMES_INSTALLED = !where oci.dll

In [37]: OHOMES_INSTALLED
Out[37]:
['C:\\Oracle10\\BIN\\oci.dll',
'C:\\oraclexe\\app\\oracle\\product\\11.2.0\\server\\bin\\oci.dll']

In [38]: ORACLE_HOME = os.path.dirname(OHOMES_INSTALLED[0])

In [39]: ORACLE_HOME
Out[39]: 'C:\\Oracle10\\BIN'

In [40]: f = open(os.path.join(ORACLE_HOME, "oracle.key"))

In [41]: SECTION = f.read()

In [42]: SECTION
Out[42]: 'SOFTWARE\\ORACLE\\KEY_OraClient10204_Home\n'

In [43]: from _winreg import *

In [44]: aReg = ConnectRegistry(None,HKEY_LOCAL_MACHINE)

In [46]: aKey = OpenKey(aReg,SECTION.strip())

In [47]: val = QueryValueEx(aKey, "NLS_LANG")

In [48]: print val
(u'NORWEGIAN_NORWAY.WE8MSWIN1252', 1)
Bjarte Brandt
  • 4,191
  • 2
  • 23
  • 25
  • Thanks, this is a workaround. Unfortunately, I can't do that. All I wanted was to perform a quick check. A full search in the HD brings the answer, but it takes too long (and also, it's not *that* reliable, there could be other files with that name). So, if I understood it correctly, there is no easy way. I *have* to browse though that crazy registry keys that change place every new release. Is that right? In that case, I think I'll just drop it. It doesn't worth the effort. – Rafael Piccolo Jul 17 '12 at 19:47
8

According to Jocke's answer (thanks Jocke), I tested the following query:

SELECT DISTINCT client_charset FROM v$session_connect_info
WHERE sid = sys_context('USERENV','SID');

It perfectly does the job, but I'm unsure if any user will have the necessary rights.

Ivan Aracki
  • 4,861
  • 11
  • 59
  • 73
Jérôme
  • 96
  • 1
  • 1
3

I am not sure if this works every time but for me in sql*plus:

variable n varchar2(200)

execute sys.dbms_system.get_env('NLS_LANG', :n )

print n

AMERICAN_AMERICA.WE8ISO8859P1

Just build a function-wrapper, grant execute to the users who needs it, and there you go.

piet.t
  • 11,718
  • 21
  • 43
  • 52
Jocke
  • 41
  • 3
  • After some testing. GET_ENV doesn't get the parameters from the client but from the server process. So it doesn't work as I hoped for. Sorry :-( – Jocke Jan 19 '18 at 15:44
  • After some digging I found that the database actually stores the client character set for every connected session: SELECT sid, client_charset FROM v$session_connect_info; – Jocke Jan 23 '18 at 09:24