SQL*Loader之CASE8

来源：互联网发布：中国陆军战斗力知乎编辑：程序博客网时间：2024/05/22 08:29

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 DATA  INFILE 'ulcase8.dat' "fix 129"BADFILE 'ulcase8.bad'TRUNCATEINTO TABLE lineitemPARTITION (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.ctlData File:      ulcase8.dat  File processing option string: "fix 129"  Bad File:     ulcase8.bad  Discard File:  none specified  (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     64 rows, maximum of 256000 bytesContinuation:    none specifiedPath used:      ConventionalTable LINEITEM, partition SHIP_Q1, loaded from every logical record.Insert option in effect for this partition: TRUNCATE   Column 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:          0Total logical records read:            10Total logical records rejected:         7Total logical records discarded:        0Run began on Fri Sep 19 03:30:54 2014Run ended on Fri Sep 19 03:30:54 2014Elapsed time was:     00:00:00.31CPU time was:         00:00:00.03

总结：在本例中

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

0 0