Field in data file exceeds maximum length)

[kool_cons]

I am trying to load an oracle table through datastage Enterprise. It uses SQL loader to load this table from a pipe generated control file. I have a field which is rejecting that record which is description, currently it is varchar2 (1000), the characters are no way near hitting 1000 characters, but this is not the problem for any other column, i only get (Field in data file exceeds maximum length) for some of the records. I know there is a limitation(255chars) on varchar fields if there is no length defined in the control file. Solution is to modify the length to 1000 in the control file. Is there any setting in adminstrator that actually tells the control file to use the schema from the DS job. Can we modify this field lenght in the control file some how?


can anyone help in any way please!!!!

[ray.wurlod]

I've encountered this error when writing to Oracle with NLS enabled. It seems to be a bug in sqlldr, because INSERT handles the same value quite happily.

In my case it was a 94 character string, and the column data type was VARCHAR2(100).

It was suggested that the data type declaration should have been VARCHAR2(100 CHAR) because the default is VARCHAR2(100 BYTE) but that suggestion had to be discarded when the INSERT worked quite happily.

(The 94-character (Japanese) string was more than 100 bytes long.)

We were never able to get sqlldr to handle it (Oracle 9i) but did not pursue it once the workaround of using INSERT was found. It was only a seven million row load.