SQL*Loader Environment
SQL*Loader Environment

Execution Steps:
1] The parameter file:

- Open a text editor.
- Type in each parameter with its corresponding value as shown in the parameter file.
- Save the file with the .par extension.
At the command prompt type sqlldr PARFILE = <parfile name>.
2] The Control File:
- The control file is a text file written in a language that SQL*Loader understands.
- The control file describes the task that the SQL*Loader is to carry out. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more.
- It also contains the names and locations of the bad file and the discard file.
- Some of above information (such as name and location of the input file) can also be passed to SQL*Loader as command-line parameters.
- It’s also possible for the control file to contain the actual data to be loaded. This is sometimes done when small amounts of data need to be distributed to many sites, because it reduces (to just one file) the number of files that need to be passed around.

- The LOAD DATA statement is required at the beginning of the control file.
- INFILE * specifies that the data is found in the control file and not in an external data file.
- BADFILE ‘example1.bad’ indicates that all erroneous records must be stored in the file example1.bad.
- DISCARDFILE ‘example1.dsc’ indicates that all discarded records must be stored in the file example1.dsc.
3] Input Datafiles:
- The data to be loaded is contained in one or more datafiles if it is not contained in the control file.
- The data in the datafile can be in the fixed length format, variable length format, or in the stream record format.
0001, —–Rina, 0002, —-Harry, 0003,—–Sudha
For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:
012my,name is,
4] The Log File:
- The names of the control file, log file, bad file, discard file, and data file
- The values of several command-line parameters
- A detailed breakdown of the fields and datatypes in the data file that was loaded
- Error messages for records that cause errors
- Messages indicating when records have been discarded
- A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load
5] The Bad File:
- If one or more records are rejected, the bad file is created and the rejected records are logged.
- If no records are rejected, then the bad file is not created.
6] The Discard File:
- You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files that you want to retain.)
- If no records are discarded, then a discard file is not created.
Leave a Reply
Want to join the discussion?Feel free to contribute!