SQL Loader, CLOB, delimited fields
734031Nov 20 2009 — edited Nov 23 2009Hello.
I have to load using SQL Loader data from csv file into table, which one field is CLOB type.
Here is how ctl file initially looked like:
UNRECOVERABLE
LOAD DATA
INFILE '.\csv_files\TSH_DGRA.csv'
BADFILE '.\bad_files\TSH_DGRA.bad'
DISCARDFILE '.\dsc_files\TSH_DGRA.dsc'
APPEND
INTO TABLE TSH_DGRA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID_OBJ_TSHD,
PR_ZOOM_TSHD,
PR_GRID_TSHD,
PR_ELMGR_TSHD CHAR(4000) OPTIONALLY ENCLOSED BY '<clob>' AND '</clob>',
PR_ALRMGR_TSHD CHAR(4000) OPTIONALLY ENCLOSED BY '<clob>' AND '</clob>'
)
Problems are fields PR_ELMGR_TSHD and PR_ALRMGR_TSHD (CLOBs in table TSH_DGRA). Until data which should be loaded into CLOB fields are under 4000 characters long, it works fine, but what should I do if I want to load data which are longer than 4000 characters?
If found on Link:[http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/ldr_loading.htm#i1006803] which one sentence said that:
"SQL*Loader defaults to 255 bytes when moving CLOB data, but a value of up to 2 gigabytes can be specified. For a delimited field, if a length is specified, that length is used as a maximum. If no maximum is specified, it defaults to 255 bytes. For a CHAR field that is delimited and is also greater than 255 bytes, you must specify a maximum length. See CHAR for more information about the CHAR datatype."
So, my question is, how to specify "up to 2gb" as text said? I can not use CHAR datatype because it is limited to 4000 characters. And I have to load about 60000 characters. I also can not use technique where all data for every CLOB field are in separate files.