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
Oracle varchar length limitation
Moderators: chulett, rschirm, roy
-
Manfred Hagedorn
- Participant
- Posts: 58
- Joined: Wed Apr 04, 2007 10:02 am
-
Manfred Hagedorn
- Participant
- Posts: 58
- Joined: Wed Apr 04, 2007 10:02 am
further information
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
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
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
Manfred Hagedorn
- Participant
- Posts: 58
- Joined: Wed Apr 04, 2007 10:02 am
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 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
-
Manfred Hagedorn
- Participant
- Posts: 58
- Joined: Wed Apr 04, 2007 10:02 am
upsert works ???
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
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
