Previous | Table of Contents | Next

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

Example 1—Loading Fixed-Length Data

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.ctl—The 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.ctl—Invoking 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.ctl—Example 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.

Example 2—Loading Variable-Length Data

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.CTL—Example 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.

Previous | Table of Contents | Next