SQLLDR练习

botang

1. 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50
   
2. 7839, "King", "President", , 17-November-1981, 5500.00
   
3. 7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00
   
4. 7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75
   
5. 7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00
   
6. 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50
   
7. 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450
   

复制代码

控制文件的语法：

1. LOAD DATA
   
2. infile '/home/oracle/a.txt'
   
3. APPEND
   
4. INTO TABLE HR.T071SE05_D
   
5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   
6. (
   
7.   ENO INTEGER EXTERNAL,
   
8.   ELASTN CHAR,
   
9.   EJOB CHAR,
   
10.   MNO INTEGER EXTERNAL,
    
11.   EDATE DATE,
    
12.   ESALARY INTEGER EXTERNAL
    
13. )
    

复制代码

希望缺项的行也被加载：

1. 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50
   
2. 7839, "King", "President", , 17-November-1981, 5500.00
   
3. 7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00
   
4. 7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75
   
5. 7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00
   
6. 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50
   
7. 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450
   
8. 7999, "Tang"
   

复制代码

改完成后的控制文件：

1. LOAD DATA
   
2. APPEND
   
3. INTO TABLE HR.T071SE05_D
   
4. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   
5. TRAILING NULLCOLS
   
6. (
   
7.   ENO INTEGER EXTERNAL,
   
8.   ELASTN CHAR,
   
9.   EJOB CHAR,
   
10.   MNO INTEGER EXTERNAL,
    
11.   EDATE DATE,
    
12.   ESALARY INTEGER EXTERNAL
    
13. )
    

复制代码

1. [oracle@classroom ~]$ sqlldr hr/oracle_4U control=/home/oracle/a.ctl  external_table=GENERATE_ONLY  log=b.sql
   
2. 
   
3. SQL*Loader: Release 12.1.0.2.0 - Production on Thu Sep 24 21:54:15 2020
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Path used:      External Table
   

复制代码

1. CREATE TABLE extt1
   
2. (
   
3.   ENO NUMBER(4),
   
4.   ELASTN VARCHAR2(20),
   
5.   EJOB VARCHAR2(20),
   
6.   MNO NUMBER(4),
   
7.   EDATE DATE,
   
8.   ESALARY NUMBER(8,2)
   
9. )
   
10. ORGANIZATION external
    
11. (
    
12.   TYPE oracle_loader
    
13.   DEFAULT DIRECTORY dir1
    
14.   ACCESS PARAMETERS
    
15.   (
    
16.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    
17.     BADFILE 'DIR1':'b.bad'
    
18.     LOGFILE 'DIR1':'b.log'
    
19.     READSIZE 1048576
    
20.     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    
21.     MISSING FIELD VALUES ARE NULL
    
22.     REJECT ROWS WITH ALL NULL FIELDS
    
23.     (
    
24.       "ENO" CHAR(4)
    
25.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    
26.       "ELASTN" CHAR(255)
    
27.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    
28.       "EJOB" CHAR(255)
    
29.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    
30.       "MNO" CHAR(4)
    
31.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    
32.       "EDATE" CHAR(20)
    
33.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    
34.         DATE_FORMAT DATE MASK "DD-Month-YYYY",
    
35.       "ESALARY" CHAR(255)
    
36.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    
37.     )
    
38.   )
    
39.   location
    
40.   (
    
41.     'b1.txt', 'b2.txt'
    
42.   )
    
43. )parallel 2  REJECT LIMIT UNLIMITED;
    

复制代码