Re: Oracle sqlldr text field limit?

Sql loader is taking the default definition and chopping you at 255 chars.

You need something like this

load data
infile "c:\stage\notes.txt"
append
into table NOTES
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(    note_id      ,
     notes  position(*) char(4000)

There are two parts.....use the"char(length)" instead of the default. When I tried to do that for comma delimited fields, sql ldr got into a snit about
the CHAR(4000) part, so I use the position(*) which just means start from wherever
the previous field ended.

Anyway, that worked for me, YMMV

james goodman wrote:

> I've installed Personal Oracle8i on my PC under Windows 98 SE (lots of
> disk, lots of memory) and it seems to be working just fine. But I've hit
> a 'problem' with the loader (SQLLDR) that I can't find described. I
> have a simple text file with many records -- first few columns numeric
> followed by a separator character ($) followed by text -- from a few up
> to 1000 or more before the end of records.
>
> When I run sqlldr to put this data into a simple table TEST defined
> with two columns (A number(10), B varchar2(1500)), where the sqlldr
> control file (guts) looks like: append into table TEST fields
> terminated by '$' (A,B) --
>
> the loader rejects all input lines where the second field is longer than
> 255 characters (otherwise works O.K. on the other records).
>
> Is this a limit to sqlldr or have I overlooked something. (I can
> populate the table TEST with long B columns manually from within
> SQLPLUS).
>
> Thanks in advance for any help, jim goodman jgoodman_at_njcc.com
Received on Fri Dec 28 2001 - 22:34:42 CET