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 aaa
20Accounting"Va,""Virginia""""2008-01-01"this is aaa
30ConsultingVirginia"2008-01-01"this is aaa
40FinanceVirginia"2008-01-01"this is 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 aaa40FinanceVirginia"2008-01-01"this is aaaMySQL的增删改查以及常用的一些操作ORA-01152错误解决方法用老的备份controlfile相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）