oracle load select,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