CHARACTER data types

Informix® supports the following character data types:

In Informix, both CHAR/VARCHAR and NCHAR/NVARCHAR data types can be used to store single-byte or multibyte encoded character strings. The only difference between CHAR/VARCHAR and NCHAR/NVARCHAR is for sorting: N[VAR]CHAR types use the collation order, while [VAR]CHAR types use the byte order. The character set used to store strings in CHAR/VARCHAR/NCHAR/NVARCHAR columns is defined by the DB_LOCALE environment variable. The character set used by applications is defined by the CLIENT_LOCALE environment variable. Informix uses Byte Length Semantics; the size N that you specify in [VAR]CHAR(N) is expressed in bytes, not characters as in some other databases.

ORACLE provides the following types to store character strings:

In ORACLE CHAR(N)/VARCHAR2(N) types, the size N can be specified in character or byte units, according to length semantics settings.

When comparing CHAR and VARCHAR2 values in ORACLE, the trailing blanks are significant; this is not the case when using Informix VARCHARs. However, before comparing string values, ORACLE blank-pads CHAR(N) data to the maximum length of both operands. As result, it looks like trailing blanks are no significant in CHAR(N) comparison. For example, a column defined as CHAR(5) with the value 'abc ' (with 2 trailing blanks) will not be equal to 'abc', but when comparing (col = 'abc'), ORACLE will add 2 blanks to the right operand and values will match. Blank padding does not occur for VARCHAR2() data, as result, the expression (col = 'abc') will be false, if col VARCHAR2 does not exactly contain the value 'abc'. For more details, see blank-padded and non-padded comparison semantics in ORACLE documentation.

ORACLE treats empty strings like NULL values; Informix doesn't. See issue Empty Character Strings for more details.

With ORACLE, you can define a Database Character Set and a National Character Set: ORACLE uses the Database Character Set to store string data in the CHAR/VARCHAR2 columns, and uses the National Character Set for NCHAR/NVARCHAR2 columns.

Solution

Informix CHAR(N) types must be mapped to ORACLE CHAR(N) types, and Informix VARCHAR(N) or LVARCHAR(N) columns must be mapped to ORACLE VARCHAR2(N).

Check that your database tables does not use CHAR, VARCHAR or LVARCHAR types with a length exceeding the ORACLE limits of CHAR/VARCHAR2.

When using a multibyte character set (such as UTF-8), configure ORACLE to use character length semantics, define CHAR/VARCHAR2 database columns with a size in character units, and use character length semantics in 4gl programs with FGL_LENGTH_SEMANTICS=CHAR.

When extracting a database schema from an ORACLE database, the schema extractor uses the size of the column in characters, not the octet length. If you have created a CHAR(10 (characters) ) column a in the database, the .sch file will get a size of 10, that will be interpreted according to FGL_LENGTH_SEMANTICS as a number of bytes or characters.

The ORACLE client character set must correspond to the Genero runtime system locale (LANG/LC_ALL). You can define the ORACLE client character set with the NLS_LANG environment variable.

See also the section about Localization.