Re: SQL*Loader and wide columns

Dan Z. Chen wrote:
>
> You can expicitly put CHAR(the-length-you-want), say 4000, for each
> varchar2 field. For example:
>
> ...
> ES_DISP CHAR(2000),
> ...
>
> Good luck!
>
> kenneth.taylor_at_cplc.com wrote:
> >
> > Good afternoon,
> >
> > SQL*Loader has a "default maximum field size for delimited fields"
> > 255. So sayeth the Server Utilities User's Guide. In fact, if more
> > than 255 characters of data per field are included in a the data file,
> > SQL*Loader will not load it.
> >
> > I have some data which needs to be loaded into a table with (a primary
> > key plus) 3 VARCHAR2(2000) columns.
> >
> > CREATE TABLE i48811.es_memo (
> > ES_NUMBER NUMBER(7,0) NOT NULL,
> > ES_REV NUMBER(3,0) NOT NULL,
> > ES_REQUEST VARCHAR2(2000),
> > ES_DISP VARCHAR2(2000),
> > ES_PROG VARCHAR2(2000))
> > TABLESPACE user_data
> > ;
> > The data is extracted from a data source and output to an ASCII data
> > file. The control file is...
> >
> > LOAD DATA
> > INFILE 'c:\esr\es_memo.dat'
> > REPLACE INTO TABLE i48811.es_memo
> > FIELDS TERMINATED BY ','
> > OPTIONALLY ENCLOSED BY '~'
> > TRAILING NULLCOLS
> > (
> > ES_NUMBER ,
> > ES_REV ,
> > ES_REQUEST ,
> > ES_DISP ,
> > ES_PROG )
> >
> > If the data in any of the last 3 columns exceeds 255 characters, the
> > row is rejected. I have increased the memory of the Bind Array to
> > astromomical levels but without effect.
> >
> > Can the "default maximum" be increased?
> >
> > If not, can you suggest a plan B?
> >
> > Thanks in advance,
> >
> > Ken Taylor

SQL*Loader seems to have a problem loading data where there is more than one column with a length > 256. I think you get a message saying something about having more than one 2000 byte buffer, but when you try to reduce the buffer sizes with CHAR(n) it doesn't work till you reduce all but one to under 256. The result we saw was that it reads one of the 2000 byte columns OK but if your other columns exceed 256 bytes you still get loader errors.

If you figure out a way around this please post it.

-- 
        James H. Yoshii  
Intellitech Business Solutions
       yoshii_at_planet.net