9

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?
Martin
  • 2,420
  • 4
  • 26
  • 35

2 Answers2

9

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

Community
  • 1
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
0

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.
Gaius
  • 11,238
  • 3
  • 32
  • 64