Previous | Table of Contents | Next

Page 341

Example 3—Loading with Embedded Data

This example (see Listings 14.11 and 14.12) shows that data does not have to be in a datafile; it can be directly embedded into the control file. The BEGINDATA keyword indicates that all lines after it are data records to be used as the input source for this control file.

Listing 14.11LOAD3.CTL—Example 3 Control File

LOAD DATA
INFILE *
APPEND
INTO TABLE account
FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"`
    (cust_nbr, acct_nbr, acct_name)
BEGINDATA
0000324,89073,SAVINGS
0000324,89074,CHECKING
0000075,111,SAVINGS
0011102,800,CHECKING
0000068,23338,CHECKING

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

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

The following is the server response:

SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 10:45:31 1997

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

Commit point reached - logical record count 5

Listing 14.12Example 3 Log File Contents

SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 10:45:31 1997

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

Control File:   load3.ctl
Data File:      load3.ctl
  Bad File:     load3.bad
  Discard File: load3.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
											continues

Page 342

Listing 14.12Continued

Table ACCOUNT, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ---- ---- ---- --------------
CUST_NBR                            FIRST     *   ,  O(") CHARACTER
ACCT_NBR                             NEXT     *   ,  O(") CHARACTER
ACCT_NAME                            NEXT     *   ,  O(") CHARACTER

Table ACCOUNT:
  5 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:             5
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Nov 21 10:45:31 1997
Run ended on Fri Nov 21 10:45:31 1997

Elapsed time was:     00:00:00.24
CPU time was:         00:00:00.03

Example 3 comments: To load data embedded into the control file, use * instead of an input file name after the keyword INFILE. This example also shows the use of the APPEND loading method to add to the account table. If the APPEND keyword is omitted in this control file, the load would abort because SQL*Loader's default loading method is INSERT, which expects the table to be empty before loading.

Example 4—Loading with Conditional Checking

This example (see Listings 14.13 and 14.14) loads data from the xact.dat file. The control file checks the flag to see whether the amount is a debit or credit and loads the data accordingly.

Listing 14.13Example 4 Control File

LOAD DATA
INFILE `xact.dat'
INTO TABLE transaction
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,

Page 343

     xact_date    POSITION(22:31)    DATE "DD-MON-YY" NULLIF xact_date=BLANKS)

INTO TABLE transaction
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 4 from the command prompt:

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

The following is the server response:

SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 11:33:58 1997

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

Commit point reached - logical record count 16

Listing 14.14Example 4 Log File Contents

SQL*Loader: Release 8.0.3.0.0 - Production on Fri Nov 21 11:33:58 1997

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

Control File:   load4.ctl
Data File:      xact.dat
  Bad File:     load4.bad
  Discard File: load4.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 TRANSACTION, loaded when XACT_FLAG = 0X44(character `D')
Insert option in effect for this table: 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"
												continues

Page 344

Listing 14.14Continued

 applied to it.
Column XACT_DATE is NULL if XACT_DATE = BLANKS

Table TRANSACTION, loaded when XACT_FLAG = 0X43(character `C')
Insert option in effect for this table: 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 15: Rejected - Error on table TRANSACTION, column XACT_AMT.
ORA-01722: invalid number

Table TRANSACTION:
  7 Rows successfully loaded.
  1 Row 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 TRANSACTION:
  7 Rows successfully loaded.
  0 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:         1
Total logical records discarded:        1

Run began on Fri Nov 21 11:33:58 1997
Run ended on Fri Nov 21 11:33:58 1997

Elapsed time was:     00:00:00.35
CPU time was:         00:00:00.02

Example 4 comments: This example shows how to use the WHEN clause in the control file to do conditional loading. The multiple WHEN clauses load into the same table in this example, but they can also be different tables. The dollar amount is multiplied by _1 before being inserted into the table when the transaction flag equals "D." This is an example of being able to apply operators or SQL functions to the data as it is being loaded.

Previous | Table of Contents | Next