SqlLoader maximum-length-exceeded error

[DoraC]

Hi,

I'm using SqlLoader to load data into our Oracle 9i database, and am having problems with

Field in data file exceeds maximum length

errors. I know what field is causing this, so I applied a substring as so:

Field char "ltrim(rtrim(substr( :Field, 1, 10 )))",

but the problem persists. The field is defined as data_length = 2000 in our database.

Thanks - any help *very* appreciated...
Dora

 

[Turkbear]

Hi, examine the source file to see if that field actually is enclosed by whatever delimiter you are using..

 

[LKBrwnDBA]

The problem resides in SQL*Loader's character buffer, which if I remember correctly is 256 char.
What you have to do is:

Field char(2000) "ltrim(rtrim(substr( :Field, 1, 10 )))",



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[DoraC]

Thank you LKBrownDba, that completely solved my problem!!!

dora

 

[ddrillich]

BTW, you can use trim instead of ltrim(rtrim....

 

[SirCharles]

why not just increase the size of your char definition in the sqlldr file? You can do this even for clobs or longs. ie. if length of field >4000, you can still specify a larger size in the sqlldr control file
char (60000) works just fine. You'll just have to modify your field type from varchar2(4000) to clob.