Loading Variable-Length Data
Case 1 demonstrates
- A simple control file identifying one table and three columns to be loaded. See Identifying Data in the Control File with BEGINDATA.
- 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 Delimited Fields.
Control File
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:
- The LOAD DATA statement is required at the beginning of the control file.
- INFILE * specifies that the data is found in the control file and not in an external file.
- 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.
- 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.
- Specifies that the names of columns to load are enclosed in parentheses. Since no datatype is specified, the default is a character of length 255.
- BEGINDATA specifies the beginning of the data.
Invoking SQL*Loader
To run this example, invoke SQL*Loader with the command:
sqlldr userid=scott/tiger control=ulcase1.ctl log=ulcase1.log
SQL*Loader loads the DEPT table and creates the log file.
Additional Information: The command
"sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your
operating system, refer to your Oracle operating system-specific
documentation.
Log File
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 65536 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: 65016 bytes(84 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 7 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Sun Nov 08 11:08:19 1998 Run ended on Sun Nov 08 11:08:20 1998 Elapsed time was: 00:00:01.16 CPU time was: 00:00:00.10
Notes:
No comments:
Post a Comment