I am getting the following error while executing a job in DI.
ORA-12899: value too large for column
(actual: 241, maximum: 240)
I have checked in source side there also maximum length is 240 only.
But source side datatype for that column is Varchar2(240) and target side Varchar2(240 char).
Is it the problem with these datatypes? What is the solution to overcome this problem in DI?
Thanks in advance…
iammanohar (BOB member since 2008-07-22)
Are you sure the target is 240 chars??? I cannot come up with an example where 240bytes of the source become 241 chars. 241 bytes is no problem, just one character needs to be a double byte character for the target codepage…
Werner Daehn 
(BOB member since 2004-12-17)
Yes. I am sure. Target column is defined like that. [Description varchar2(240 char)].
Even I implemented like
ifthenelse(length(description)>240, null, description)
I am getting the same error.
I am using oracle apps as source and target also oracle only.
iammanohar (BOB member since 2008-07-22)
I am getting the source as 240 bytes.
Can you please mention, Is it possible to convert bytes to char of a string in DI?
iammanohar (BOB member since 2008-07-22)
In DI all string operations are char based, not byte based. A substring(‘ä’, 1,1) will return ‘ä’, not the first byte of a double byte char or similar.
But again, it does not make sense to me. What one-byte becomes two characters??? The reverse is fine, one-char is two bytes in UTF-8. But not the reverse.
Please debug that a little bit more, write it into a template table and compar…I have an idea!
What if the datastore is set to the wrong codepage? For example the database session is running in a single-byte codepage but the datastore is set to UTF-8. Then DI would convert the ‘ä’ char into two bytes, the database session receives a string with two bytes and interprets this as two characters. And suddenly the string of length one is two chars wide.
Keep in mind, the codepage your datastore has to have, is the codepage of the session (NLS_LANG environment variable or regedit hkey_local_machine -> software -> oracle -> …)!
Werner Daehn (BOB member since 2004-12-17)
Some time ago I had a similar problem with output to a flat-file. Selected the wrong codepage for the flat-file and every line (length = 160) ended with an extra space behind the line (length = 161). Setting the codepage to the correct value and the space disappeared!
Griffon (BOB member since 2004-10-04)
Can someone please elaborate the comments by deahn and also the exact location where we can get the codepage ?
Also what is the default value for code page ?
"What if the datastore is set to the wrong codepage? For example the database session is running in a single-byte codepage but the datastore is set to UTF-8. Then DI would convert the ‘ä’ char into two bytes, the database session receives a string with two bytes and interprets this as two characters. And suddenly the string of length one is two chars wide.
Keep in mind, the codepage your datastore has to have, is the codepage of the session (NLS_LANG environment variable or regedit hkey_local_machine -> software -> oracle -> …)!"
128499 
(BOB member since 2010-10-25)
https://wiki.sdn.sap.com/wiki/display/BOBJ/Multiple+Codepages
Are you using template tables maybe? There is a flag to use varchar or nvarchar datatypes in the create table statement.
Werner Daehn (BOB member since 2004-12-17)
I ran into this a couple years ago. The column was expected to be 40 characters, but because there was one multi-byte character in the string the entire string actually required 41 bytes. The DDL was originallly specified at VARCHAR2(40 bytes).
If this is on Oracle and you declared your column as VARCHAR2(240 bytes) then change it to VARCHAR2(240 CHAR) and that may take care of the problem.
eganjp 
(BOB member since 2007-09-12)
Hi.
I have a similar problem.
I have DI 11.5.1 and oracle 10g.
I have a dataflow with an input table with a column varchar2(10 char) and an output table with a column varchar2(10 byte).
The template table is drop and re-create. DI perform the process but I have an error, obviously! Because DI perfom drop table and afterwards a create table but without specify column varchar2(10 char)! The by default oracle is varchar2(10 byte)!
I would be happy if I would can set oracle create table by default, for varchar2(10 CHAR). Is it possible?
powerinmind (BOB member since 2005-06-24)
As said, you can use nvarchar - but not with your release. And you can change the Oracle default:
alter system set nls_length_semantics=char;
alter system set nls_length_semantics=byte;
Werner Daehn (BOB member since 2004-12-17)
Thank you very much. 
Now I would try to speak with the system administrator! 
powerinmind (BOB member since 2005-06-24)
This can also be resolved in how you declare your DDL: varchar2(40 bytes) vs varchar2(40 char).
So you may not need to involve the DBA. There was an early version of 12.x (probably 12.0.0.0.0) that wasn’t in agreement with Oracle on the actual number of bytes needed for a column. If the column was declared varchar2(40 char) then in the metadata DS tracked it as varchar(120) or something like that. I posted the details on this in 2008 I think and I’m pretty sure it was fixed in a subsequent release.
eganjp (BOB member since 2007-09-12)
Will the DBA’s agree to modify the Oracle Server parameters??? 
ganeshxp 
(BOB member since 2008-07-17)
Given the impact that configuration change will have on the entire system I think it is a longshot.
If the DBA’s are reluctant to make the change then point out that the change only applies to future DDL.
When the DDL is VARCHAR2(20) Oracle makes a decision based on the nls_length_semantics setting to determine if it means 20 char or 20 bytes. The workaround is of course to always declare it as 20 char or 20 bytes.
eganjp (BOB member since 2007-09-12)
While you’re at it, I would recommend upgrading to at least 11.5.3 as 11.5.1 and 11.5.2 both have a nasty Table Comparison bug that can lead to duplicate, active records in SCD2 dimensions.
ErikR 
(BOB member since 2007-01-10)
Thanks at all!
I unfortunately work in italy. I have to ask to DBA for change oracle parameter because him has cause the problem. He want that all the tables with field varchar2 must be varchar2( char) but NOT want change the set nls_length_semantics parameter to char! We have many Data integrator process with template tables that drop tables and re-create it with fields always with varchar2(byte) and unfortunately the customer not want change Data integrator release.
Where do you work this is normal?
I will can news from DBA, tomorrow…
powerinmind (BOB member since 2005-06-24)
As far as I know VARCHAR and VARCHAR2 in Oracle is the same thing. There is a lot of history over the VARCHAR2 data type and I can’t refer you to the “story” about it. Oracle decided they wanted to do something different in the future so they created VARCHAR2. Most developers do use VARCHAR2.
I discourage the use of template tables in a production environment.
eganjp (BOB member since 2007-09-12)
Thank you. I think that use template table is not good, too. I am a newcomer in this project… and the colleagues have worked with template table 
Tomorrow I have a meeting.
I thinking once agan… Is possible set oracle parameter from Data Integrator for an oracle session??? Is perhaps possible for any Data Integrator process set the parameter NLS_LENGTH_SEMANTICS ?
powerinmind (BOB member since 2005-06-24)
In DS 12.2 that is possible using the Datastore.
eganjp (BOB member since 2007-09-12)