ERROR: "ORA-12899 PowerCenter

ERROR: "ORA-12899: value too large for column" when running a PowerCenter session that writes data to a Unicode (UTF-8) Oracle target database

Problem Description

When running a PowerCenter session that writes data to a Unicode (UTF-8) Oracle target database the following error occurs: ERROR : WRT_8229 : Database errors occurred:  ORA-12899: value too large for column "Schema name"."Table name"."Column name" (actual: 21, maximum: 20)

Cause

This error can occur when the NLS_LANG is not set correctly. Example: When writing Unicode, multibyte, UTF-8 data and the NLS_LANG is set as AMERICAN_UNITED KINGDOM.WE8ISO8859P15 and the the NLS parameters in the database is as follows, then the session will fail with this error: NLS_LANGUAGE  AMERICAN NLS_TERRITORY AMERICA NLS_CHARACTERSET AL32UTF8

Solution

To resolve this error, set the NLS_LANG to the NLS parameters in the database as follows: Execute the following query in the database to confirm the NLS settings: select * from v$nls_parameters. On the machine hosting the Integration Service set the NLS_LANG environment variable. In the above example, the NLS_LANG should be set as follows: NLS_LANG=AMERICAN_AMERICA.AL32UTF8 Restart the Informatica Services.