Can SQL Load large CLOB columns ?

From: Dominic Bines <dombaines_at_yahoo.com>
Date: 5 Feb 2002 07:34:21 -0800
Message-ID: <205f56a7.0202050734.6f4d3b08@posting.google.com>

Trying to SQL Load into a table text of varying length...

The data is free text that needs to be stored as is. It isn't XML nor will it convert readily.

The table structure is:

col1 varchar2(100)
col2 varchar2(100)
col3 varchar2(100)
col4 varchar2(100)
col5 varchar2(100)

exptdata CLOB

The CLOB column values range from 20,000 characters to 20,000,000 characters.... (OCR'd pages of expt reports to almost whole lab books)

All seesm to go well except when a column contains more than c1M characters which happens too often !

Anyone advise a way forward... I can't split the feeder files up, not easily anyway :-)

The controlfile is:

LOAD DATA
INFILE 'exptresult_somenumber' "str '|/n'" INTO TABLE exptresults FIELDS terminated by '|' TRAILING NULLCOLS (col1, col2, col3, col4, col5, exptdata char(300000000) )

The error message is:
SQL*Loader-510: Physical record in data file (exptresult_somenumber) is longer than the maximum(1048576)

Regards,

Dominic Baines Received on Tue Feb 05 2002 - 09:34:21 CST