Monday, November 19, 2012

How to See a Database's Character Set

If you have access to objects owned by SYS, you can see a database’s character set as follows:
 
SQL> l
  1  SELECT VALUE$
  2  FROM SYS.PROPS$
  3* WHERE NAME = 'NLS_CHARACTERSET'
SQL> /
 
VALUE$
--------------------
WE8MSWIN1252
 
SQL>

As pointed out in the first comment below, your user may not have access to objects owned by SYS. In that case, you have to do it like this:

SQL> conn / as sysdba
Connected.
SQL> create user andrew
  2  identified by reid
  3  /

User created.

SQL> grant create session to andrew
  2  /

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> select value$
  2  from sys.props$
  3  where name = 'NLS_CHARACTERSET'
  4  /
from sys.props$
         *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> select nls_charset_name(nls_charset_id('CHAR_CS'))
  2  from dual
  3  /

NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS'))
---------------------------------------------
WE8MSWIN1252

SQL> select sys_context('userenv','LANGUAGE')
  2  from dual
  3  /

SYS_CONTEXT('USERENV','LANGUAGE')
-----------------------------------
ENGLISH_AMERICA.WE8MSWIN1252

SQL>

In Spanish / en español 

2 comments:

Anonymous said...

SQL>SELECT VALUE$
2 FROM SYS.PROPS$
3 WHERE NAME = 'NLS_CHARACTERSET'
4 /
FROM SYS.PROPS$
*
ERROR at line 2:
ORA-00942: table or view does not exist


SQL>select nls_charset_name( nls_charset_id( 'CHAR_CS' ) )
2 , sys_context( 'userenv', 'LANGUAGE' )
3 from dual
4 /

NLS_CHARSET_NAME(NLS_CHARSET_ID('CHA
------------------------------------
SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
WE8MSWIN1252
AMERICAN_AMERICA.WE8MSWIN1252

Andrew Reid said...

Good point. My example only works if you can see objects owned by SYS. I will update the post accordingly.