使用sqlldr装载数据的简单例子

创建测试表：
create table DEMO
(
  DEPTNO NUMBER(2) not null,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
);[@more@]

例子1：装载定界数据类型为csv(Comma-Separated Value)

C:sqlldr userid=scott/tiger control=load.ctl data=data.csv direct=y

load.ctl内容如下：

LOAD DATA                                                 
INFILE *                                                  
INTO TABLE DEMO                                           
TRUNCATE                                                   
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'       
(DEPTNO,
DNAME,
LOC
) 

数据文件data.csv的内容如下：

"10","ACCOUNTING","NEW YORK"
"20","RESEARCH","DALLAS"
"30","SALES","""CHICAGO"""
"40","OPERATIONS","BOSTON"
"50","","Virginia"
"60",,"Virginia"

例子2：装载定界数据类型为tsv(制表符分隔)

C:sqlldr userid=scott/tiger control=load.ctl data=data.tsv direct=y

load.ctl内容如下：

LOAD DATA                                                 
INFILE *                                                  
INTO TABLE DEMO                                           
TRUNCATE                                                   
FIELDS TERMINATED BY X'09'
(DEPTNO,
DNAME,
LOC
)

数据文件data.tsv的内容如下：

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES "CHICAGO"
40 OPERATIONS BOSTON
50  Virginia
60  Virginia

注意：连续两个制表符表示中间的列为空

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3898/viewspace-787851/，如需转载，请注明出处，否则将追究法律责任。