0377-0379

Previous Table of Contents Next

Page 377

NOTE

When using the parallel loader, you can specify multiple input files and tables. The FILE parameter specified in the OPTIONS clause becomes the global default for all INSERT INTO TABLE options clauses. In addition to the OPTIONS clause for the load, each INSERT INTO TABLE clause can have an OPTIONS clause, which specifies the tablespace file where the temporary segments are created. (See Listing 16.1.) If multiple input files are specified or multiple tables are loaded, you should further qualify the options in the options section for that table.

UNRECOVERABLE/RECOVERABLE Clause

The UNRECOVERABLE/RECOVERABLE options apply only to direct path loads; all conventional loads are by definition recoverable. When the database is in archivelog mode, if RECOVERABLE is specified, the loaded data is written to the redo logs. Specifying UNRECOVERABLE bypasses writing to the redo logs, which improves performance (by about 100 percent) but forces you to drop and re-create the loaded data if media recovery is required.

TIP

Unless you are appending data to existing data in a table, you should specify UNRECOVERABLE for performance issues. If for some reason the instance needs recovery, recover it, and truncate the tables into which you were loading the data. Then start the SQL*Loader session again.

LOAD DATA Clause

The LOAD DATA clause is the main statement in the control file. Only comments, the OPTIONS clause, and the RECOVERABLE clause can precede LOAD DATA in the control file. LOAD DATA is followed by phrases and clauses that further qualify it. For the complete syntax of the control file, refer to the Oracle8 Server Utilities Guide.

The LOAD DATA clause begins with the keyword LOAD, optionally followed by the keyword DATA. Note in Listing 16.3 that the keyword CHARACTERSET followed by the character set name is required if the input data file is from a foreign character set, in this case EBCDIC. The control file can have only one LOAD DATA clause.

INFILE Clause

To specify the input file containing the data to be loaded, specify the INFILE or INDDN keyword, followed by the filename and an optional O/S-dependent file-processing specifications string. Note in Listing 16.4 that the string "FIX 28" follows the complete file pathname for a UNIX system. Listing 16.4 contains some examples of file specifications from other platforms.

Page 378

Listing 16.4. Sample file specifications.

 INFILE  myfile.dat INFILE  `c\loader\input\march\sales.dat' INFILE  `/clinical/a0004/data/clin0056.dat'         "recsize 80 buffers 10"         BADFILE `/clinical/a0004/logs/clin0056.bad'         DISCARDFILE `/clinical/a004/logs/clin0056.dsc'         DISCARDMAX 50 INFILE  `clin_a4:[data]clin0056.dat'         DISCARDFILE `clin_a4:[log]clin0056.dsc'         DISCARDMAX 50 

NOTE

As a rule, use single quotes around any file specification that contains punctuation marks.

If the operating system uses a single backslash to specify an escape character, then use a double backslash in directory structures.

Filename specifications and the file-processing specifications string are generally not portable between platforms and might need to be rewritten if you are migrating to a different platform.

Following the INFILE statement is the optional bad file specification, which begins with the keyword BADFILE or BDDN followed by the filename. If no name is specified by the bad file, the name defaults to the name of the data file followed by a .BAD extension. A bad file is created only if records were rejected because of formatting errors or the Oracle kernel returned an error while trying to insert records into the database.

Following the BADFILE statement is the optional discard file specification, which begins with the keyword DISCARDFILE or DISCARDDN and is followed by the filename. Next comes a DISCARDS or DISCARDMAX keyword with an integer specification. SQL*Loader might create a discard file for records that do not meet any of the loading criteria specified in the WHEN clauses in the control file. If no discard file is named and the DISCARDS and DISCARDMAX keywords are not specified, a discard file is not created even if records were discarded. If, however, the DISCARDS or DISCARDMAX keyword is specified on the command line or in the control file, with no discard filename specified, a default file is created with the name of the data file followed by a .DSC extension.

The DISCARDS or DISCARDMAX clause limits the number of records to be discarded for each data file. If a limit is specified, processing for the associated data file stops when the limit is reached.

Page 379

NOTE

For both the bad file and discard files:

• If the file is created, it overwrites any existing files with the same name.
• If the file is not created and a file with the same name already exists, it remains intact.

Table Loading Methods

The table loading method keyword specifies the default global method for loading the tables. You can use one of four methods:

• INSERT
• APPEND
• REPLACE
• TRUNCATE

INSERT is the default method and requires the table to be empty before loading the data file. SQL*Loader terminates with an error if the table is not empty.

APPEND adds new rows to the table, even if the table is empty.

REPLACE uses the SQL DELETE command to delete all rows from the table, performs a commit, and then loads the new data into the table.

TRUNCATE uses the SQL TRUNCATE command to remove the rows from the table, performs a commit, and then loads the new data into the table. All referential constraints must be disabled before the SQL*Loader session begins; otherwise , SQL*Loader terminates with an error.

WARNING

When you use the REPLACE method, if you specified DELETE CASCADE for the table, then the cascaded deletes are also performed. Any delete triggers you defined for the table also fire as the rows are deleted.

When you use the REPLACE or TRUNCATE keyword, all rows are removed from the table before data begins loading into the table. If this result was not your intent, you might need to restore the table from a backup.

When using the parallel load option, you can only APPEND rows. You cannot use REPLACE, TRUNCATE, and INSERT. If you must truncate a table before a parallel load, you must do it manually.

In addition to specifying a global table load method, you can specify a method for each table in the INTO TABLE clause.

Previous Table of Contents Next