ter parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
3.将数据文件和控制文件组合在一起
[oracle@vmoel5u4 ~]$ vi car.ctl1
LOAD DATA
INFILE *
INTO TABLE car
FIELDS TERMINATED BY ','
(maker, model, no_cyl,
first_built_date date "yyyy/mm/dd",
engine, hp,price)
BEGINDATA
Talbot,8/18,4,1923/02/03,ohv,8,295.00
Talbot,10/23,4,1923/03/04,ohv,8.9,375.00
Talbot,12/30,6,1924/01/04,ohv,13.4,550.00
Sunbeam,14/40,4,1924/06/23,ohv,13.9,895.00
Sunbeam,12/30,4,1924/02/28,ohv,11.5,570.00
Sunbeam,20/60,6,1924/02/24,ohv,20.9,950.00
Sunbeam,Twin Cam,6,1926/03/23,ohv,20.9,1125.00
Sunbeam,20,6,1927/03/23,ohv,20.9,750.00
Sunbeam,16,6,1927/09/10,ohv,16.9,550.00
Peugeot,172,4,1928/09/28,sv,6.4,165.00
Austin,7,4,1922/01/22,sv,7.2,225.00
Austin,12,4,1922/01/01,sv,12.8,550.00
Austin,20,4,1916/01/04,sv,22.4,616.00
Lanchester,40,6,1919/01/08,ohv,38.4,1875.00
Lanchester,21,6,1924/01/26,ohv,20.6,950.00
Vauxhall,30/98,4,1919/01/08,sv,23.8,1475.00
Vauxhall,23/60,4,1919/01/27,sv,22.4,1300.00
SQL> conn hr/hr
Connected.
create table car(
maker varchar2(20),
model varchar2(20),
no_cyl varchar2(20),
first_built_date date,
engine varchar2(20),
hp number,
price number(10,2));
Table created.
[oracle@vmoel5u4 ~]$ sqlldr hr/hr control=car.ctl1
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 28 22:25:41 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 17
[oracle@vmoel5u4 ~]$ sqlplus hr/hr
SQL> select count(*) from car;
COUNT(*)
----------
17
4.将数据文件和控制文件分离实现数据装载
[oracle@vmoel5u4 ~]$ cat car.txt
Talbot,8/18,4,1923/02/03,ohv,8,295.00
Talbot,10/23,4,1923/03/04,ohv,8.9,375.00
Talbot,12/30,6,1924/01/04,ohv,13.4,550.00
Sunbeam,14/40,4,1924/06/23,ohv,13.9,895.00
Sunbeam,12/30,4,1924/02/28,ohv,11.5,570.00
Sunbeam,20/60,6,1924/02/24,ohv,20.9,950.00
Sunbeam,Twin Cam,6,1926/03/23,ohv,20.9,1125.00
Sunbeam,20,6,1927/03/23,ohv,20.9,750.00
Sunbeam,16,6,1927/09/10,ohv,16.9,550.00
Peugeot,172,4,1928/09/28,sv,6.4,165.00
Austin,7,4,1922/01/22,sv,7.2,225.00
Austin,12,4,1922/01/01,sv,12.8,550.00
Austin,20,4,1916/01/04,sv,22.4,616.00
Lanchester,40,6,1919/01/08,ohv,38.4,1875.00
Lanchester,21,6,1924/01/26,ohv,20.6,950.00
Vauxhall,30/98,4,1919/01/08,sv,23.8,1475.00
Vauxhall,23/60,4,1919/01/27,sv,22.4,1300.00
[oracle@vmoel5u4 ~]$ cat car.ctl2
LOAD DATA
infile '/home/oracle/car.txt'
APPEND
INTO TABLE hr.car
fields terminated by ","
(maker, model, no_cyl,
first_built_date date "yyyy/mm/dd",
engine, hp,price)
[oracle@vmoel5u4 ~]$ sqlldr hr/hr control=car.ctl2
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 28 22:31:40 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 17
导入成功！
SQL> conn hr/hr
Connected.
SQL> select count(*) from car;
COUNT(*)
----------