How to find the Character set of Oracle with examples?

In my previous article I have explained about the oracle date functions with multiple real time examples. In this article I would like to give you multiple examples about how to find the character set of Oracle database with real examples. We require to find out the character set of oracle for multiple business reasons to do the programming. Sometimes as a dba we require to change the character set of oracle as well. The character set information is stored in oracle system table named ‘SYS.PROPS$.’ table.

How to Find Character set of oracle using SYS.PROPS$ table?

We can use separate table named ‘SYS.PROPS$‘ to find out the character set of oracle. The table contains information about the information of stored character set. You can get character set by not only using ‘SYS.PROPS$‘ table but also with using some database_properties or nls_database_parameters exist in the databases.

Example : The “Show Parameter” Command does not tell the database characterset name but if you use the NLS_Characterset it will give you actual characterset name of database.

Why Character set of oracle is important with example?

The question in everyone’s mind why character set for oracle is important. There are set of symbols which you can not able to show using some specific character set. At that time you require to change the character set else the customer experience will be bad for the users.

Example : If you are using “US-ASCII'” character set and tries to display the € symbol it will not work.

connection = cx_Oracle.connect(userName, password, "dbhost.example.com/amitdb",
        encoding="US-ASCII")
cursor = connection.cursor()
for row in cursor.execute("select nvarchar2_column from nchar_test"):
    print(row)

Because the ‘€’ symbol is not supported by the US-ASCII character set, all ‘€’ characters are replaced by ‘¿’ in the cx_Oracle

output:

('¿',)

Here you can see the discrepancy in showing the data. so we require to change the format to UTF-8 

connection = cx_Oracle.connect(userName, password, "dbhost.example.com/orclpdb1")

Then the output displays the Euro symbol as desired:

('€',)

How to find out the database and client character set with examples :

Query 1 : How to find out the database character set?

Answer : You can use the nls_database_parameters table to find out the value of current database character set.

Query :

SELECT Parameter,value AS database_characterset
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';

Output:

PARAMETER                      database_characterset
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252

Query 2 : If you want to find out the character set for specific database you can use following query:

Answer : User needs to use sys.props$ table with filter of NLS_CHARACTERSET

Query :

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

Output :

VALUE$
---------------------------------------------------------------------------

WE8MSWIN1252

Query 3 : To find the specific character set for the database you can use below query :

Query :

SELECT value$ as Character_set FROM sys.props$  WHERE name = 'NLS_CHARACTERSET' ;

Output :

Character_set 
---------------------------------------------------------------------------

US-ASCII

You can also use following query to find out NLS_national characterset.

Query 4 :

SELECT value AS National_Character_set FROM nls_database_parameters WHERE parameter = ‘NLS_NCHAR_CHARACTERSET’;

To find the current “client” character set used by cx_Oracle, execute the query:

SELECT DISTINCT client_charset AS client_charset FROM v$session_connect_info WHERE sid = SYS_CONTEXT(‘USERENV’, ‘SID’);

If these character sets do not match, characters transferred over Oracle Net will be mapped from one character set to another. This may impact performance and may result in invalid data.

If you want to find out all parameters from the database which you are using use following query :

Query 5 :

SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.1.0.6.0

20 rows selected.

The above queries will give you the exact information about the How to find out the character set of oracle. I hope this helps you. If you like this article or if you have any comments kindly share your comments in comments section.