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 HCL Enterprise Integrator (HEI) 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 HEI 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)))