Resolution:
Description:
===========
How to import Oracle backingstore database backup using NLS_LANG ISO8859SP1 to a new database with UTF-8 NLS_LANG settings.

Environment:
===========
BusinessEvents 3.0/4.0/5.0
All Operating Systems


Resolution:
========
BusinessEvents uses the default settings for varchar2 database attributes which means data stored as VARCHAR2(255 BYTE). You could run into some import issues when NLS_LANG has been changed for the new Oracle database (from: ISO8859P1 to Oracle AL32UTF8). The import could fail for some records (ORA-12899 value too large for column). The new NLS_LANG settings changes the way Oracle enforces the character number (ISO8859P1 1 byte = 1 char, UTF-8 4 bytes=1 char).

The simplest approach to solve the issue is to set NLS_LENGTH_SEMANTICS from 'BYTE' to 'CHAR' as the default character set. Use 'char' as NLS_LENGTH_SEMANTICS setting will make all varchar2 definition 'char' based and this is the recommended approach.

If you have existing data to be migrated to the new db, you may run into the 4000 byte limit if the converted string from ISO8859P1 to AL32UTF8 exceeds the limit. There is a tool called CSSCAN (see http://www.oracle-base.com/articles/10g/CharacterSetMigration.php) which can be used to identify potential truncation problem.  

NOTE: Another option is to change the Oracle columns settings for each string attribute without update NLS_LENGTH_SEMANTICS.

Example:
ALTER TABLE &lttable>
MODIFY(&ltcolumn> VARCHAR2(255 CHAR));

See:
https://forums.oracle.com/forums/thread.jspa?threadID=267176
https://forums.oracle.com/forums/thread.jspa?messageID=1610926&

How to import Oracle backingstore database backup using NLS_LANG ISO8859SP1 to a new database with UTF-8 NLS_LANG settings.