Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know how the database characterset (NLS_CHARACTERSET in select * from v$nls_parameters;) and the client character set (the client environment setting NLS_LANG) interact.

What I can't find out however, is how or if I can determine, for an established session, what Oracle thinks the current client characterset is.

Is this possible at all?

Note: SELECT * FROM NLS_SESSION_PARAMETERS; does not include the character set (on 10g2).

To make absolutely clear what I'd like to accomplish:

  1. NLS_LANG is set in client environment to an arbitrary value (for example GERMAN_GERMANY.WE8MSWIN1252)
  2. Database application[*] starts and establishes a connection/session to the Oracle database.
  3. Database application[*] wants to "ask" Oracle (not its OS environment) what the client character set is Oracle will assume.

[*]: If the db application is sqlplus, the example would look as follows:

...
sqlplus /nolog
connect user/pass@example
*magic command*;
   CLIENT CHARACTERSET = ...

Jack's note in his answer raises two important points:

  • With Oracle, who does the characterset translation. Is it the client-library code or is it done on the server side?
  • As it appears it is the client, the client would need expose this setting -- what the client lib/tool assumes this setting is. Is there any of the Oracle client libs/tools (sqlplus, OCI/OCCI, Pro*C, ...) that can be queried for what it thinks this setting is?
share|improve this question

2 Answers 2

up vote 5 down vote accepted

I am a little doubtful that this is exactly what you are looking for, but

host echo %nls_lang%;

ENGLISH_UNITED KINGDOM.WE8ISO8859P1

shows the client nls_lang environment variable on the client.

I don't think there will be a SQL query you can run to give the 'current' setting because AFAIK the server is not aware of what translation is done client-side, so any command to show the current setting will have to be native to the client - I used SQL Developer for the above command, but I assume it will work the same in SQL*Plus

--edit

from AskTom:

only the client knows their character set as well -- it is not available "in the database"

and

the character set describes what is stored in database.

the client makes their desired translated to character know [sic] to the database via the NLS_LANG settting.

If you were on 11.1+, you might have some joy with v$session_connect_info, because:

This information is pushed by OCI to the server ats login time.

But I discovered it would still depend on how you are connecting, eg from the JDBC Thin Driver you aren't using OCI and so the information isn't pushed

share|improve this answer
    
Well, no, it is exactly not what I was looking for :-) -- But you raise an (two) important point(s), and if it's true, I guess it would be an acceptable answer. I'll add the two points to the question. –  Martin Nov 14 '11 at 7:54
    
Bah! (not to blame you) But Oracle really has it: "only the client knows their character set as well -- it is not available "in the database"" and "the client makes ... known to the database via the NLS_LANG" are really quite stating the opposite, no? :-) –  Martin Nov 14 '11 at 15:55
    
Yes you are right though Tom<>Oracle. I think he is stumbling over his words - that whole thread is well worth skimming... –  Jack Douglas Nov 14 '11 at 15:58
    
Accepted answer for the 1st link - because this question on Ask Tom (from 2002) actually asks the same thing "I would like to know if it is possible to check the client side NLS_LANG settings in any of v$ or nls_ views once connected to the database." –  Martin Nov 14 '11 at 16:02

You can see the following:

  • NLS_CALENDAR
  • NLS_CURRENCY
  • NLS_DATE_FORMAT
  • NLS_DATE_LANGUAGE
  • NLS_SORT
  • NLS_TERRITORY

E.g:

SQL> select sys_context('USERENV', 'NLS_TERRITORY') from dual;

SYS_CONTEXT('USERENV','NLS_TERRITORY')
--------------------------------------------------------------------------------
UNITED KINGDOM

1 row selected.
share|improve this answer
    
I guess mentioning sys_context here is useful. Otherwise this fails to address the problem of which characterset. –  Martin Nov 18 '11 at 18:34
    
My point is that these are the only things that the server knows (or cares) about. –  Gaius Nov 18 '11 at 19:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.