Thursday, September 24, 2009

Oracle 10.2 Normal and National Character Sets

Our DBA moved data from one Oracle 10.2 database to another and all of the smart-quotes, various dashes, bullets, and accented characters were converted into "???".

Turns out that the original database was using the normal character set (NLS_CHARACTERSET) "US7ASCII" and the new one was using the normal character set "WE8ISO8859P1". They were both using the same national character set (NLS_NCHAR_CHARACTERSET) "AL16UTF16", but the fields in question where not of type NCHAR, NVARCHAR2 or NCLOB which is what the NLS_NCHAR_CHARACTERSET affects.

To determine your normal character set in Oracle v10.2, you can use:

select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
To determine your national character set in Oracle v10.2 (used by NCHAR, NVARCHAR2 or NCLOB), you can use:
select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

Richard also shared another way to get related info via:

select userenv('LANGUAGE') from dual;
which returned "AMERICAN_AMERICA.US7ASCII" and "AMERICAN_AMERICA.WE8ISO8859P1" respectively for the databases mentioned, so apparently that reports on the normal character set only.

There is a lot of great info about this here in Oracle in World. If you need help choosing your character set, check this out. Also one of the answers comes from here.

No comments: