DSXchange

Hello,
i have problems to load varchar fields with a length greater 255 to oracle.
I always get: Field in data file exceeds maximum length
Even if i load the data with "table create" option, the data doesn't fit ???
Is there any special limitation when doing this with Datastage?
Loading the same data with a pure SQL-Loader script makes no problems.
Can anybody give me a hint?
Thank a lot !
Manfred

Hello,
some further information:

255 / 256 really seems to be the threshold.
Fields with 255 length are possible; 256 not.

Even if i read from an existing oracle e.g. a field with varchar 1000,
the job fails when writing this into an other oracle table.

Even, if i try to load 256 in a field with length 2000, it doesn't fit.
When routing the output to a file, i see, the length is as expected,
only 256

Manfred

Try specifying as LongVarChar.

Hello Ray,
i changed the field to LongVarChar ... but still get the same error.
Meanwhile i found, that char has this limit of 255, or?

The Loader-Log looks like:
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Jun 22 12:01:07 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Control File: ora.929942.528609.0.ctl
Character Set WE8ISO8859P1 specified for all input.

Data File: ora.929942.528609.fifo.0
Bad File: ora.929942.528609.0.log.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 100000 rows, maximum of 100000000 bytes
Continuation: none specified
Path used: Conventional

Table ED_CARO_TEST, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
I_TRX_ID FIRST 12 , O(") CHARACTER
V_ABT NEXT * , O(") CHARACTER
I_BEARB_ERF NEXT 12 , O(") CHARACTER
I_FB_BS_ID NEXT 12 , O(") CHARACTER
V_TRX_CODE NEXT * , O(") CHARACTER
V_ORGASTELLE NEXT * , O(") CHARACTER
I_AUTO_MASS_PRUEF_VZ NEXT 12 , O(") CHARACTER
V_TRX_USERID_MA NEXT * , O(") CHARACTER
V_TRX_NAME_MA NEXT * , O(") CHARACTER
V_TRX_DATUM NEXT * , O(") CHARACTER
V_TRX_ZEIT NEXT * , O(") CHARACTER
V_CIN1 NEXT * , O(") CHARACTER
V_CIN2 NEXT * , O(") CHARACTER
V_KTO NEXT * , O(") CHARACTER
BT1X NEXT * , O(") CHARACTER
V_KARTEN_NR NEXT * , O(") CHARACTER
V_GEGEN_KTO NEXT * , O(") CHARACTER
N_BETRAG NEXT 15 , O(") CHARACTER
V_RESERVE1 NEXT * , O(") CHARACTER
V_RESERVE2 NEXT * , O(") CHARACTER
V_RESERVE3 NEXT * , O(") CHARACTER
V_RESERVE4 NEXT * , O(") CHARACTER
V_RESERVE5 NEXT * , O(") CHARACTER

value used for ROWS parameter changed from 100000 to 21195
Record 1: Rejected - Error on table ED_CARO_TEST, column BT1X.
Field in data file exceeds maximum length

I searched in hundrets of posts here ...
In on post i found the same problem ... there it was told, that upsert instead of append works.
I tested it ... and found the same.
So i would know a workaround.
But what is the reason ???
Manfred

It is related to the ctl file being generated as it specifies CHARACTER for the fields. Perhaps there is a patch available for this?