Page 333
The Bad FileSQL*Loader goes through a two-stage process in validating data for insertion into the database. The first stage is validating the format of the data according to the specifications in the control file. If the data format or length is inconsistent with the specifications, SQL*Loader writes that record to the bad file. When records pass the first stage of validation, it is passed to the database for insertion.
The second stage of validation then takes place in the database. The database may reject the record for many reasons, some of which could be database check constraints and datatype conversion errors. Second stage validation rejections are also written to the bad file. If the number of rejections reaches a certain threshold (default = 50), the SQL*Loader session is aborted. This threshold can be set at the command line with the errors parameter. The bad file is written in the same format as the original input data file, which enables the bad file records to be loaded using the same control file after the necessary corrections are made.
The Discard FileSQL*Loader writes records to the discard file if there are conditions present in the control file and the record fails all of the conditions. For example, a condition in the control file states that records must have an "X" in column one. Records that do not have an X will not be inserted into the database and will be written to the discard file. Unlike the bad file, the default threshold of discard records is to allow all discards. This threshold can be set lower at the command line with the discardmax parameter.
The Log FileWhen SQL*Loader begins execution, it creates a log file. Any situations that prevent this log file from being successfully created terminate the loader session. The default filename for the log file is the control filename with the .log extension. It is important to note that if you do not give it a new name at the command line using the log parameter, the loader session automatically overwrites the last log with the same name. The log file has multiple sections showing the environment at which the loader session ran with results and summary statistics. Samples of log files are included in the Examples section.
Most control files begin with the keywords:
LOAD DATA
Other keywords that may precede these are --, which are comments, and options, which enable command-line options previously discussed to be included in the control file.
This is followed by the definition to indicate the source external data file to use for the load:
INFILE `mydata.dat'
Multiple data files can be loaded in the same session by specifying multiple INFILE statements:
INFILE `mydata1.dat' INFILE `mydata2.dat'
If the file extension is not specified, SQL*Loader defaults the extension to .dat. Although it is not required to enclose the data file in single quotes, it is highly recommended to avoid incorrect special character translations when specifying full data paths.
Page 334
This is then followed by the loading method (see Table 14.1) to be used for all tables in the loading session.
Table 14.1Table Loading Methods
Method | Description |
INSERT | This is the default method. It assumes that the table is empty before loading. If there are still rows within the table, SQL*Loader will abort. |
APPEND | This method allows rows to be added to the table without affecting existing rows. |
REPLACE | This method deletes existing rows in the table first and then starts loading the new rows. Note that any delete triggers on the table fire when the old rows are deleted. |
TRUNCATE | This method uses the SQL command TRUNCATE to remove the old rows before loading. This is much quicker than REPLACE because delete triggers fire and no rollback is generated. TRUNCATE is not a recoverable command. In order to use this method, the table's referential integrity constraints must be disabled. |
This is followed by the table definition:
INTO TABLE tablename method
in which method is the same as above, but this method only applies to the table specified on this INTO TABLE line.
What follows after the INTO TABLE keywords are the field and datatype specifications. Instead of reviewing all the different options, it is simpler to look at the different examples in the following section.
All examples use the following schema consisting of four tables (see Listing 14.2). This schema simulates a banking schema with customer, account, and transaction tables. For the purposes of demonstrating loading into a partitioned table, the partition_xact table is a duplicate of the transaction table, with the data partitioned based on the quarter in which the transaction took place.
Listing 14.2LIST1.1Sample Schema
create table customer ( cust_nbr number(7) not null, cust_name varchar2(100) not null, cust_addr1 varchar2(50), cust_addr2 varchar2(50), cust_city varchar2(30),
Page 335
cust_state varchar2(2), cust_zip varchar2(10), cust_phone varchar2(20), cust_birthday date) / create table account ( cust_nbr number(7) not null, acct_nbr number(10) not null, acct_name varchar2(40) not null) / create table transaction ( acct_nbr number(10) not null, xact_amt number(10,2) not null, xact_flag char not null, xact_date date not null) / create table partition_xact ( acct_nbr number(10) not null, xact_amt number(10,2) not null, xact_flag char not null, xact_date date not null) PARTITION BY RANGE (xact_date) (PARTITION P1 VALUES LESS THAN (to_date('01-APR-1997','DD-MON-YYYY')), PARTITION P2 VALUES LESS THAN (to_date('01-JUL-1997','DD-MON-YYYY')), PARTITION P3 VALUES LESS THAN (to_date('01-OCT-1997','DD-MON-YYYY')), PARTITION P4 VALUES LESS THAN (MAXVALUE)) /
All examples use the following data files (see Listings 14.3, 14.4, and 14.5).
Listing 14.3cust.datDescription of the Listing
0000001BOB MARIN 123 MAIN ST. TOPEKA KS12345 Â999-555-1234 20-APR-55 0000002MARY JOHNSON 18 HOPE LANE SAN FRANCISCO CA94054 Â415-555-1299 32-JAN-69 0000003RICHARD WILLIAMS 1225 DAFFODIL LANE BOSTON MA98377 0000004WALTER SIMS 1888 PROSPECT AVE. BROOKLYN NY11218 Â718-555-3420 0000005LARRY HATFIELD TWO FIELDS CT. SOMERSET NJ07689 Â732-555-2454 25-DEC-60 0000006LAURA LAU 25 CHRISTOPHER LN SAN BRUNO CA90234 Â510-555-4834 0000123PRISCILLA WONG 888 FORTUNE COURT PHILADELPHIA PA35545 Â 01-JAN-65 0000068SONNY BALRUP 27 KAMA ST. JACKSON HEIGHTS NY10199 Â718-555-9876 07-MAY-61 0023494RUPAL PARIKH 2 FORCE BLVD NEW YORK NY10105 Â212-555-5887 31-DEC-72 0000324CRAIG SILVEIRA 1674 ISLAND ST SMITHTOWN NY12467 Â516-555-5534 27-OCT-74 0000010DANIEL SMITH 35 DIRECT DRIVE BERGEN NJ07899 Â201-555-3734
continues