Replicating CLOB and NCLOB Datatypes

Oracle NCLOB (National Character Large Object) is a datatype that stores large character data using a multibyte national character set. Similarly, the CLOB datatype may also store character data using a multibyte national character set, when the Oracle database is defined with a double-byte or variable-width character set.

By default, the byte order of the multibyte characters stored in the NCLOB datatype (and CLOB when the database is defined with a double-byte or variable-width character set) is converted during replication to big-endian byte order. This allows the data to be transmitted over networks using big-endian order, which is the common network byte order.

The datatype in a replication definition for an NCLOB or CLOB should be unitext. This prevents Replication Server from attempting character set conversion on the data. If the Replication Server version does not support unitext, use the image datatype.

If the target database that is to receive this NCLOB or CLOB data is installed on a little-endian platform, the database may not automatically convert the replicated data from the sent big-endian order to the little-endian order. To support replicating NCLOB or CLOB data to a database server that does not provide the necessary conversion from big-endian (network order) to little-endian, force the byte order to be sent by the Replication Agent using the lr_ntext_byte_order parameter to set a value of big (for big-endian) or little (for little-endian).

The lr_ntext_byte_order parameter is available for Microsoft SQL Server and Oracle, andis important for replication between two databases that reside on different platforms. For example, for replication between Oracle and Microsoft SQL Server, the primary database stores the data in big-endian byte order, but the replicate database stores data in little-endian byte order because Microsoft SQL Server only runs on Windows. Therefore, set the lr_ntext_byte_order parameter to little to force the Replication Agent to convert the data to little-endian (the format expected by SQL Server). However, if the replicate database is not a Microsoft SQL Server, determine its byte order and set the lr_ntext_byte_order parameter accordingly.

Note: The default behavior of Replication Agent for Oracle is to force any Unicode data to big-endian order as defined by the ltl_big_endian_unitext configuration parameter. To allow the lr_ntext_byte_order configuration parameter to successfully override the Oracle byte order, you must also set the ltl_big_endian_unitext configuration parameter to false whenever the lr_ntext_byte_order parameter is used.

The ltl_big_endian_unitext parameter specifies whether unitext data should be converted from little-endian to big-endian before sending LTL to the Replication Server. Valid values are true and false. When setting this parameter, you must know how lr_ntext_byte_order is set. If lr_ntext_byte_order is set to send the correct byte order for the replicate database, the ltl_big_endian_unitext parameter must be set to false so that the byte order is not changed. ltl_big_endian_unitext is true, by default. The ltl_big_endian_unitext and lr_ntext_byte_order configuration parameters have differences:

When ltl_big_endian_unitext is true, Replication Agent for Oracle sends all Unicode data in big-endian order.
When ltl_big_endian_unitext is false, Replication Agent for Oracle allows Unicode data to be sent in a byte order that is used when the data is stored in the transaction log file.

lr_ntext_byte_order forces the result of Unicode data that is read from the transaction log to be in the correct byte order, regardless of how it normally exists in the transaction log file.