Unable to load large amount of data when running the SQL Loader Process through Java

Question

My Sample CSV File that I need to upload to DB has a little over 10000 records. I am executing sql loader command through Java method call:

process = Runtime.getRuntime().exec(sqlLdrCmd);

this uploads 4924 records and then the execution just hangs waiting for CSV to upload.

just wanted to highlight that this is just a sample CSV that I am using, my actual csv would contain close to a million records.

My ctl File looks something like this:

OPTIONS (SKIP=1)
LOAD
  DATA 
  INFILE 'samples.csv'

  BADFILE 'samples.bad'
  DISCARDFILE 'samples.dsc'
  APPEND INTO TABLE TAB_SAMPLE
    EVALUATE CHECK_CONSTRAINTS
    REENABLE DISABLED_CONSTRAINTS
    EXCEPTIONS EXCEPTION_TABLE
  FIELDS TERMINATED BY "," 
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  ( 
    SOURCE_NAME,
ASSOC1_NAME,
ASSOC2_NAME,
ASSOC3_NAME,
ENTITY_NAME,
ATTRIBUTE_GROUP,
ATTRIBUTE_NAME,
TYPE,
STRING_VALUE,
INTEGER_VALUE,
LONG_VALUE,
FLOAT_VALUE,
DOUBLE_VALUE,
BOOLEAN_VALUE,
TIMESTAMP_VALUE,
TIME_POINT_VALUE
)

The command runs just fine and uploads entire data when I execute from prompt directly.

There is no Foreign key constraint on this table

please suggest way out, let me know if anything is unclear

Answer 1

I think you should split the process. I don't know which database are you using but SQL server has an 8k limit.