Oracle Character set

Problem This technote should be used to identify what Oracle Character set is in use, which can be important to know when a text has been replaced with question marks.

Cause

Oracle allows you to specify which character set it will use, both on the Oracle client and on the Oracle server. When the Oracle client flags the Oracle server to store a character it does not understand the Oracle server stores the character as a question mark. This usually happens when the character set on the client is not a subset of the character set in the database.

Resolving the problem

To resolve this problem you must identify what the character set is on your client, on your server, and if these character sets are compatible: The character set on an Oracle client is stored in the following key: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG Note: There may be other HOME Registry branches as well. The Oracle client character set is the last section of the value. For example: If the registry value is: AMERICAN_AMERICA.WE8ISO8859P1 then the character set is WE8ISO8859P1. Other values might include WE8MSWIN1252, UTF8 orAL32UTF8. To determine what character set in on the server submit the following query: Select SYS.PROPS$.VALUE$ From SYS.PROPS$ Where ((SYS.PROPS$.NAME = 'NLS_CHARACTERSET')) ; Note: To perform this query you need to be logged in with SYSDBA privileges such as a system/manager The following chart is a brief list of Oracle character sets and their compatibility. Superset Subset WE8ISO8859P1 US7ASCII ALT24UTFFSS US7ASCII UTF8 US7ASCII The follow is an example of two code pages that are not completely compatible. If ISO-8859-1 was used on the database server and CP1252 was used on the client the top two rows of character in CP1252 could not be stored in the database server. The server would save these characters as question marks. CP1252 Encoding Table ISO-8859-1 Encoding Table