Difference between the CHAR and VARCHAR2 data type

The LN Oracle driver strongly relies on the ANSI-compliant string comparison semantics, which are blank-padded semantics. Unlike other database vendors, Oracle has chosen to implicitly link the comparison behavior to the data type.

The Oracle CHAR/NCHAR data types comply with the ANSI-comparison semantics, while the Oracle VARCHAR2/NVARCHAR2 data types do not comply.

From porting set 8.6a onwards, you can use the VARCHAR2/NVARCHAR2 data type for LN. The resource ora_use_varchar controls this behavior. For the driver to operate correctly in varchar mode, several criteria must be met.

To preserve the blank-padded semantics, the LN Oracle driver must ensure that all data in Oracle is trimmed during comparison and padded during concatenation. When needed, the driver generates additional RTRIM() or CAST() operators in the Oracle query text.

Another issue is that the Oracle VARCHAR2 data type handles an empty string as a NULL value, which can lead to incorrect results. NULL values supply a different outcome in boolean expressions than empty strings.

To avoid incorrect values, the driver generates additional COALESCE() or NVL() calls in the Oracle query text.

Note: 

For correct query evaluation when using the VARCHAR2/NVARCHAR2 data type, these constraints on the data in the database must be met:

  • An empty string must be represented by a string of exactly 1 space.
  • All other string data must never have trailing spaces.

It is important that all external integrations that change LN data comply with these constraints.

Without these data constraints and driver modifications done in porting set 8.6 or later, you cannot carry out operations such as comparison and concatenation in a predefined way, with unpredictable results.

Mixing CHAR and VARCHAR2 data types leads to a violation of the constraints mentioned earlier and to unpredictable results.