SQLLDR练习
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.507839, "King", "President", , 17-November-1981, 5500.00
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450
控制文件的语法：
LOAD DATA
infile '/home/oracle/a.txt'
APPEND
INTO TABLE HR.T071SE05_D
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ENO INTEGER EXTERNAL,
ELASTN CHAR,
EJOB CHAR,
MNO INTEGER EXTERNAL,
EDATE DATE,
ESALARY INTEGER EXTERNAL
)
希望缺项的行也被加载：
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50
7839, "King", "President", , 17-November-1981, 5500.00
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450
7999, "Tang"
改完成后的控制文件：
LOAD DATA
APPEND
INTO TABLE HR.T071SE05_D
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ENO INTEGER EXTERNAL,
ELASTN CHAR,
EJOB CHAR,
MNO INTEGER EXTERNAL,
EDATE DATE,
ESALARY INTEGER EXTERNAL
)
$ sqlldr hr/oracle_4U control=/home/oracle/a.ctlexternal_table=GENERATE_ONLYlog=b.sql
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Sep 24 21:54:15 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Path used: External Table
CREATE TABLE extt1
(
ENO NUMBER(4),
ELASTN VARCHAR2(20),
EJOB VARCHAR2(20),
MNO NUMBER(4),
EDATE DATE,
ESALARY NUMBER(8,2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY dir1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
BADFILE 'DIR1':'b.bad'
LOGFILE 'DIR1':'b.log'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"ENO" CHAR(4)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"ELASTN" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"EJOB" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MNO" CHAR(4)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"EDATE" CHAR(20)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK "DD-Month-YYYY",
"ESALARY" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'b1.txt', 'b2.txt'
)
)parallel 2REJECT LIMIT UNLIMITED;
页:
[1]