SQL*Loader and wide columns

From: <kenneth.taylor_at_cplc.com>
Date: 1997/01/13
Message-ID: <32da6655.1908124@news>#1/1

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 Received on Mon Jan 13 1997 - 00:00:00 CST