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.
• LANGUAGE specifies the language used to display messages, day names, and month names
• TERRITORY specifies monetary and numeric formats, and conventions for calculating week and day numbers
• CHARACTERSET controls how characters are mapped for storage. Single-byte character sets (like, WE8ISO8559P1 or WE8MSWIN1252) store each character in one byte. These character sets can be used if your language only contains ASCII characters like A-Z,a-Z,0-1 and ./?,*# etc. National character sets (like AL16UTF16, AL32UTF8, or UTF8) use multi-or variable-byte encoding, so each character can occupy 1, 2, 3 or more bytes. For example, AL32UTF8 and UTF8 encode ASCII characters in one byte, special characters (like ç, ñ , é) in two bytes, some multilingual characters in three bytes, and certain mathematical symbols in four bytes.
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;
| 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 |
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
Mertech: Mar 29, 2024 8:53:00 AM
Mertech