CSSCAN does not detect data truncation for CHAR datatype - ORA-12899 when importing (Doc ID 779526.1)

Last updated on DECEMBER 05, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.
This issue is not a Bug
***Checked for relevance on 02-Nov-2016***

Goal


When using CSSCAN before going from single to a multibyte characterset (eg: going from WE8MSWIN1252 to AL32UTF8) CSSCAN may not always report "Truncation" for CHAR columns.
So during exp/imp you may see ORA-12899/ORA-01401 errors, even if the csscan report does not list any "Truncation".

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCHEMA_NAME"."TABLE_NAME"."COLUMN_NAME" (actual: <xx>, maximum: <yy>)
or - for versions lower then 10g - :
ORA-01401: inserted value too large for column


This is expected behaviour as explained in <Bug 3699154>CSSCAN IS NOT REPORTING EXCEPTION FOR "EXCEED COLUMN SIZE" , which is closed as Not a Bug.

Note that this is specific to the CHAR datatype and not very common.This is certainly NOT the main reason to see ORA-12899 / ORA-01401. The main reason is just not using csscan upfront.
Please see <Note 1297961.1> ORA-01401 / ORA-12899 While Importing Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database. 


The CSSCAN behavior is correct. Csscan only reports "Truncation" if the actual used data is longer then the defined column size. It will not take into account the padded spaces of the CHAR datatype.
If the actual datasize (excluding padded spaces) of the string , once converted to the multibyte characterset, is longer then the column definition then csscan will report "Truncation".
The issue here is that exp should trim the tailing blank for char type when exporting. See the enhancement <Bug 3275779>. This is however never implemented.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms