--常规load方式----load.ctl文件load datainfile
--常规load方式
----load.ctl文件
load data
infile 'c:\1.txt'
into table dept
fields terminated by ','
(deptno,dname,loc)
----数据如下
10,sales,virginia
20,accounting,virginia
30,consulting,virginia
40,finance,virginia
----建表
create table dept
(
deptno number(2),
dname varchar2(14),s
loc varchar2(13)
)
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
--sqlldr收到'exceeds maximum length',因为输入文件的字符串默认是255,超过即报错
alter table dept modify dname varchar2(1000);
----load.ctl文件
load data
infile 'c:\1.txt'
into table dept
fields terminated by ','
(deptno,dname,loc)
----数据如下
10,sales,virginia
20,accounting,virginia
30,consulting,virginia
40,financeaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,virginia
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
报错：'exceeds maximum length'
----修改load.ctl
load data
infile 'c:\1.txt'
into table dept
fields terminated by ','
(deptno,dname char(1000),loc)
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
--如何加载定界符
load data
infile 'c:\1.txt'
into table dept
fields terminated by ',' optionally enclosed by ''
(deptno,dname char(1000),loc)
----数据如下
10,sales,virginia,usa
20,accounting,va,virginia
30,consulting,virginia
40,finance,virginia
----结果如下
10,sales,virginia,usa
20,accounting,va,virginia
30,consulting,virginia
40,finance,virginia
----修改load.ctl
load data
infile 'c:\1.txt'
into table dept
fields terminated by x'07' optionally enclosed by ''
(deptno,dname char(1000),loc)
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
----数据如下,用x'07'做分割符
10salesvirginia,usa
20accountingva,virginia
30consultingvirginia
40financevirginia
----结果如下
10salesvirginia,usa
20accountingva,virginia
30consultingvirginia
40financevirginia
--加载日期
alter table dept add last_update date;
----修改load.ctl
load data
infile 'c:\1.txt'
into table dept
fields terminated by x'07' optionally enclosed by ''
(deptno,dname char(1000),loc,
 last_update date yyyy-mm-dd)
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
----数据如下
10salesvirginia,usa2008-01-01
20accountingva,virginia2008-01-01
30consultingvirginia2008-01-01
40financevirginia2008-01-01
--使用函数
alter table dept add entire_line varchar2(200);
----修改load.ctl
load data
infile 'c:\1.txt'
into table dept
fields terminated by x'07' optionally enclosed by ''
trailing nullcols
(deptno,
 dname char(1000) upper(:dname),
 loc              upper(:loc),   
 last_update date yyyy-mm-dd,
 entire_line      :deptno||:dname||:last_update)
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
----数据如下
10salesvirginia,usa2008-01-01
20accountingva,virginia2008-01-01
30consultingvirginia2008-01-01
40financevirginia2008-01-01
--如何加载内嵌换行符
----修改load.ctl
load data
infile 'c:\1.txt'
into table dept
fields terminated by x'07' optionally enclosed by ''
trailing nullcols
(deptno,
 dname char(1000) upper(:dname),
 loc              upper(:loc),   
 last_update date yyyy-mm-dd,
 entire_line      replace(:entire_line,'\\n','chr(10)'))
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
----数据如下
10salesvirginia,usa2008-01-01this is \n aaa
20accountingva,virginia2008-01-01this is \n aaa
30consultingvirginia2008-01-01this is \n aaa
40financevirginia2008-01-01this is \n aaa
select utl_raw.cast_to_raw(chr(6)||chr(13)||chr(10)) from dual
------------
'060d0a'
----修改load.ctl
load data
infile 'c:\1.txt' str x'060d0a'
into table dept
fields terminated by x'07' optionally enclosed by ''
trailing nullcols
(deptno,
 dname char(1000) upper(:dname),
 loc              upper(:loc),   
 last_update date yyyy-mm-dd,
 entire_line)
sqlldr userid='sys/test as sysdba' control='c:\load.ctl' log='c:\log.log'
----数据如下
10salesvirginia,usa2008-01-01this is aaa
20accountingva,virginia2008-01-01this is aaa
30consultingvirginia2008-01-01this is aaa
40financevirginia2008-01-01this is aaa
，