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.