I want to insert in a table data (daily) coming from diverse text files, each one with some millions of lines. I´m using Oracle EE 9.2.0.5, so I´m thinking in the lines of :

a) load the text file (with sqlldr) into a common, heap table, and use the MERGE command into the destination table: main problem here, I will need some extra working space inside the database to keep the staging table, and will need to truncate them after the load.

b) mount the external text file as an external table, and use just one INSERT /*+ APPEND */ from external into the destination. Main problem here : the destination table HAVE a primary key, and I need to keep a list of bad (duplicated/invalid) records, and INSERT runs as a whole, any bad record will mean an error as a whole to the INSERT command.

c) mount the text file as external table, and using MERGE with the destination table WHEN NO_MATCHED INSERT nnnn : main problem here, the WHEN_MATCHED do not permit (AFAIK) insert into another table - a "log error table" , one.

d) PL/SQL program, reading in bulk from external table, inserting FORALL with EXCEPTIONS clause. Will do all what I want (probably), but will require a non-trivial level of work.

Any ideas more ??