6， 将多个数据flat 文件同时load 进数据库
LOAD DATA
INFILE 'c:\data\mydata.csv' "str '*****'"
INFILE 'c:\data\mydata2.csv' "str '*****'"
REPLACE INTO TABLE book
fields terminated by "," optionally enclosed by '"'
(
book_title ,
book_price ,
book_pages ,
book_id
)
Mydata.csv 数据内容如下：
Oracle Essentials bbbbbbbbbbbbb
,3495,355,1 *****
SQL*Plus: The Definitive
Guide ,3995,502,2 *****
Oracle PL/SQL
Programming ,4495,87,3 *****
Oracle8 Design Tips ,1495,115,4 *****
Mydata2.csv 数据内容如下：
新概念1
,3495,355,1 *****
新概念2
,3995,502,2 *****
新概念3
,4495,87,3 *****
新概念4 ,1495,115,4 *****
例子：
SQL> truncate table book;
表被截断。
SQL> host sqlldr hr/hr control =c:\data\loader.ctl log=c:\data\log.log
SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 8月 14 10:32:55 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 4
达到提交点 - 逻辑记录计数 8
SQL> select * from book;
BOOK_ID BOOK_TITLE BOOK_PRICE BOOK_PAGES
---------- ----------------------------------- ---------- ----------
1 Oracle Essentials bbbbbbbbbbbbb 3495 355
2 SQL*Plus: The Definitive 3995 502
3 Oracle PL/SQL 4495 87
4 Oracle8 Design Tips 1495 115
1 新概念1 3495 355
2 新概念2 3995 502
3 新概念3 4495 87
4 新概念4 1495 115

已选择8行。
7， 如果文件名中包含有特殊字符，可以使用“\” 逃逸字符
如以下例子
INFILE 'mi_\'deci' 实际文件名为：mi_'deci
INFILE "mi_\"deci' 实际文件名为：mi_"deci
INFILE "mi_\\deci' 实际文件名为：mi_\deci
8，load 数据到表分区：
LOAD DATA
INFILE 'c:\data\mydata.csv' "str '*****'"
REPLACE INTO TABLE book PARTITION (p1)
fields terminated by "," optionally enclosed by '"'
(
book_title ,
book_price ,
book_pages ,
book_id
)
例子：
create table BOOK
(
BOOK_ID NUMBER,
BOOK_TITLE VARCHAR2(35),
BOOK_PRICE NUMBER,
BOOK_PAGES NUMBER
)
partition by range (book_id)(
partition p1 values less than (10),
partition p2 values less than (20))

SQL> host sqlldr hr/hr control =c:\data\loader.ctl log=c:\data\log.log
SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 8月 14 1