ORA-12899: value too large for column

[OritA]

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

1. Stop the task
2. 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

[gaegig]

Thank you very much, it seems that this parameter solved my problem, in the same job I also have this message: Column 'ADDITIONAL_CONTACTS' was removed from table definition 'DEVADT.ADT2_PATIENT_AUD': the column data type is LOB and the table has no primary key or unique index

No way to transfer lob in tables where no PK is present? I need to create a PK in advance?

Thank you again for your support

[Dana_Baldwin]

Hi @gaegig 

Yes, a PK is required for tables with LOB columns that are being replicated. This is so Qlik Replicate can efficiently look up the LOB value in the event that not all of it is stored in the redo log. Without a PK, a full table scan would occur for every LOB value that needed to be looked up, which would cause horrible performance leading the task to fall behind consistently.

Thanks,

Dana