4)dw sqlldr

1@@@@parallel sql*loading  ~drop all index,  ~diable all constraint and trigger.  like:  sqlldr USERID=sh/sh CONTROL=control03.ctl LOG=control03.log DIRECT=true SKIP_INDEX_MAINTENANCE=true &;  sqlldr USERID=sh/sh CONTROL=control04.ctl LOG=control04.log DIRECT=true SKIP_INDEX_MAINTENANCE=true &;  sqlldr USERID=sh/sh CONTROL=control05.ctl LOG=control05.log DIRECT=true SKIP_INDEX_MAINTENANCE=true &;  ~reenable all constraint and trigger, create all index.  ~summary: one partition for one process at the same time.   without mess data, it is hard to make lab. 2@@@@transformations using sql*loader @@@ @@@<1>create table product like previously. @@@ HR@ocp> conn hr/hr Connected. HR@ocp> ed   1  create table product   2  (   3   product_id  varchar2(20),   4   product_name  varchar2(20),   5   category     varchar2(20),   6   cost_price  number(9,2),   7   sell_price  number(9,2),   8   weight  number(9,2),   9   shipping_charge  number(9,2),  10   manufacturer varchar2(20),  11   supplier    varchar2(20)  12* ) HR@ocp> / Table created. @@@ @@@<2>write control file, add the transformation @@@ [oracle@station78 dwdir]$ cat control04.ctl LOAD DATA INFILE 'product04.dat' append INTO TABLE product WHEN product_id != BLANKS FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (  product_id "upper(:product_id)",  product_name,  category,  cost_price,  weight,  shipping_charge,  manufacturer,  supplier ) @@@ @@@<3>write the test data @@@ [oracle@station78 dwdir]$ cat product04.dat 'SP1242', 'CD LX1', 'MUSC', 8.90, 15.67, 2.5, 2.95, 'RTG', 'CD Inc' 'SP1243', 'CD LX2', 'MUSC', 8.91, 15.68, 2.6, 2.97, 'RTG', 'CD Inc' 'SP1244', 'CD LX3', 'MUSC', 8.92, 15.69, 2.7, 2.99, 'RTG', 'CD Inc' 'SP1245', 'CD LX4', 'MUSC', 8.93, 15.70, 2.8, 2.93, 'RTG', 'CD Inc' 'BLANKS', 'CD LX5', 'MUSC', 8.93, 15.70, 2.1, 2.93, 'RTG', 'CD Inc' ' ', 'CD LX6', 'MUSC', 8.95, 15.74, 2.9, 2.93, 'RTG', 'CD Inc' @@@ @@@<4>run the sqlldr, and inspect the result. @@@ [oracle@station78 dwdir]$ sqlldr USERID=hr/hr CONTROL=control04.ctl LOG=product04.log \ > BAD=product.bad DISCARD=product.dis  DIRECT=true; SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 23 14:10:08 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Load completed - logical record count 7. @@@ [oracle@station78 dwdir]$ cat product.dis ' ', 'CD LX6', 'MUSC', 8.95, 15.74, 2.9, 2.93, 'RTG', 'CD Inc' @@@ [oracle@station78 dwdir]$ cat product04.log SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 23 13:56:46 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Control File:   control04.ctl Data File:      product04.dat   Bad File:     product.bad   Discard File: product.dis  (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation:    none specified Path used:      Direct Table PRODUCT, loaded when PRODUCT_ID != BLANKS Insert option in effect for this table: APPEND    Column Name                  Position   Len  Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- PRODUCT_ID                          FIRST     *   ,  O(') CHARACTER                SQL string for column : "upper(:product_id)" PRODUCT_NAME                         NEXT     *   ,  O(') CHARACTER            CATEGORY                             NEXT     *   ,  O(') CHARACTER            COST_PRICE                           NEXT     *   ,  O(') CHARACTER            WEIGHT                               NEXT     *   ,  O(') CHARACTER            SHIPPING_CHARGE                      NEXT     *   ,  O(') CHARACTER            MANUFACTURER                         NEXT     *   ,  O(') CHARACTER            SUPPLIER                             NEXT     *   ,  O(') CHARACTER            Record 6: Discarded - failed all WHEN clauses. Record 7: Discarded - failed all WHEN clauses. Table PRODUCT:   5 Rows successfully loaded.   0 Rows not loaded due to data errors.   2 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null. Bind array size not used in direct path. Column array  rows :    5000 Stream buffer bytes:  256000 Read   buffer bytes: 1048576 Total logical records skipped:          0 Total logical records read:             7 Total logical records rejected:         0 Total logical records discarded:        2 Total stream buffers loaded by SQL*Loader main thread:        1 Total stream buffers loaded by SQL*Loader load thread:        0 Run began on Thu Aug 23 13:56:46 2012 Run ended on Thu Aug 23 13:56:47 2012 Elapsed time was:     00:00:00.53 CPU time was:         00:00:00.03 @@@ @@@summary: Here, I write the WHEN clause in the control file, the bad file after running sqlldr @@@do not appear, the discard file appear instead. I am not sure the BLANKS mean a word or a char.