Check Character set for Oracle database and client

Check Characterset for Oracle database and client

Following is the command used to check the Database and client connected character set. If their is different then their is chance of data corruption during transformation of data.

Check the Database character set

col parameter for a30
col value for a20
select parameter, value from v$nls_parameters where parameter like 'NLS_%CHARA%SET';

PARAMETER                      VALUE
------------------------------ --------------------
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         AL16UTF16

OR
-- Check the database characterset with different value.
SELECT value AS db_charset FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
DB_CHARSET
----------------------------
AL32UTF8
-- Check the National Character Set of Oracle database. National Character set belong to NCHAR,NCLOB etc datatypes.
SELECT value AS db_ncharset FROM nls_database_parameters WHERE parameter = 'NLS_NCHAR_CHARACTERSET';
DB_NCHARSET
------------------------
AL16UTF16

Check the Client Character set which currently connected

SELECT DISTINCT client_charset AS client_charset
FROM v$session_connect_info
WHERE sid = SYS_CONTEXT('USERENV', 'SID');
CLIENT_CHARSET
------------------------------
WE8MSWIN1252

Example: Client Character set is changed with NLS_LANG environment variable.
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
NLS_LANG = FRENCH_CANADA.WE8ISO8859P1
NLS_LANG = JAPANESE_JAPAN.JA16EUC

Example of using the NLS_LANG parameter to set different character set

--Set the environment variable at command prompt
SET NLS_LANG=JAPANESE_JAPAN
--Connect with SQLPLUs on same command prompt
SQLPLUS sys/password as sysdba
--Connected with DB and check the client charset
SQL> SELECT DISTINCT client_charset AS client_charset
2 FROM v$session_connect_info
3 WHERE sid = SYS_CONTEXT('USERENV', 'SID');
CLIENT_CHARSET
-------------------------
JA16EUC

Check the list of encoding are supported by Oracle

select distinct utl_i18n.map_charset(value)
from v$nls_valid_values
where parameter = 'CHARACTERSET'
and utl_i18n.map_charset(value) is not null
order by 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.