Script Failure

A developer was writing a script to set up some configuration data via script. He complained that the script was completing successfully, but the data was not getting inserted. He asked for help. His thought was that the success should mean that the records should be inserted.

Immediately I pointed out that running the script and seeing success means that the script did not throw any exceptions that were not caught. We looked at his script and saw that it was eating any exceptions. The developer floudered around some more. I later advised him to forget about the script, move over to SQL*Plus, and manually execute the SQL statements one at a time.

Bingo. In SQL*Plus, he found that some constraints were being violated and causing exceptions to be thrown. This taught me two things. You shouuld not jump to the script until you are sure your raw SQL is good. You should also not rely on some tool to execute your script if you are not in full command of what it is doing.

SQL Loader to the Rescue

I gave myself an action item to try to do some hands on SQL Loader practice. It turns out it is not that hard for default loading from file. I specified the filename, used absolute positioning, and loaded stuff into my database table.

The reference I used to learn how to do this was the SQL Loader FAQ. You should check it out. The FAQ goes over a lot of common scenarios, and how you use SQL Loader to accomplish your work. Good stuff I tell you.

So far I only tried SQL loading files with 10 to 15 records. The next step is to try to load 10k to 15k records and beyong. I got a good feeling about it. They say that SQL Loader is optimized to handle the big jobs. I should have already known this. The team on my project that loads huge files from the mainframe primarily uses SQL Loader to do the job.

Huge Script

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.