Case 2 demonstrates:
In this case, the field positions and datatypes are specified explicitly.
The control file is ulcase2.ctl.
1) LOAD DATA 2) INFILE 'ulcase2.dat' 3) INTO TABLE emp 4) (empno POSITION(01:04) INTEGER EXTERNAL, ename POSITION(06:15) CHAR, job POSITION(17:25) CHAR, mgr POSITION(27:30) INTEGER EXTERNAL, sal POSITION(32:39) DECIMAL EXTERNAL, comm POSITION(41:48) DECIMAL EXTERNAL, 5) deptno POSITION(50:51) INTEGER EXTERNAL)
The LOAD DATA statement is required at the beginning of the control file.
The name of the file containing data follows the INFILE parameter.
The INTO TABLE statement is required to identify the table to be loaded into.
Lines 4 and 5 identify a column name and the location of the data in the datafile to be loaded into that column. empno, ename, job, and so on are names of columns in table emp. The datatypes (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) identify the datatype of data fields in the file, not of corresponding columns in the emp table.
Note that the set of column specifications is enclosed in parentheses.
The following are a few sample data lines from the file ulcase2.dat. Blank fields are set to null automatically.
7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10 7566 JONES MANAGER 7839 3123.75 20 7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
Take the following steps to run the case study. If you have already run case study 1, you can skip to Step 3 because the ulcase1.sql script. handles both case 1 and case 2.
Start SQL*Plus as scott/tiger by entering the following at the system prompt:
The SQL prompt is displayed.
At the SQL prompt, execute the SQL script. for this case study, as follows:
This prepares and populates tables for the case study and then returns you to the system prompt.
At the system prompt, invoke SQL*Loader and run the case study, as follows:
sqlldr USERID=scott/tiger CONTROL=ulcase2.ctl LOG=ulcase2.log
SQL*Loader loads the 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.
Records loaded in this example from the emp table contain department numbers. Unless the dept table is loaded first, referential integrity checking rejects these records (if referential integrity constraints are enabled for the emp table).
The following shows a portion of the log file:
Control File: ulcase2.ctl Data File: ulcase2.dat Bad File: ulcase2.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 EMP, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO 1:4 4 CHARACTER ENAME 6:15 10 CHARACTER JOB 17:25 9 CHARACTER MGR 27:30 4 CHARACTER SAL 32:39 8 CHARACTER COMM 41:48 8 CHARACTER DEPTNO 50:51 2 CHARACTER Table EMP: 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: 3840 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:00.81 CPU time was: 00:00:00.15
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24057587/viewspace-734160/，如需转载，请注明出处，否则将追究法律责任。