SQL*Loader之CASE4
CASE4
1. SQL脚本
[oracle@node3 ulcase]$ cat ulcase4.sql
set termout off rem host write sys$output "Building case 4 demonstration tables. Please wait" drop table emp; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2)); create unique index empix on emp(empno); exit
2. 控制文件
[oracle@node3 ulcase]$ cat ulcase4.ctl
-- NAME -- ulcase4.ctl - SQL*Loader Case Study 4: Loading Combined Physical Records -- -- DESCRIPTION -- This case study demonstrates the following: -- Combining multiple physical records to form one logical -- record with CONTINUEIF. -- -- Inserting negative numbers. -- -- Using REPLACE to indicate that the table should be emptied -- before the new data is inserted. -- -- Specifying a discard file in the control file using DISCARDFILE. -- -- Specifying a maximum number of discards using DISCARDMAX. -- -- Rejecting records due to duplicate values in a unique index -- or due to invalid data values. -- -- TO RUN THIS CASE STUDY: -- 1. Before executing this control file, log in to SQL*Plus as -- scott/tiger. Enter @ulcase4 to execute the SQL script for -- this case study. This prepares and populates tables and -- then returns you to the system prompt. -- -- 2. At the system prompt, invoke the case study as follows: -- sqlldr USERID=scott/tiger CONTROL=ulcase4.ctl LOG=ulcase4.log -- -- NOTES ABOUT THIS CONTROL FILE -- DISCARDFILE specifies a discard file named ulcase4.dsc. -- -- DISCARDMAX specifies a maximum of 999 discards allowed before -- terminating the run. For all practical purposes, this allows -- all discards for this test case. In real-world situations, -- there may well be more than 999 discarded records. -- -- REPLACE specifies that if there is data in the table being loaded, -- then SQL*Loader should delete that data before loading new data. -- -- CONTINUEIF specifies that if an asterisk is found in column 1 -- of the current record, then the next physical record after that -- record should be appended to it from the logical record. Note that -- column 1 in each physical record should then contain either an -- asterisk or a nondata value. -- -- The data file (ulcase4.dat) for this case study shows asterisks -- in the first position and, though not visible, a newline character -- is in position 20. Note that clark's commission is -10, and -- SQL*Loader loads the value, converting it to a negative number. -- -- The resulting log file will show that the last two records are -- rejected, given two assumptions. If a unique index is created on -- column empno, then the record for chin will be rejected because -- his empno is identical to chan's. If empno is defined as NOT NULL, -- then chen's record will be rejected because it has no value for -- empno. -- LOAD DATA INFILE "ulcase4.dat" DISCARDFILE "ulcase4.dsc" DISCARDMAX 999 REPLACE CONTINUEIF (1) = '*' INTO TABLE EMP ( 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, DEPTNO POSITION(50:51) INTEGER EXTERNAL, HIREDATE POSITION(52:60) INTEGER EXTERNAL)
3. 数据文件
[oracle@node3 ulcase]$ cat ulcase4.dat
*7782 CLARK MA NAGER 7839 2572.50 -10 2512-NOV-85 *7839 KING PR ESIDENT 5500.00 2505-APR-83 *7934 MILLER CL ERK 7782 920.00 2508-MAY-80 *7566 JONES MA NAGER 7839 3123.75 2517-JUL-85 *7499 ALLEN SA LESMAN 7698 1600.00 300.00 25 3-JUN-84 *7654 MARTIN SA LESMAN 7698 1312.50 1400.00 2521-DEC-85 *7658 CHAN AN ALYST 7566 3450.00 2516-FEB-84 * CHEN AN ALYST 7566 3450.00 2516-FEB-84 *7658 CHIN AN ALYST 7566 3450.00 2516-FEB-84
执行后结果：
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase4.sql
[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase4.ctl
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ --------- ----- --------- ------- ----- ------ 7782 CLARK MANAGER 7839 12-NOV-85 2573 -10 25 7839 KING PRESIDENT 05-APR-83 5500 25 7934 MILLER CLERK 7782 08-MAY-80 920 25 7566 JONES MANAGER 7839 17-JUL-85 3124 25
7499 ALLEN SALESMAN 7698 03-JUN-84 1600 300 25 7654 MARTIN SALESMAN 7698 21-DEC-85 1313 1400 25 7658 CHAN ANALYST 7566 16-FEB-84 3450 25 7 rows selected.
不难发现，数据文件中有9条数据，但是最后插入的只有7条数据。执行sqlldr时，如果没有显性指定日志文件名，则会隐性创建一个同名日志，后缀为.log。
我们不妨来看看日志文件的记录
[oracle@node3 ulcase]$ cat ulcase4.log
SQL*Loader: Release 11.2.0.1.0 - Production on Thu Sep 18 23:41:36 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: ulcase4.ctl Data File: ulcase4.dat Bad File: ulcase4.bad Discard File: ulcase4.dsc (Allow 999 discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: 1:1 = 0X2a(character '*'), in current physical record Path used: Conventional Table EMP, loaded from every logical record. Insert option in effect for this table: REPLACE 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 HIREDATE 52:60 9 CHARACTER Record 8: Rejected - Error on table EMP, column EMPNO. ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO") Record 9: Rejected - Error on table EMP. ORA-00001: unique constraint (SCOTT.EMPIX) violated Table EMP: 7 Rows successfully loaded. 2 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: 4608 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 9 Total logical records rejected: 2 Total logical records discarded: 0 Run began on Thu Sep 18 23:41:36 2014 Run ended on Thu Sep 18 23:41:36 2014 Elapsed time was: 00:00:00.29 CPU time was: 00:00:00.07
可见，第8行记录因违法empno的非空约束，第9行记录因违反唯一索引而抛弃。
抛弃的两条记录可在控制文件指定的DISCARDFILE "ulcase4.dsc"中找到
[oracle@node3 ulcase]$ cat ulcase4.bad
* CHEN AN ALYST 7566 3450.00 2516-FEB-84 *7658 CHIN AN ALYST 7566 3450.00 2516-FEB-84
总结：在本例中，
1> 指定了discard file，discardmax指定丢弃到该文件的记录最多为999条。
2> The REPLACE option executes a SQL DELETE FROM TABLE statement. All rows in the table are deleted and the new data is loaded. The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. --replace会删除所有数据，而不会更新既存的数据。
