SQL & PL/SQL

Hi,

I am loading Multiple CSV files into table using sql loader but the length of a column is exceeding the Max length of varchar2 data type i.e. 4000.

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"|"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