Previous | Table of Contents | Next

Page 345

Record 3 is discarded and placed into the discard file because the flag is neither "C" nor "D," thus not satisfying either condition. Record 15 is rejected because the dollar amount is not numeric. These two records show the two stages of checking previously mentioned. Record 3 is discarded by the loader process while record 15 was not rejected until the database tried to insert it into the table.

Example 5—Loading into a Table Partition

This example (see Listings 14.15 and 14.16) is a variation of Example 4, in which we load the data from the xact.dat file. In this example, we load the data into the partition_xact table, which has four partitions to store the data by calendar quarter. A single partition or all partitions of a table can be loaded in the same loader session. In this instance, we only load partition P1 for the first quarter.

Listing 14.15LOAD5.CTL—Example 5 Control File

LOAD DATA
INFILE `xact.dat'
INTO TABLE partition_xact PARTITION (P1)
WHEN xact_flag = `D'
    (acct_nbr    POSITION(01:10)    INTEGER EXTERNAL,
     xact_amt    POSITION(11:20)    INTEGER EXTERNAL ":xact_amt * -1",
     xact_flag    POSITION(21:21)    CHAR,
     xact_date    POSITION(22:31)    DATE "DD-MON-YY" NULLIF xact_date=BLANKS)

INTO TABLE partition_xact PARTITION (P1)
WHEN xact_flag = `C'
    (acct_nbr    POSITION(01:10)    INTEGER EXTERNAL,
     xact_amt    POSITION(11:20)    INTEGER EXTERNAL,
     xact_flag    POSITION(21:21)    CHAR,
     xact_date    POSITION(22:31)    DATE "DD-MON-YY" NULLIF xact_date=BLANKS)

Invoking SQL*Loader for Example 5 from the command prompt:

$ sqlldr example/expass control=load5.ctl log=load5.log bad=load5.bad
                                       discard=load5.dis

The following is the server response:

SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 14:50:32 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 16

Listing 14.16Example 5 Log File Contents

SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 14:50:32 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

											continues

Page 346

Listing 14.16Continued

Control File:   load5.ctl
Data File:      xact.dat
  Bad File:     load5.bad
  Discard File: load5.dis
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table PARTITION_XACT, partition P1, loaded when XACT_FLAG = 0X44(character `D')
Insert option in effect for this partition: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ---- ---- ---- --------------
ACCT_NBR                             1:10    10           CHARACTER
XACT_AMT                            11:20    10           CHARACTER
XACT_FLAG                           21:21     1           CHARACTER
XACT_DATE                           22:31    10           DATE DD-MON-YY

Column XACT_AMT had SQL string
":xact_amt * -1"
 applied to it.
Column XACT_DATE is NULL if XACT_DATE = BLANKS

Table PARTITION_XACT, partition P1, loaded when XACT_FLAG = 0X43(character `C')
Insert option in effect for this partition: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ---- ---- ---- --------------
ACCT_NBR                             1:10    10           CHARACTER
XACT_AMT                            11:20    10           CHARACTER
XACT_FLAG                           21:21     1           CHARACTER
XACT_DATE                           22:31    10           DATE DD-MON-YY

Column XACT_DATE is NULL if XACT_DATE = BLANKS

Record 3: Discarded - failed all WHEN clauses.
Record 5: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 10: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 14: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 15: Rejected - Error on table PARTITION_XACT, column XACT_AMT.
ORA-01722: invalid number
Record 16: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 4: Rejected - Error on table PARTITION_XACT, partition P1.

Page 347

ORA-14401: inserted partition key is outside specified partition
Record 6: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 8: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 12: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition
Record 13: Rejected - Error on table PARTITION_XACT, partition P1.
ORA-14401: inserted partition key is outside specified partition

Table PARTITION_XACT, partition P1:
  3 Rows successfully loaded.
  5 Rows not loaded due to data errors.
  8 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table PARTITION_XACT, partition P1:
  2 Rows successfully loaded.
  5 Rows not loaded due to data errors.
  9 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Space allocated for bind array:                   7168 bytes(64 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:            16
Total logical records rejected:        10
Total logical records discarded:        1

Run began on Fri Nov 21 14:50:32 1997
Run ended on Fri Nov 21 14:50:33 1997

Elapsed time was:     00:00:00.31
CPU time was:         00:00:00.04

Example 5 comments: To load a partition of a partitioned table, the keyword PARTITION needs to be added after the table name in the control file. Note that the partition name, in this instance P1, must be enclosed in parentheses. All partitions are loaded if the keyword PARTITION is omitted. All records that are not in P1's partition range are written to the bad file.

Conventional and Direct Path Loading

SQL*Loader provides two methods to load data, conventional and direct path loading. Conventional path loading is the default for SQL*Loader. To enable direct path loading, DIRECT=TRUE must be added to the command-line parameters when invoking SQL*Loader. As seen in Figure 14.2, conventional path loading has additional steps that direct path loading doesn't. These extra steps add overhead to the process, making conventional path slower than direct path. The additional steps of formatting SQL INSERT statements and going through the buffer cache of the SGA are contending with all other processes that are running concurrently against the database. Although the inclination is to always use direct path for its speed, there are restrictions and cases in which conventional path should be used. This is covered in the following section.

Previous | Table of Contents | Next