Handling character type conversions to CLOB

Fields longer than the 4000 byte maximum character length of a VARCHAR2 column in Oracle are migrated as LONG columns by the Oracle DataServer migration tool. You can also set the Maximum char length migration option lower than 4000 bytes and field(s) exceeding that limit will be migrated as a LONG column. However, Oracle allows only one LONG column per table. If more than one migrated column is defined with a size greater than 4000 bytes (or the Maximum char length option value) or, if data widening from Unicode expansion causes data from more than one column to overflow the 4000-byte limit (or the Maximum char length option value), the table definition will become invalid and the table cannot be migrated to Oracle without modification.

Note: N/VARCHAR2 data types cannot exceed 4000 bytes irrespective of what number of characters are defined for the data NVARCHAR2 data type. If you are using an AL16UTF16 NCS data type, set the maximum character size to 2000 bytes. This is because thought the column length is 3000 characters, the single byte characters originating from an ANSI character set will expand to 2 bytes of storage and will occupy more than the maximum 4000 bytes of an N/VARCHAR2 field.

If multiple fields over the Maximum char length causes some tables to be excluded from the migration, these OpenEdge database definitions and the application that uses them will need to be modified first before a migration is possible. These database definitions should be changed from CHARACTER to CLOB data types and their associated references in the application should be reworked as LOB-managed data types. Once the number of fields exceeding the Maximum char length value has been reduced to one and the application has been changed accordingly, a DataServer table migration can proceed.

The following table provides details on character length settings during migrations:

Table 67. Recommended character length settings
Character semantics				Byte semantics
Use Unicode Types toggle box	National character set	Database character set	Max length	Max length
Selected	UTF-8	N/A	1333	4000
Selected	UTF-16	N/A	2000¹	4000
Unselected	N/A	Non-Unicode	4000	4000
Unselected	N/A	UTF-8	1333	4000
Unselected	N/A	AL32UTF8	1000	4000

¹ Consideration should be given for whether data content might include UTF-16 supplementary characters. These will produce four-byte characters which would favor a maximum character length less than 2000.