Page 336
Listing 14.3Continued
0011102STEPHEN LEUNG 16 STANFORD CT STANFORD CA96688 Â650-555-1248 05-SEP-76 0011102ALICIA LOWRY 5678 TIMOTHY DR ATLANTA GA47730 0002340JENNIFER LEUNG 1 MURRAY HILL GREENWICH CT78835 Â203-555-7564 1003423HORACE MICHAEL 90 MINISTER ST MINNEAPOLIS MN77788 Â 18-MAR-65 0000223CHRISTOPHER YEE 9077 MUSIC AVE DETROIT MI45345 Â777-555-7785 22-JUL-75 0009032JAMES BORIOTTI 65 FIREMENS LANE COLUMBUS OH37485 Â904-555-5674 0000088HIREN PATEL 69 CLUB ST. NEW YORK NY12445 Â212-555-7822 12-APR-70 0000100RICHARD JI 1225 STEER ST KOBE KS12009 Â999-555-5824 10-OCT-74 0000046DAVID CHOW 49 HUGO DRIVE FLUSHING NY10199 Â718-555-4367 0000758HENRY WALKER 12 SIGMUND ST. CHICAGO IL33890 Â312-555-5567 09-APR-45 0002993GEORGE BLOOM 28 BRIDGEWATER ST SAN MATEO CA90475 Â650-555-2838 25-MAY-63 0009488LISA JONES 30 MISSION ST UNITY FL23899
Listing 14.4acct.datDescription of the Listing
0000001,459023,SAVINGS 0000001,459024,CHECKING 0000003,211108,SAVINGS 0000003,211123,CHECKING 0000006,23388,SAVINGS 0000123,43992,CHECKING 0000123,50699390,LINE OF CREDIT 0000068,23330,SAVINGS 0023494,433020,SAVINGS 0000010,4566,SAVINGS 0000010,4599,CHECKING 0000223,8887544,SAVINGS
Listing 14.5xact.datDescription of the Listing
0000459023 123.45D01-FEB-97 0000459023 1233.86C01-MAR-97 0000459023 987.00P01-DEC-97 0000459024 1000C03-JUN-97 0000211108 875.27D23-JUL-97 0000211123 20987.88C30-DEC-97 0000211123 12500.16D10-JAN-97 0000023388 1.75C19-MAY-97 0000043992 350.00C12-MAR-97
Page 337
0050699390 2899.09D01-SEP-97 0000023330 100D26-JAN-97 0000433020 60.99C20-NOV-97 0000004566 230.23C20-AUG-97 0000004599 14.96D05-JUN-97 0000004599 14.AAD07-JUN-97 0008887544 9999.99D11-JUL-97
This example loads the data in the cust.dat data file into the customer table. Since the data is in fixed-length format, the control file (see Listing 14.6) maps the data to the database by column positions.
Listing 14.6load1.ctlThe Control File
LOAD DATA INFILE `cust.dat' INTO TABLE customer (cust_nbr POSITION(01:07) INTEGER EXTERNAL, cust_name POSITION(08:27) CHAR, cust_addr1 POSITION(28:47) CHAR, cust_city POSITION(48:67) CHAR, cust_state POSITION(68:69) CHAR, cust_zip POSITION(70:79) CHAR, cust_phone POSITION(80:91) CHAR, cust_birthday POSITION(100:108) DATE "DD-MON-YY" NULLIF cust_birthday=BLANKS)
Invoke SQL*Loader using example/expass as the username and password (see Listing 14.7). The control, log, bad, and discard files are passed to SQL*Loader as command-line parameters as previously discussed in the section on using SQL*Loader (see Listing 14.8).
Listing 14.7load1.ctlInvoking the SQL*Loader
$ sqlldr example/expass control=load1.ctl log=load1.log bad=load1.bad discard=load1.dis
The following is the server response:
SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 9:52:36 1997 (c) Copyright 1997 Oracle Corporation. All rights reserved. Commit point reached - logical record count 23
Page 338
Listing 14.8load1.ctlExample 1 Log File Contents
SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 9:52:54 1997 (c) Copyright 1997 Oracle Corporation. All rights reserved. Control File: load1.ctl Data File: cust.dat Bad File: load1.bad Discard File: load1.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 CUSTOMER, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ---- ---- ---- -------------- CUST_NBR 1:7 7 CHARACTER CUST_NAME 8:27 20 CHARACTER CUST_ADDR1 28:47 20 CHARACTER CUST_CITY 48:67 20 CHARACTER CUST_STATE 68:69 2 CHARACTER CUST_ZIP 70:79 10 CHARACTER CUST_PHONE 80:91 12 CHARACTER CUST_BIRTHDAY 100:108 9 DATE DD-MON-YY Column CUST_NAME is NULL if CUST_NAME = BLANKS Column CUST_ADDR1 is NULL if CUST_ADDR1 = BLANKS Column CUST_CITY is NULL if CUST_CITY = BLANKS Column CUST_STATE is NULL if CUST_STATE = BLANKS Column CUST_ZIP is NULL if CUST_ZIP = BLANKS Column CUST_PHONE is NULL if CUST_PHONE = BLANKS Column CUST_BIRTHDAY is NULL if CUST_BIRTHDAY = BLANKS Record 2: Rejected - Error on table CUSTOMER, column CUST_BIRTHDAY. ORA-01847: day of month must be between 1 and last day of month Table CUSTOMER: 22 Rows successfully loaded. 1 Row not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 9216 bytes(64 rows) Space allocated for memory besides bind array: 0 bytes
Page 339
Total logical records skipped: 0 Total logical records read: 23 Total logical records rejected: 1 Total logical records discarded: 0 Run began on Fri Nov 21 09:52:54 1997 Run ended on Fri Nov 21 09:52:54 1997 Elapsed time was: 00:00:00.21 CPU time was: 00:00:00.04
Example 1 comments: Record 2 is rejected due to an invalid date and is written to the bad file (load1.bad). This record can then be corrected in the bad file and loaded by making changes to the same control file to use the bad file as the input file, and adding the keyword APPEND before the keywords INTO TABLE. Also note the use of the NULLIF clause in the control file. Without this clause, records that had blanks for dates would have failed the database date check.
This example (see Listings 14.9 and 14.10) loads the data in the acct.dat data file into the customer table. Because the data is in variable-length format, the control file defines the delimiter used to distinguish between the different data items.
Listing 14.9LOAD2.CTLExample 2 Control File
LOAD DATA INFILE `acct.dat' INTO TABLE account FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"` (cust_nbr, acct_nbr, acct_name)
Invoking SQL*Loader for Example 2 at the command prompt:
$ sqlldr example/expass control=load2.ctl log=load2.log bad=load2.bad discard=load2.dis
The following is the server response:
SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 10:30:48 1997 (c) Copyright 1997 Oracle Corporation. All rights reserved. Commit point reached - logical record count 12
Page 340
Listing 14.10Example 2 Log File Contents
SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 10:30:48 1997 (c) Copyright 1997 Oracle Corporation. All rights reserved. Control File: load2.ctl Data File: acct.dat Bad File: load2.bad Discard File: load2.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 ACCOUNT, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ---- ---- ---- -------------- CUST_NBR FIRST * , O(") CHARACTER ACCT_NBR NEXT * , O(") CHARACTER ACCT_NAME NEXT * , O(") CHARACTER Table ACCOUNT: 12 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 35328 bytes(64 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 12 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Fri Nov 21 10:30:48 1997 Run ended on Fri Nov 21 10:30:49 1997 Elapsed time was: 00:00:00.23 CPU time was: 00:00:00.04
Example 2 comments: All records are successfully loaded. Remember that variable-length data must have delimiters to separate data items within the input data file. When loading delimited character data, it is more efficient to define the maximum length of each char data field. In this example, acct_name char(20) should be used in the control file. If the maximum length is not defined, SQL*Loader uses a default of 255 bytes for the length, which influences how many records are inserted in each execution of the bind array.