Suggestion for loading data of 2M records in to DB

Question

Users upload data file through application (JSF) which has 2 million records, i have to upload it to DB. Loading through JAVA asynchronous call is occupying more memory out-of memory exception and also most of the time it is getting timeout. So for that what i did is, stored uploaded file as CLOB in table1, i use UNIX shell script which runs every 15 minutes to see if table1 has not-processed records, if then read that CLOB file and load in to table2 using SQLLDR in the same shell script.It is working fine, but there is a 15 minutes delay in processing records. So i think the same SQLLDR process can be run through a PL/SQL package or procedure and the same package can be called through JAVA JDBC call.. rite? any examples?

Answer 1

If it's one-time export/import you can use SQL Developer. It enables you to export displayed rows in a loader format. B/Clobs are exported as separate files.

Following Oracle's blog:

LOAD DATA
INFILE 'loader.txt'
  INTO TABLE my_table
  FIELDS TERMINATED BY ','
  ( id         CHAR(10),
    author     CHAR(30),
    created    DATE "YYYY-MM-DD" ":created",
    fname      FILLER CHAR(80),
    text       LOBFILE(fname) TERMINATED BY EOF
  )

"fname" is an arbitrary label, we could have used "fred" and it would have worked exactly the same. It just needs to be the same on the two lines where it is used.

loader.txt:
1,John Smith,2015-04-29,file1.txt
2,Pete Jones,2013-01-31,file2.txt

If you want to know how to dump a CLOB column into a file, please refer to Dumping CLOB fields into files?.