sql loader Case Study 1: Loading Variable-Length Data

Case 1 demonstrates:

• A simple control file identifying one table and three columns to be loaded.

• Including data to be loaded from the control file itself, so there is no separate datafile. See Identifying Data in the Control File with BEGINDATA.

• Loading data in stream format, with both types of delimited fields: terminated and enclosed. See Specifying Field Length for Datatypes for Which Whitespace Can Be Trimmed.

Control File for Case Study 1

The control file is ulcase1.ctl:

1)   LOAD DATA
2)   INFILE *
3)   INTO TABLE dept
4)   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
5)   (deptno, dname, loc)
6)   BEGINDATA
   12,RESEARCH,"SARATOGA"
   10,"ACCOUNTING",CLEVELAND
   11,"ART",SALEM
   13,FINANCE,"BOSTON"
   21,"SALES",PHILA.
   22,"SALES",ROCHESTER
   42,"INT'L","SAN FRAN"

Notes:

1. The LOAD DATA statement is required at the beginning of the control file.

2. INFILE * specifies that the data is found in the control file and not in an external file.

3. The INTO TABLE statement is required to identify the table to be loaded (dept) into. By default, SQL*Loader requires the table to be empty before it inserts any records.

4. FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks. Datatypes for all fields default to CHAR.

5. The names of columns to load are enclosed in parentheses. Because no datatype or length is specified, the default is type CHAR with a maximum length of 255.

6. BEGINDATA specifies the beginning of the data.

Running Case Study 1

Take the following steps to run the case study.

1. Start SQL*Plus as scott/tiger by entering the following at the system prompt:

   sqlplus scott/tiger
   
   

   The SQL prompt is displayed.

2. At the SQL prompt, execute the SQL script. for this case study, as follows:

   SQL> @ulcase1
   
   

   This prepares and populates tables for the case study and then returns you to the system prompt.

3. At the system prompt, invoke SQL*Loader and run the case study, as follows:

   sqlldr USERID=scott/tiger CONTROL=ulcase1.ctl LOG=ulcase1.log
   
   

   SQL*Loader loads the dept table, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

Log File for Case Study 1

The following shows a portion of the log file:

Control File:   ulcase1.ctl
Data File:      ulcase1.ctl
  Bad File:     ulcase1.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

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

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
1) DEPTNO                              FIRST     *   ,  O(") CHARACTER            
   DNAME                                NEXT     *   ,  O(") CHARACTER            
2) LOC                                  NEXT     *   ,  O(") CHARACTER            


Table DEPT:
  7 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         0
Total logical records discarded:        0
.
.
.
Elapsed time was:     00:00:01.53
CPU time was:         00:00:00.20    

Notes:

1. Position and length for each field are determined for each record, based on delimiters in the input file.

2. The notation O(") signifies optional enclosure by quotation marks.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24057587/viewspace-734159/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24057587/viewspace-734159/