Abhishek Mish wrote:Thanks for the response..
I think what i understand is execuitng SQLLDR command from java class which will be faster.
But my concern is after executing sqlldr, I need to get the execution type so that i can show the message in the UI that it is successful or failed.
But still i think using this approach can add to lot of user firendly limitations.
Thanks,
Abhishek
This is a bulk load yes? Its never going to be that user friendly operation; its only ever done be DBAs and simmilar people, because ultimately it may require some DBA style tasks to do it (disabling constraints, using parallel threads, using direct loading etc.). Are you anticipating your bulk load will be performed by people who won't be able to understand SQLLDR's output?
This aside your biggest headache is the requirement to commit only when
all the records have been validated. If the data set is of any significant size wrapping the whole thing in a single transaction is a non-starter in any loading tool. And since some of your validation is from data in the database itself the longer the load time the more likely the data will be stale before being commited. I think you may need to apply more control to this by using staging tables; upload the data outside of a transaction to staging tables, validate the data in the database, then perform your insert based on the data in the staging tables. To do this you may have to lock the data you are validating with in some way. Not nice I'll admit, but a transactional bulk load is hard.
Just out of curiosity, why does this need to be transactional? It's not a normal requirement for a bulk load of any significant size.