Previous | Table of Contents | Next

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.

Control File Syntax

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.

Looking at SQL*Loader Examples

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.1—Sample 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.dat—Description 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

Previous | Table of Contents | Next