Oracle資料庫的Load詳解

--常規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'做分割符
10Sales"Virginia,USA"
20Accounting"Va,""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'
 
----資料如下
10Sales"Virginia,USA""2008-01-01"
20Accounting"Va,""Virginia""""2008-01-01"
30ConsultingVirginia"2008-01-01"
40FinanceVirginia"2008-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'
 
----資料如下
10Sales"Virginia,USA""2008-01-01"
20Accounting"Va,""Virginia""""2008-01-01"
30ConsultingVirginia"2008-01-01"
40FinanceVirginia"2008-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'

----資料如下
10Sales"Virginia,USA""2008-01-01"this is \n aaa
20Accounting"Va,""Virginia""""2008-01-01"this is \n aaa
30ConsultingVirginia"2008-01-01"this is \n aaa
40FinanceVirginia"2008-01-01"this 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'

----資料如下
10Sales"Virginia,USA""2008-01-01"this is aaa
20Accounting"Va,""Virginia""""2008-01-01"this is aaa
30ConsultingVirginia"2008-01-01"this is aaa

40FinanceVirginia"2008-01-01"this is aaa

原創文章，如果轉載，請標註作者：田文  CSDN地址：http://blog.csdn.net/tiwen818