SQL*Loader之CASE5
CASE5
1. SQL脚本
[oracle@node3 ulcase]$ cat ulcase5.sql
set termout off rem host write sys$output "Building case 5 demonstration tables. Please wait" drop table emp; drop table proj; 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); create table proj (empno number, projno number); exit
2. 控制文件
[oracle@node3 ulcase]$ cat ulcase5.ctl
-- Copyright (c) 1991, 2004 Oracle. All rights reserved. -- NAME -- ulcase5.ctl - SQL*Loader Case Study 5: Loading Data Into Multiple Tables -- DESCRIPTION -- This case study demonstrates the following: -- Loading multiple tables. -- -- Using SQL*Loader to break down repeating groups in a flat file -- and to load the data into normalized tables. In this way, one -- file record may generate multiple database rows. -- -- Deriving multiple logical records from each physical record. -- -- Using a WHEN clause. -- -- Loading the same field (empno) into multiple tables. -- -- TO RUN THIS CASE STUDY: -- 1. Before executing this control file, log in to SQL*Plus as -- scott/tiger. Enter @ulcase5 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=ulcase5.ctl LOG=ulcase5.log -- -- NOTES ABOUT THIS CONTROL FILE -- REPLACE specifies that if there is data in the tables to be -- loaded (emp and proj), SQL*loader should delete the data before -- loading new rows. -- -- Multiple INTO TABLE clauses load two tables, emp and proj. -- The same set of records is processed three times, using different -- combinations of columns each time to load table proj. -- -- WHEN loads only rows with nonblank project numbers. When projno -- is defined as columns 25...27, rows are inserted into proj only -- if there is a value in those columns. -- -- When projno is defined as columns 29...31, rows are inserted -- into proj only if there is a value in those columns. -- -- When projno is defined as columns 33...35, rows are inserted -- into proj only if there is a value in those columns. -- LOAD DATA INFILE 'ulcase5.dat' BADFILE 'ulcase5.bad' DISCARDFILE 'ulcase5.dsc' REPLACE INTO TABLE EMP (EMPNO POSITION(1:4) INTEGER EXTERNAL, ENAME POSITION(6:15) CHAR, DEPTNO POSITION(17:18) CHAR, MGR POSITION(20:23) INTEGER EXTERNAL) INTO TABLE PROJ -- PROJ has two columns, both not null: EMPNO and PROJNO WHEN PROJNO != ' ' (EMPNO POSITION(1:4) INTEGER EXTERNAL, PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj INTO TABLE PROJ WHEN PROJNO != ' ' (EMPNO POSITION(1:4) INTEGER EXTERNAL, PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj INTO TABLE PROJ WHEN PROJNO != ' ' (EMPNO POSITION(1:4) INTEGER EXTERNAL, PROJNO POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
3. 数据文件
[oracle@node3 ulcase]$ cat ulcase5.dat
1234 BAKER 10 9999 101 102 103 1234 JOKER 10 9999 777 888 999 2664 YOUNG 20 2893 425 abc 102 5321 OTOOLE 10 9999 321 55 40 2134 FARMER 20 4555 236 456 2414 LITTLE 20 5634 236 456 40 6542 LEE 10 4532 102 321 14 2849 EDDS xx 4555 294 40 4532 PERKINS 10 9999 40 1244 HUNT 11 3452 665 133 456 123 DOOLITTLE 12 9940 132 1453 MACDONALD 25 5532 200
执行后结果：
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase5.sql
[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase5.ctl
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- --------- ------- ----- ------ 1234 BAKER 9999 10 5321 OTOOLE 9999 10 2134 FARMER 4555 20 2414 LITTLE 5634 20 6542 LEE 4532 10 4532 PERKINS 9999 10 1244 HUNT 3452 11 123 DOOLITTLE 9940 12 1453 MACDONALD 5532 25 9 rows selected. SQL> select * from proj order by 1; EMPNO PROJNO ----- ------ 123 132 1234 101 1234 103 1234 102 1244 456 1244 133 1244 665 1453 200 2134 236 2134 456 2414 456 EMPNO PROJNO ----- ------ 2414 236 2414 40 4532 40 5321 55 5321 40 5321 321 6542 102 6542 14 6542 321 20 rows selected.
查看一下日志文件：
[oracle@node3 ulcase]$ cat ulcase5.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 00:20:39 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: ulcase5.ctl Data File: ulcase5.dat Bad File: ulcase5.bad Discard File: ulcase5.dsc (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: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO 1:4 4 CHARACTER ENAME 6:15 10 CHARACTER DEPTNO 17:18 2 CHARACTER MGR 20:23 4 CHARACTER Table PROJ, loaded when PROJNO != 0X202020(character ' ') Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO 1:4 4 CHARACTER PROJNO 25:27 3 CHARACTER Table PROJ, loaded when PROJNO != 0X202020(character ' ') Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO 1:4 4 CHARACTER PROJNO 29:31 3 CHARACTER Table PROJ, loaded when PROJNO != 0X202020(character ' ') Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO 1:4 4 CHARACTER PROJNO 33:35 3 CHARACTER Record 2: Rejected - Error on table EMP. ORA-00001: unique constraint (SCOTT.EMPIX) violated Record 8: Rejected - Error on table EMP, column DEPTNO. ORA-01722: invalid number Record 3: Rejected - Error on table PROJ, column PROJNO. ORA-01722: invalid number Table EMP: 9 Rows successfully loaded. 3 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. Table PROJ: 7 Rows successfully loaded. 2 Rows not loaded due to data errors. 3 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Table PROJ: 7 Rows successfully loaded. 3 Rows not loaded due to data errors. 2 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Table PROJ: 6 Rows successfully loaded. 3 Rows not loaded due to data errors. 3 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 4096 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 12 Total logical records rejected: 3 Total logical records discarded: 0 Run began on Fri Sep 19 00:20:39 2014 Run ended on Fri Sep 19 00:20:40 2014 Elapsed time was: 00:00:01.30 CPU time was: 00:00:00.70
查看一下bad file：
[oracle@node3 ulcase]$ cat ulcase5.bad
1234 JOKER 10 9999 777 888 999 2849 EDDS xx 4555 294 40 2664 YOUNG 20 2893 425 abc 102
总结：在本例中，
1> 首先是根据索引，列的数据类型来排除数据文件中的三条记录，注意：1234 JOKER 10 9999 777 888 999违反的是唯一性约束，2849 EDDS xx 4555 294 40的xx与列的数据类型不一致，2664 YOUNG 20 2893 425 abc 102中abc与PROJNO的数据类型不符。首先是排除这三条记录，然后在根据控制文件的格式进行插入。即违反EMP表的数据不会插入到proj表中，即便该数据没有违反proj表的规则。同样，违反proj表的数据也不会插入到emp表中，即使该数据没有违法emp表的规则。
