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