I had a task to deal with a large amount of data for our customer. Being the programmer that I am, I got a couple files with the data. Then I ran them through a custome Java program to extract out the data of interest. Finally I stuck the data in a massive SQL script to do some work.I did all my tests with some dummy data. Then when I got the actual data in the script, I ran one last smoke test to ensure the thing worked. It did not. The script kept hanging without inserting a single record. I called my DBA. He told me I should have used SQL*Loader. Well I prefer custom PL/SQL. Plus I needed to implement some logic for each of the inserts.
There was a tight deadline for this work to be completed. I decided to split the script up into a number of smaller scripts that actually did complete. The original script had about 300,000 lines in it. I found that 20,000 and 30,000 line scripts worked fine. So my plan is to break the big mamma up into 10 separate script. I will test them out and let you know how I fare.
New action item for self - learn how to do stuff with SQL*Loader.
