Experience with SQL Loader

So You were asked to do the Warehouse Load using SQL*LOADER

1. Run!  Don’t collect your things Just run.
2. Still here?  Sigh.....  OK Things you need to know:
3. Step One:  Someone has to run the dbase program on the lake survey statewide files.  That is outside the scope of this document. P:FAWWAREHOUSWARE2001lksurveyprocedurware2001.doc
4. Step Two:  Export the dbase files generated by warehouse load program into Access.
   1. Indexes should be removed from the dbase file before they are imported into MS Access.  Consult the following resource
   2. I had to choose between using Dbase or MS Access to export into a delimited text file.  Dbase had the drawback of exporting   /  /   in cases of a blank date .  MS Access had the drawback of exporting date fields with Hours, minutes, and seconds. 
   3. I chose Access since I don't have dbase installed.
   4. Use ~ as the text separator, and pipe (|) as the delimiter.  This avoids problems caused by quotes and commas in some of the text fields.
5. You need a control (*.ctl) file for each of the tables.  These are stored in P:FAWWAREHOUSWARE2001lksurveyprocedur.
6. to use SQL Loader utility, open the DOS command prompt and navigate to the directory in which you stored your control file.
7. The SQL Loader command looks like this: 

sqlldr warehouse/password@dnr_ora2 control=W_SURVEY.ctl

8. To make sure everything works, you can load the data into the fishdata warehouse on MUIR, which is not the production server. 
9. The control files issue a truncate command on the tables before loading the data.  That means that it erases all the data in the table before trying to load the new data.   (make sure everything works first, so that data is not unavailable for very long).
10. Both the control file and the data file should have a blank line at the end or you will get SQL*Loader-524: partial record found at end of datafile as an error!
11. One field that is in a different order in the warehouse than in the dbase files is the GEAR_CODE.  Note that in the attached control file example the GEAR_CODE is near the top instead of at the end of the list of fields.
12. On the ELECTROFISHING table, in Access some fields that should be integers get changed to doubles.  This causes these fields to exceed the field limits.  Before you export to text, change these field types to integer.
13. On the ELECTROFISHING table in Access get rid of the fields target_spp_type, target_spp_code, target_spp_name 4&5 because the warehouse tables do not have these fields.
14. Since the date fields have hours, minutes, seconds  The control file must identify dates after the fashion: SURVEY_DATE DATE “mm/dd/yyyy HH24:MI:SS” ,
15. Another Error I kept making was not attaching the above code to all the date fields.  Some date fields are in the warehouse as character fields and others are expecting dates.
16. I used search and replace in MS Access to strip the double quotes and commas out of the METHOD field before exporting.  since both of those characters are being used as delimiters, it caused many errors.
17. After each load, check the .log file and .bad file to make sure no records were discarded.
18. Use the package able_fk (‘table_name’,’disable’) to disable any foreign keys that depend on the table you are truncating and loading 
19.  Use the package to re-enable the indexes once you are done  able_fk('table_name','enable')
20. This should only need to be done for two more years (2002 and 2003).  If it is later than  2004 and you are still using this process, it means we have failed in all our struggles.  All hope for humanity is lost.