SQL*Loader Refresher

I have started searching for an Oracle development job. Optimally it will be one where I can do a lot of PL/SQL coding. Some jobs out there require experience with SQL*Loader. I have used the tool in the past. But not much. Time to refresh my memory.

SQL*Loader will take a data file as input, and put the data in your database tables. The main driver of reading in that file is the control file. The control file can specify a fixed datafile format. That has the best performance. Or it can specify a variable length. The length of each line can vary, with the size being set in the first couple character of the line.

You run SQL*Loader with the sqlldr command. You pass it a username and password. Plus you give it the name of the control file. It will generate a log file with the same name as the control file, but with a log extension, by default.

SQL*Loader can also generate a bad file. This will contain records rejected by SQL*Loader. It can also generate a discard file. That file has input records from the datafile that were not selected by the constraints specified in the control file. These are optional files to be generated.

There are a few methods that SQL*Loader can use to load data. The first is conventional path loading, where data is copied to a bind array, then inserted into the database table. The second is direct path loading, where blocks are built and written directly to the database. It is fast. The third is external table loading.

If you want to load an Excel file, which I sometimes do, you need to export it to CSV format first. There is a way to put the actual data being loaded right in the control file. This eliminates the need for a separate datafile. You just give them command "INFILE *" in the control file. Then you prepend your data section with the keyword BEGINDATA.

Selective loading of records can be accomplished with the WHEN clause. Just note that you can only use AND in the parts of that clause. OR is not allowed. You can specify the batch size using the ROWS parameter. You tell what table the data is going into using the INTO TABLE clause. That is followed by fields. and optionally their types.

Although I have never done it before, SQL*Loader can read data into collections such as nested arrays or VARRAYs.