tOracleBulkExec - "Field in data file exceeds maximum length"

One Star

tOracleBulkExec - "Field in data file exceeds maximum length"

I am trying to work with tOracleBulkExec (Java) - which uses SQL*LOADER
One of the fields is defined as VARCHAR2(4000) but I have max length errors when trying to load 1996 characters length values, if I use smaller fields lengths it is working well
I added an environment variable NLS_LANG=AMERICAN_AMERICA.AL32UTF8 - just to be on the safe side
Any Idea?

I am using
Talend Version: 2.2.3, Build id: r7292-20071129-1439
SQL*Loader: Release 9.2.0.1.0
Tags (1)
One Star

Re: tOracleBulkExec - "Field in data file exceeds maximum length"

Hello
I think this is due to something like à bug but i don't know if it is in sql*load or in ETL.
This problem appears also in Oracle jDevelopper and DataStage in 2005.
This is due to the tool's usage (and mismatch) of Oracle internal variables for calculating the length in bytes of multi byte character fields.
On one side there is the nb characters of the fields, and in the other the size in bytes for loading.
As far as I remember, the tools use nb bytes when he should use nb chars, and so, Oracle convert it to nb bytes, which results in converting it twice in bytes, leading to a field too long error.
To get around, I had to declare the field in bytes instead of chars (at least twice the length in chars for utf8) then it does work.
And when using the field, the tool should use the length in chars and when checking the length, the size in bytes, according to Oracle's rules. In oracle's internal dictionary, there are separate data for this.
This is not the solution but only a way to find it.
Hope this help!
Michel:-)
One Star

Re: tOracleBulkExec - "Field in data file exceeds maximum length"

The reason for the problem is that Talend component does not write in the SQL*LOADER control file the length of the fields, therefore SQL*LOADER uses the default length value and that is 255.
There is also 2988
I created a quick fix for VARCHARS in the component by changing tOracleBulkExec_begin.javajet
Please notice this quick fix is working for me but it is not generic for all types just for CHARS types and it is not fully tested this should be referred as workaround until the bug is fixed
replace the following code:
for (int i = 1; i <= colCount_<%=cid %>; i++) {
sb_<%=cid %>.append(rsm_<%=cid %>.getColumnName(i));

if(i < colCount_<%=cid %>){

sb_<%=cid %>.append(", ");

}
}
sb_<%=cid %>.append(")");

by
for (int i = 1; i <= colCount_<%=cid %>; i++) {
int columnType = rsm_<%=cid %>.getColumnType(i);
int precision = rsm_<%=cid %>.getPrecision(i);
sb_<%=cid %>.append(rsm_<%=cid %>.getColumnName(i));

if(i < colCount_<%=cid %>){

//columnType 12 == VARCHAR2
if ( (columnType == 12) && precision >=255){
sb_<%=cid %>.append(" CHAR(" + precision + ")" + ", ");
}else {
sb_<%=cid %>.append(", ");
}

} else if ( (columnType == 12) && precision >=255) {
sb_<%=cid %>.append(" CHAR(" + precision + ")");
} else {
sb_<%=cid %>.append(", ");
}
}
sb_<%=cid %>.append(")");
One Star

Re: tOracleBulkExec - "Field in data file exceeds maximum length"

I got same problem using tOracleBulkExec Perl module of TOS 3.0.3
Aparently bug was fixed on Java module, but NOT on Perl branch :/
Rewriting the SQL*Loader CTR file outside of TOS, I was able to get my data uploaded.
Trick is to add type defnition to any variable containing>255 characters.
Before: "(FIELD1, FIELD2)"
After: "(FIELD1, FIELD2 char(4000))"
Related to 2988
Employee

Re: tOracleBulkExec - "Field in data file exceeds maximum length"

I got same problem using tOracleBulkExec Perl module of TOS 3.0.3
Aparently bug was fixed on Java module, but NOT on Perl branch :/

It was fixed for TOS 3.0.4 thanks to your report pmarc, thank you : 6425