Previous | Table of Contents | Next

Page 231

TIP
You will find it much easier to fix errors in the bad file rather than in the data file in cases where the load procedure is completed. You can fix the errors in the bad file, change your control file to read the bad file instead of the original data file, and change the load option to append. (Load styles are discussed in the Defining the Load Style Section.)
Performing these steps will allow you to avoid deleting the rows you have added to the table. You'll also save the time it takes to reload all of the records in the data file into the table.

Using the Discard File

The discard file is identical in format to the bad file except that it holds the records rejected by the load program because of the user-defined conditional logic contained in the control file program. The discard file has an extension of DSC.

As you'll see in the next section, the control file can contain logic used to evaluate records to be loaded into the table. Records that don't agree with the logic are discarded.

The beauty of the discard file is that it lets you look at the records to see if the logic in the control file was right to reject the records. If you find records that should not have been discarded, you can use the procedure outlined in the tip in the previous section to add the records to the table.

Listing 10.3 illustrates a discard file, which has an identical format. It looks identical to a bad file.

Listing 10.3 L_10_03.TXT—A Typical Discard File

25,01-OCT-22,Y,Pliers,"25",10,"5",01-SEP-22,"01-OCT-22"
25,01-FEB-23,N,Vice Grips,"10",10,,,
35,04-JUN-80,Y,3/4" Wrench,"4",2,"2",01-JUL-80,"15-JUL-80"
35,06-NOV-82,Y,Tool Chest,"16.75",6,"4.75",01-DEC-80,"01-JAN-81"
35,24-APR-81,N,Knife,"7.95",7.95,,,

Defining Control Files

A control file contains the instructions for SQL*LOADER. These instructions consist of the name and location of the data file. The file also contains the loading option that tells Oracle what to do with the records. It contains the names(s) of the tables that will receive the records. It contains a listing of the table columns that are to be populated. The file contains formatting instructions that tell Oracle how to format the physical load record and where to find the values to place into the Oracle table columns. Finally, in some cases the control file can even contain the data that is to be loaded.

The following sections discuss these control file instructions.

Page 232

Defining a Simple Control File

A control file consists of a series of expressions that instruct Oracle how to load the data. The first part of the file script begins with the keywords load data, which means that the procedure lets Oracle know this application is to load data into a table.

The next second part of the file begins with the keyword infile, which tells Oracle the name and location of the data file. This definition begins with the keyword infile. The file name (enclosed in single quotation marks) and file location follows this keyword. When the control file holds the data, an asterisk (*) is placed after the infile keyword.

The next part of the script contains the name of the Oracle table that will receive the data. This name is prefaced by the keywords into table. The final part (enclosed in parentheses) tells Oracle the names of the table columns that will be populated. It also tells Oracle where to find the data in the physical record row, and what type of data it can expect to be received.

A fixed-position control file is shown in Figure 10.5. Fixed-position means a column's values begin and end in the same location in the control file.

FIG. 10.5
A simple fixed-position
control file.


The control file in Figure 10.5 is a fixed-position file. It is this type of file because of the way the data is defined. Each field name is followed by the keyword position, and two numbers separated by a colon and enclosed by parentheses. These values represent the start and end position of the data.

The control file shown in the Figure 10.5 contains the data that is to be loaded by the procedure. Notice that above the data is a series of numbers that represent the column numbers. These numbers are not part of the control file but are shown in this example for the purpose of discussion.

Using the column numbers, the value begins in row 1 and ends in row 4. The first value (1) is the start position parameter for the department column. The second value (4) is the end position parameter. These are the values included in the department definition. They tell SQL*LOADER where the data for the department column is located.

Page 233

The department name column begins at column 6 and ends at column 20. These values are also placed in the position parameters for the department name column definition. A final parameter consists of the data type and size of each of the load fields.

When SQL*LOADER executes the preceding control file in this example, it will place the characters in positions 1_4 into the department field of the Tools table and the characters in position 6_20 in the department_name name field.

The char data type specification tells SQL*LOADER that the characters should be treated as character data. If you specify some type of numeric data type, SQL*LOADER will try to convert the characters to a number. If it can't do this because of alpha characters, it will reject the record and place it into the bad file.

The reason this type of file is called fixed is because the values in each row are in the exact same position. This differs from variable formats such as comma- and quotation-delimited physical records.

Comma-delimited records have a comma that denotes the completion of the value. Quotation-delimited records are enclosed by quotation marks.

Defining the Load Style

The control file allows you to use four different loading styles. The loading style option precedes the keyword into. The loading styles are as follows:

Figure 10.6 shows the replace load style. The load procedure causes the records in the Department table to be deleted before the new records are loaded into the table.

NOTE
Figure 10.6 is not intended as a practice example. It is included for discussion only. If you try to use the control file to populate the department table, you will get the following error message:

Previous | Table of Contents | Next