Previous | Table of Contents | Next

Page 241

This reduces the loading time since sorting records is time-consuming. But if you use this option and the data is not sorted properly, the index will be invalid and you'll have to drop it and recreate it.

The fields condition clause is used with the nullif and defaultif keywords. This clause is used to change the values of certain fields under certain conditions.

The nullif keyword changes the value to null when the condition is met. The defaultif keyword changes the value to blank or zero when evaluated as true.

In Figure 10.12, the nullif keyword is used to change the cost value to nulls when the cost = 175. The defaultif keyword is used to change the value of check_number to null when the check_ number = `N8754'. These changes occur in the first two records.

FIG. 10.12
Using the fields clause
to change the value of
a field.



When the logical record you're loading contains white space in the position you specify, this white space is loaded into the field. It may be advantageous to make the field null because SQL*LOADER doesn't consider white space the same as nulls.

You can ensure that your fields contain nulls rather than white space by using the BLANKS keyword. Specifying nullif fieldname = BLANKS causes the field to be null.

The Concatenation and Continueif Clauses

The concatenation and continueif clauses are used to combine several physical records into a logical record by combining the various records together.

The concatenation clause is the simplest. You use it when SQL*LOADER always has the same number of physical records to combine. The syntax is "Concatenate n." n represents the number of consecutive physical records to combine.

Figure 10.13 shows the use of the concatenation clause. The load program combines two consecutive physical records into one logical record. The concatenate keyword follows the replace keyword in the into clause.

Page 242

FIG. 10.13
Using the concatenate
clause to combine
physical records into
one logical record.



In Figure 10.13, the data has the same number of physical records for each logical record created by the load procedure. Sometimes you don't have the same number of physical records. When this condition happens, you can use the continueif clause to combine the physical records.

The continueif keyword indicates whether the data continues on the next row. You use the keyword with the this, next, and last keywords. These keywords indicate that the current record, next record, or previous record determines whether the next record should be used as the beginning of the logical record.

Figure 10.14 shows the use of the continueif clause. The continueif keyword is placed after the replace keyword. It's followed by the keyword this keyword, indicating that a value on the current row determines whether the next row should be included in the logical record. The remaining value is an asterisk (*) in position 1 of the row.

FIG. 10.14
Using the continueif
clause to combine
physical records.



Since in this example the continueif character is in position 1, this position can't be used for the load data. SQL*LOADER will looks at this position on each row for instructions about combining the records.

Page 243

Executing SQL*LOADER from the Command Line

Earlier in this chapter, you saw that SQL*LOADER can be executed directly from Windows by using the SQL*LOADER icon and associated dialog boxes. SQL*LOADER can also be executed from the command line when you're using a non-Windows operating system such as UNIX.

The basic syntax of the command is shown in Figure 10.15.

FIG. 10.15
The SQL*LOADER
command line initiation
syntax.



The SQLLOAD keyword is used to initiate SQL*LOADER. The command must contain the Oracle database user ID and the name of the control file. The bad file, discard file, and log file default to the name of the control file if no options are set.

In addition, the same default load set as discussed earlier in the chapter. You can change any of the default settings by entering the option and corresponding value on the command line.

The command line options are listed in Table 10.2.

Table 10.2 Command Line Load Options


Option Description
Bad The name of the bad file.
Bindsize The size of the conventional path bind array.
Control The name of the control file.
Data The name of the data file.
Direct Use the direct path load method.
Discard The name of the discard file.
Discardmax The maximum number of discarded records allowed before termination of the procedure.
Errors The maximum number of rejected records allowed before termination of the procedure.
File The name of the file from which to allocate extents.
                                         continues

Page 244

Table 10.2 Continued


Option Description
Load The number of logical records to load with this procedure.
Log The name of the log file.
Parallel Perform a parallel loading procedure.
Parfile The name of the parameter file that can be used in place of entering the options on the command line.
Rows The number of rows to load between direct path data saves or in aconventional path bind array.
Silent Suppress messages during the procedure.
Skip The number of records to skip before loading.
Userid The name and password of the Oracle database.

Summary

SQL*LOADER is Oracle's product for loading text-based data files into Oracle database tables. The procedure can be initiated from Windows using the SQL*LOADER icon in the Oracle icon group. You must have insert authority on the database to use the product.

Several files are important to the SQL*LOADER process. The control file holds the loading instructions and, optionally, the load data. The data file holds the load data.

The log file holds statistics about the success or failure of the loading process. The bad file holds the records rejected during the procedure. The discard file holds the records rejected due to logic in the control file.

The control file contains a number of parts. The Load data keyword initiates the procedure. The infile clause indicates where the load data is located.

The into clause contains the name of the table receiving the logical record. The clause also contains a variety of other values.

A portion of the clause tells indicates where to append the records to the table, replace the existing records in the table to allow for rollback, truncate the current table and insert the records, and insert the records into an empty table.

The into clause indicates whether the data is terminated or enclosed by a specific character. It also contains instructions for where the values are located in the physical record.

The control file can also contain logic that prevents the loading of some records. This logic is contained in a when clause. When records are prevented from loading due because of this logic, they are placed in the discard file.

Previous | Table of Contents | Next