When replicating to Oracle target, in some cases you may get ORA-12899 error.
This error may happen in a situation where your Oracle target has NLS_LENGTH_SEMANTICS set to ‘byte’ and the column being replicated from the source endpoint to the Oracle target endpoint includes at Unicode character/s that is presented by more than one byte. In this case, for example, when you replicate a varchar (10), it will be created as varchar (10 byte). Therefore, if the replicated data in the source endpoint includes a Unicode character that in UTF8 is presented by more than one byte, it could be that although the source is only 10 characters, on target endpoint (where replicate works in utf8), it would require more than 10 bytes for presenting its value. This will result with ORA-12899 error.
In general, when working with Oracle, the NLS_LENGTH_SEMANTICS setting determines how the CHAR and VARCHAR2 columns will be created. i.e., it enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics.
Environment
- Replicate with Oracle target endpoint
Resolution
- Stop the task
- Open the Oracle target endpoint, go to under Advanced tab-->Internal Parameters.
In the search box type charLengthSemantics and set it to CHAR. This will cause Replicate to create CHAR and VARCHAR2 columns using char semantics. For example: COL1 VARCHAR2(10 CHAR) instead of COL1 VARCHAR2(10 BYTES), and this will make sure that column length will be long enough to hold its value (including Unicode characters) and thus eliminate the ORA-12899 error.
Note:
- This internal property in Replicate charLengthSemantics determines whether to use the oracle default of NLS_LENGTH_SEMANTICS or override it.
- The NLS_LENGTH_SEMANTICS/charLengthSemantics setting affects any CHAR and VARCHAR2 columns in any table that Replicate creates in the target endpoint
