CSSCAN does not detect data truncation for CHAR datatype - ORA-12899 when importing
Last updated on DECEMBER 05, 2016
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
This issue is not a Bug
***Checked for relevance on 02-Nov-2016***
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-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.
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms