CODEWITHSUNDEEP
oracle-databasecharacter-encodingnls-lang
Rafael Piccolo
Rafael Piccolo

Reputation: 2338

How do I check the NLS_LANG of the client?

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.

Upvotes: 19

Views: 120694

Answers (3)

Jocke
Jocke

Reputation: 41

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.

Upvotes: 3

Jérôme
Jérôme

Reputation: 106

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.

Upvotes: 9

Bjarte Brandt
Bjarte Brandt

Reputation: 4461

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)

Upvotes: 10

Related Questions