SQL*Loader之CASE8

CASE8

1. SQL脚本

[oracle@node3 ulcase]$ cat ulcase8.sql

set termout offrem host write sys$output "Building case 8 demonstration tables. Please wait"drop table lineitem;create table lineitem (l_orderkey number, l_partkey number, l_suppkey number, l_linenumber number, l_quantity number, l_extendedprice number, l_discount number, l_tax number, l_returnflag char, l_linestatus char, l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(17), l_shipmode char(7), l_comment char(43)) partition by range (l_shipdate) ( partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY')), partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')), partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')), partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY')) );exit

2. 控制文件

[oracle@node3 ulcase]$ cat ulcase8.ctl

-- Copyright (c) 1991, 2004 Oracle. All rights reserved. -- NAME -- ulcase8.ctl - SQL*Loader Case Study 8: Loading Partitioned Tables -- -- DESCRIPTION -- This case study demonstrates the following: -- Partitioning of data. -- -- Explicitly defined field positions and datatypes. -- -- Loading data using the fixed-record-length option. -- -- TO RUN THIS CASE STUDY: -- 1. Before executing this control file, log in to SQL*Plus as -- scott/tiger. Enter @ulcase8 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=ulcase8.ctl LOG=ulcase8.log -- -- NOTES ABOUT THIS CONTROL FILE -- This control file loads the lineitem table with fixed-length -- records, partitioning the data according to shipment date. -- -- The INFILE clause specifies that each record in the datafile is -- of fixed length (129 bytes in this example). -- -- The PARTITION clause identifies the column name and location of the -- data in the datafile to be loaded into each column. -- LOAD DATAINFILE 'ulcase8.dat' "fix 129" BADFILE 'ulcase8.bad' TRUNCATE INTO TABLE lineitem PARTITION (ship_q1)(l_orderkey position (1:6) char,l_partkey position (7:11) char,l_suppkey position (12:15) char,l_linenumber position (16:16) char,l_quantity position (17:18) char,l_extendedprice position (19:26) char,l_discount position (27:29) char,l_tax position (30:32) char,l_returnflag position (33:33) char,l_linestatus position (34:34) char,l_shipdate position (35:43) char,l_commitdate position (44:52) char,l_receiptdate position (53:61) char,l_shipinstruct position (62:78) char,l_shipmode position (79:85) char,l_comment position (86:128) char)

3. 数据文件

[oracle@node3 ulcase]$ cat ulcase8.dat

1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP 1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi 1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN REG AIRSQC2C 5PNCy4mM 1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k 1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh 1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN 2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm 3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN FOB 6wQnO0Llg6y 3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN SHIP LhiA7wygz0k4g4zRhMLBAM 3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297

执行后结果：

[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase8.sql

[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase8.ctl

SQL> select L_ORDERKEY,L_PARTKEY,L_SHIPDATE from lineitem;

L_ORDERKEY L_PARTKEY  L_SHIPDAT
---------- ---------- ---------
1          1519       09-SEP-64
1          2731       12-FEB-96
1          3370       29-MAR-96

--因为列数太多，在这里我们只选取三列，可见只有三条记录被插入。

查看日志文件：

[oracle@node3 ulcase]$ cat ulcase8.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 03:30:54 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Control File: ulcase8.ctl Data File: ulcase8.datFile processing option string: "fix 129"Bad File: ulcase8.badDiscard 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: ConventionalTable LINEITEM, partition SHIP_Q1, loaded from every logical record. Insert option in effect for this partition: TRUNCATEColumn Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- L_ORDERKEY 1:6 6 CHARACTER L_PARTKEY 7:11 5 CHARACTER L_SUPPKEY 12:15 4 CHARACTER L_LINENUMBER 16:16 1 CHARACTER L_QUANTITY 17:18 2 CHARACTER L_EXTENDEDPRICE 19:26 8 CHARACTER L_DISCOUNT 27:29 3 CHARACTER L_TAX 30:32 3 CHARACTER L_RETURNFLAG 33:33 1 CHARACTER L_LINESTATUS 34:34 1 CHARACTER L_SHIPDATE 35:43 9 CHARACTER L_COMMITDATE 44:52 9 CHARACTER L_RECEIPTDATE 53:61 9 CHARACTER L_SHIPINSTRUCT 62:78 17 CHARACTER L_SHIPMODE 79:85 7 CHARACTER L_COMMENT 86:128 43 CHARACTER Record 4: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionRecord 5: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionRecord 6: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionRecord 7: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionRecord 8: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionRecord 9: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionRecord 10: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partitionTable LINEITEM, partition SHIP_Q1:3 Rows successfully loaded.7 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: 11008 bytes(64 rows) Read buffer bytes: 1048576Total logical records skipped: 0 Total logical records read: 10 Total logical records rejected: 7 Total logical records discarded: 0Run began on Fri Sep 19 03:30:54 2014 Run ended on Fri Sep 19 03:30:54 2014Elapsed time was: 00:00:00.31 CPU time was: 00:00:00.03

 总结： 在本例中

      1> PARTITION (ship_q1)子句表明只有符合这个分区范围内的值才能被插入，即l_shipdate小于01-APR-1996的数据可被插入。

转载于:https://www.cnblogs.com/ivictor/p/3981517.html

总结

以上是生活随笔为你收集整理的SQL*Loader之CASE8的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。