sql loader : Load string greater than 4000 characters using sql loader

Question

I need to load a CSV file into table using sql loader but the lenth of a column is exceeding the Max length of varchar2 data type.

Create table statement :

 CREATE TABLE TEST_PIPE_SEP (FILE_NM VARCHAR2(3000), KEY_COL VARCHAr2(4000), DESCR VARCHAR2(4000), RUN_DATE DATE );

CTL file :

load data
into table test_pipe_sep
append
fields terminated by ','
TRAILING NULLCOLS
(
 FILE_NM char(4000) "trim(:FILE_NM)",
 KEY_COL char(4000) "trim(:KEY_COL)",
 DESCR "trim(:DESCR)",
 RUN_DATE "to_date(sysdate, 'dd-mon-yyyy hh24:mi:ss')"
)

CSV sample record :

sample_file_name.csv,"B"|"STRESS_TESTING_SCENARIO_ID"|"TRANCHE_COLLATERAL_TYPE"|"TRANCHE_GUARANTEE_TYPE"|"BS_TYPE"|"CONTRACT_REFERENCE"|"CONTRACT_TYPE"|,Not Present in file2

I just not paste the full text here as it will become lengthy but you can append the length of middle column in CSV or KEY_COL field value to more than 4000 for testing.

SQLLDR :

sqlldr userid=$SQL_CREDENTIALS control=new_test_3.ctl data=data/$filename log=logs/$filename.log bad=logs/$filename.bad skip=1

Please suggest if there is any way where we can load a string of length more than 4000 using SQL LOADER or is there any workaround for this kind of problem.

I am on :

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

mradul · Answer 1 · 2017-09-07T11:21:40.317

I did it ! :)

Change the column data type to CLOB as below :

CREATE TABLE TEST_PIPE_SEP (FILE_NM VARCHAR2(3000), KEY_COL CLOB, DESCR VARCHAR2(4000), RUN_DATE DATE );

Then the CTL I used to load data into CLOB column is as below :

load data
into table test_pipe_sep
append
fields terminated by ','
TRAILING NULLCOLS
(
 FILE_NM char(4000) "trim(:FILE_NM)",
 KEY_COL CHAR(30000) optionally ENCLOSED BY '<' AND '>',
 DESCR "trim(:DESCR)",
 RUN_DATE "to_date(sysdate, 'dd-mon-yyyy hh24:mi:ss')"
)

sql loader : Load string greater than 4000 characters using sql loader

1 Answers1