Character Data Types and Key Fields

When you use data of type CHAR in a key field, the functionality of the Oracle connector in two scenarios must be addressed: trimming spaces and creating an entry.

The CHAR data type in Oracle requires a fixed number of spaces for it to be identified by the external system. Without the correct number of spaces, external records will not be found. In an IBM® Lotus Enterprise Integrator® (LEI) or Domino® Enterprise Connection Services (DECS) Virtual Fields activity, space trimming and padding can be problematic. Certain events (such as updating and deleting) may not be available after a CREATE event has been executed. When Oracle creates a field it adds space padding to give the field a fixed length. However, Notes® does not add additional space padding to the field during the CREATE event. This prevents Notes® from locating the record on the Oracle side.

As a result, if fixed length text fields aren't necessary, you should use VARCHAR2 data types instead of CHAR data types. If you must use text fields of fixed length, use a formula filter to pad the specific field to the correct length. A code example for padding a field to the correct length is given as follows. This code can be used on the Notes® form at the specific field under "Input Translation," in an DECS Virtual Fields Activity Document under "Options: When intercepting the creation of a document...", or in an LEI Advanced RealTime Virtual Fields Activity Document under "Intercept Document Creation."

FIELD fieldName := fieldName;
@SetField ("fieldName"; fieldName + @Repeat (" "; Oracle_Column_Length --@Length(fieldName)))

For example, if the Notes® field name is "CharField" and the length of the Oracle CHAR column is 32, the formula would read as follows:

FIELD CharField := CharField;
@SetField ("CharField"; CharField + @Repeat (" "; 32 --@Length(CharField)))