Oracle Character Set

Improper selection of the client or server language, territory, or character set can result in a garbled text display on the client and corrupted or truncated data being stored in the database.

Client settings

The NLS_LANG parameter stores the language, territory, and character set of the client application.  The NLS_LANG parameter is set in the Windows Registry under HKEY_LOCAL_MACHINESOFTWAREORACLEHOMEx for Oracle version 8, 8i, and 9i or under HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_  for version 10g. The NLS_LANG parameter can also be set using an environment variable for both the Windows and Linux operating systems.

Example: NLS_LANG=french_canada.WE8DEC

Server settings

NLS_DATABASE_PARAMETERS stores the language, territory, and character set used on the server:

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

The correct values must be set on both the client and server to enable proper conversion from the client operating system character encoding to the database character set. When clients from different locales connect to the database, automatic conversion of character sets takes place in the background to display the data properly (based on the NLS_LANG setting on the client). When a client in a different locale sends data back to Oracle, the same conversion takes place before data is actually stored. Ideally, the server's character set should be a superset of all client locales.

Setting NLS_LANG incorrectly can cause your DataFlex program to get stuck in an infinite Loop/End procedure. In the example below, ORAFlex never receives the End of File, which would set NOT FOUND to true.

Example:

Clear Customer

Repeat

Find Gt Customer By Index.1 // Uses CUSTOMER.NAME that needs NLS_LANG

If (Found) Begin

// Some DF Code Here

End

Until (Not(Found))

Oracle Instant Client and NLS_LANG

When using Oracle Instant Client in a multi-lingual environment, the NLS_LANG parameter needs to be set manually. When installing other flavors of Oracle client, the Universal Installer does this for you automatically.

Note: Make sure the NLS_LANG parameter is set if you are using Oracle Instant Client so OraFlex properly handles all special characters.

ORA-01401: inserted value too large for column

Oracle Error ORA-01401 can occur if your client uses a single-byte character set and your database uses variable byte encoding.  When conversion takes place between the client character set and database character set, the resulting value may be larger than the size of the column, and Oracle issues error 01401. This means that you have to make sure your database columns are big enough to store the additional bytes.

By default, the Oracle VARCHAR2 datatype defines the column size in BYTES, not in CHARACTERS. So "create table <name> (<colname> VARCHAR2 (200));" means that that column can store 200 bytes.  However, a 200 byte string entered on a client can convert to 220 bytes of storage on the server if the string contains special characters. You can resolve this problem by changing the client character set to match the database character set (in this case the overflow will be caught on the client) or by increasing the number of bytes the column can store. Starting with Oracle version 9i,  you can also specify the number of characters (rather than bytes) you want to store using VARCHAR2(n CHAR) or NVARCHAR2 (n).

Note: When you convert a DataFlex database to an Oracle backend, the default mapping for a DataFlex text field is VARCHAR2(n). If AL16UTF16 or UTF8 is specified at database creation time, then you should change this mapping to nVARCHAR2(n).

For additional description of Oracle datatypes see http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm