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.
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.CTLExample 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.
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.