Oracle工具之sqlldr的使用

简介

有多种方式可以将文本文件的数据导入到数据库中，例如，利用PLSQL Developer软件进行复制粘贴，利用外部表，利用SQL*Loader等方式。至于EXCEL中的数据可以另存为csv文件（csv文件其实是逗号分隔的文本文件），然后导入到数据库中。

下面简单介绍一下SQL*Loader的使用方式。

SQL*Loader是一个Oracle工具，能够将数据从外部数据文件装载到数据库中。SQL*Loader必须包含一个控制文件，该控制文件是SQL*Loader的中枢核心，控制文件能够控制外部数据文件中的数据如何映射到Oracle的表和列。通常与SPOOL导出文本数据方法配合使用。SQL*Loader能够接收多种不同格式的数据文件。文件可以存储在磁盘或磁带上，或记录本身可以被嵌套到控制文件中。记录格式可以是定长的或变长的，定长记录是指这样的记录：每条记录具有相同的固定长度，并且每条记录中的数据域也具有相同的固定长度、数据类型和位置。

SQL*Loader的数据导入比较专业，有各种参数及选项可供选择，经常是作为数据仓库中大型数据的导入方法选择。

SQL*Loader的优点：

1、可将导入命令写入BAT文件直接批量处理

2、导入处理比较专业，提供各种参数选择

3、无需操作Oracle所在服务器

SQL*Loader也有缺点，例如，Excel文件需要另存为txt或csv格式才能导入到数据库中。

总得来说这种方法是最值得采用的，可以自动建立操作系统的批处理文件执行SQL*Loader命令，将数据导入原始接收表，并在数据库中设置触发器进行精细操作。

SQL*Loader有两种使用方法：

1、只使用一个控制文件，在这个控制文件中包含数据

2、使用一个控制文件和一个数据文件

SQL*Loader工具使用的命令为sqlldr，其常用参数的含义如下表所示：

下面给出SQL*Loader控制文件的一个示例：

其中，CHARACTERSET指定文件的编码格式，infile指定导入的文件。

接下来就是执行导入命令了，如下所示：

当要加载的数据文件比较大的时候该如何提高SQL*Loader的性能呢？可以从以下几个方面考虑：

①　ROWS的默认值为64，可以根据需要指定更合适的ROWS参数来指定每次提交记录数。

②　采用DIRECT=TRUE导入可以跳过数据库的相关逻辑，直接将数据导入到数据文件中，可以提高导入数据的性能。

③　通过指定UNRECOVERABLE选项，可以写少量的日志，而从提高数据加载的性能。不过，推荐在加载完成后立即对数据库或至少对备份。

当加载大量数据时，最好抑制日志的产生：

将表修改为NOLOGGING，可以只产生少量的Redo日志，从而提高导入效率。在CONTROL文件中的load data前边加一行：UNRECOVERABLE，此选项必须要与DIRECT共同使用。对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务：

下表给出在使用SQL*Loader的过程中，一些常用的需求实现方法：

下表给出了在使用SQL*Loader的过程中，经常会遇到的一些错误及其解决方法：

OCP

关于SQL*Loader还有很多参数本书不再详述，具体可以参考官方文档。有关如何导出数据到EXCEL中，本书也不再详述。读者若有需要可以关注作者的微信公众号来阅读。

真题1、Which two statements are true regarding the usage of the SQL*Loader utility? (Choose two.)

A、You can load data into multiple tables during the same load session.

B、You can load data from multiple files to a table during the same load session.

C、You cannot perform selective data loading based on the values available in the records.

D、You can use an export file generated by the EXPDP utility as an input data file to load the data.

答案：A、B。

题目中要求选出关于SQL*Loader工具的描述中正确的两个选项。

本题中，对于选项A，可以在一个SQL*Loader会话中导入多个表的数据，根据前面的分析，该功能可以实现。所以，选项A正确。

对于选项B，可以在一个SQL*Loader会话中将多个数据文件导入到1个表中，根据前面的分析，该功能可以实现。所以，选项B正确。

对于选项C，在导入数据的时候可以使用WHEN关键词实现选择性的导入。所以，选项C错误。

对于选项D，SQL*Loader是文本导入工具，只能导入文本数据，而exp导出的文件属于二进制文件，所以不能导入。所以，选项D错误。

所以，本题的答案为A、B。

真题2、Which statement is true about loading data using the conventional path of SQL*Loader?

A、Redo is not generated while performing conventional path loads.

B、Only PRIMARY KEY, UNIQUE KEY, and NOT NULL constraints are checked.

C、The SQL*Loader control file is a text file that contains data definition language (DDL) instructions.

D、Instead of performing transactions, SQL*Loader directly writes data blocks to the data files.

E、INSERT triggers are disabled before the conventional path load and reenabled at the end of the load.

答案：C。

题目要求选出关于SQL*Loader工具的常规路径加载说法正确的选项。

本题中，对于选项A，常规路径加载的时候会产生Redo日志，选项A错误。

对于选项B，所有的约束均有效，而不止PRIMARY KEY、UNIQUE KEY和NOT NULL这3项约束。所以，选项B错误。

对于选项C，SQL*Loader的控制文件是一个文本文件，其中包含了一些DDL结构。所以，选项C正确。

对于选项D，SQL*Loader的常规路径加载是经过内存的，而不是直接写入数据文件。所以，选项D错误。

对于选项E，SQL*Loader加载的时候，表上的相关触发器是会执行的。所以，选项E错误。

所以，本题的答案为C。

使用CONCATENATE合并多行记录

sqlldr userid=lgone/tiger control=a.ctl
其中，a.ctl为控制文件，内容：
　　LOAD DATA －－控制文件标识
　　INFILE 't.dat' －－ 要导入的文件
　　INFILE 'tt.dat' －－ 导入多个文件
　　INFILE －－ "" 代表要导入的内容就在control文件里，下面的BEGINDATA后面就是导入的内容
　　INSERT －－数据载入方式，有四种
INTO TABLE table_name －－ 指定装入的表
　　BADFILE 'c:bad.txt' －－指定坏文件地址
LOGFILE 'c:log.txt' －－log文件
DISCARD 'c:discard.txt' －－包含丢弃数据的文件
ERRORS=1000 －－指定错误的数目

可以使用sqlldr查看帮助。

以下是4种装入表的方式：
　　 APPEND 原先的表有数据，就加在后面
　　 INSERT 装载空表 如果原先的表有数据 sqlloader会停止，默认值
　　 REPLACE 原先的表有数据，原先的数据会全部删除
　　 TRUNCATE 指定的内容和replace的相同 会用truncate语句删除现存数据
　　
指定的TERMINATED可以在表的开头，也可在表的内部字段部分：
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' －－OPTIONALLY ENCLOSED BY是指字段值用什么括起来，平时我们都是用单引号，如'value'
　　 装载这种数据: 10,lg,"""lg""","lg,lg"
　　 在表中结果: 10 lg "lg" lg,lg
　　 TERMINATED BY X'09' －－ 表示字段之间通过制表符tab键（以十六进制格式 '09' 表示 ）分割，也可以把FIELDS TERMINATED BY ';'分号表示成ascii编码
　　 TERMINATED BY WRITESPACE －－ 以空白分割，装载这种数据: 10 lg lg

　　 (col_1 [interger external] TERMINATED BY ',' ,
　　 col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
　　 col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' )

当某列为空，不存在对应值：　　
　　 TRAILING NULLCOLS －－如果某个字段没有对应的值时（空格、空白、或null），被当作null列，向表中插入null
　　
不导入某列：
　　( col_1 , col_2 ,col_filler FILLER －－FILLER 关键字（不是filter）指定此列（第三列）的数值不会被装载，如: lg,lg,not，结果 lg lg)
也可以写成这样：( col_1 , col_2 ) －－数据文件中存在超过两列数据，但是只导入前两列
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
　　(DEPTNO, DNAME, LOC )
　　BEGINDATA
　　10,Sales,"""USA"""
　　20,Accounting,"Virginia,USA"
　　30,Consulting,Virginia
　　40,Finance,Virginia
　　50,"Finance","",Virginia －－loc 列将为空
　　60,"Finance",,Virginia －－loc 列将为空

跳过数据行，可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：
INTO TABLE load_positional_data
SKIP 5
　　　
当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据：
　　 (
　　 col_1 position(1:2), －－1到2，(01) 代表第一个字符
　　 col_2 position(3:10), －－3到10
　　 col_3 position(:16), －－ 这个字段的开始位置在前一字段的结束位置
　　 col_4 position(1:16),
　　 col_5 position(3:10) char(8) －－ 指定字段的类型
　　 )
　　
　　BEGINDATA －－ 对应开始的 INFILE 要导入的内容就在control文件里，注意begindata后的数值前面不能有空格
　　10,Sql,what
　　20,lg,show
　　
使用函数（包括自定义函数）、日期的某种表达方式：
　　FIELDS TERMINATED BY ','
　　(DEPTNO,
　　DNAME "upper(:dname)", －－ 使用函数
　　LOC "upper(:loc)",
　　LAST_UPDATED date 'dd/mm/yyyy', －－或'dd-month-yyyy'等，也可以使用自定义函数LAST_UPDATED "my_to_date( :last_updated )" ， my_to_date()为自定义函数
　　ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
　　)

载入有换行符的数据：
　　注意: unix 和 windows换行符不同 & /n
(......
　　COMMENTS "replace(:comments,'n',chr(10))" --replace 的使用帮助转换换行符
......
)

载入每行的行号:
　　......
　　( seqno RECNUM －－载入每行的行号
　　text Position(1:20))
　　BEGINDATA
　　fsdfasj －－自动分配一行号给载入 表t 的seqno字段, 此行行号为1
　　fasdjfasdfl －－此行行号为2
　　
合并多行记录为一行记录:
　CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

LOAD DATA
　　INFILE
　　concatenate 3 －－通过关键字concatenate 把几行的记录看成一行记录
　　INTO TABLE DEPT
......
　　(DEPTNO,
　　DNAME "upper(:dname)",
　　LOC "upper(:loc)",
　　LAST_UPDATED date 'dd/mm/yyyy'
　　)
　　BEGINDATA
　　10,Sales, －－其实这3行看成一行 10,Sales,Virginia,1/5/2000
　　Virginia,
　　1/5/2000
　　
这列子用 continueif list="," 也可以,告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行.
　　
　　LOAD DATA
　　INFILE
　　continueif this(1:1) = '-' -- 找每行的开始是否有连接字符'-',有就把下一行连接为一行:
　　 如 -10,Sales,Virginia,
　　 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
　　其中1:1 表示从第一行开始 并在第一行结束 还有continueif next, 但continueif list最理想

将数据导入多个表：
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

导入数据时修改数据：
在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

SQL*Loader 数据的提交：

一般情况下是在导入数据文件数据后提交的。也可以通过指定 ROWS= 参数来指定每次提交记录数。

提高 SQL*Loader　的性能：
\1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。
\2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。
\3) 通过指定　UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。
\4) 可以同时运行多个导入任务.

常规导入与direct导入方式的区别：
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

Oracle SQL*Loader 使用指南（转载）

如何使用 SQL*Loader 工具

我们可以用Oracle的sqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl

控制文件(loader.ctl)将加载一个外部数据文件(含分隔符)

loader.ctl如下:

load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
(empno, empname, sal, deptno)

mydata.csv 如下:

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20

下面是一个指定记录长度的示例控制文件。“*” 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。

load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE

Unloader这样的工具

Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及 format 数据来输出到一个文件：

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off

另外，也可以使用使用 UTL_FILE PL/SQL 包处理:

rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/

当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

加载可变长度或指定长度的记录

LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

下面是导入固定位置（固定长度）数据示例：

LOAD DATA
INFILE *
INTO TABLE load_positional_data
(data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

跳过数据行：

可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：

LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5 --似乎不行？需要在DOS层级下操作才有效
(data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

导入数据时修改数据：

在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：

LOAD DATA
INFILE *
INTO TABLE modified_data
(rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
(addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

将数据导入多个表：

如:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

导入选定的记录：

如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)

导入时跳过某些字段：

可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

导入多行记录：

可以使用下面两个选项之一来实现将多行数据导入为一个记录:

CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.

CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

SQL*Loader 数据的提交：

一般情况下是在导入数据文件数据后提交的。
也可以通过指定 ROWS= 参数来指定每次提交记录数。

提高 SQL*Loader 的性能：

\1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。
\2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。
\3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。
\4) 可以同时运行多个导入任务.

常规导入与direct导入方式的区别：
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数

============================================================================

控制根据数据不同插入不同的表

包括文件:日志文件;控制文件;坏记录文件;废弃记录文件(when)
控制文件:
load data
1说明输入文件 infile后根文件名.默认扩展名为’.dat’
infile=’accounts’
用单引号’ 括起文件名accounts.dat
2处理选项discardfile badfile
load data
infile ‘accounts’ discardfile mtidsc.rec badfile mthad.rec
3目标对象—–表
slqloader的用户必要有insert的权限.into table 开头
into table account_trans
when day between ‘01′ and ‘31′
into table account_nbr
when account_type between ‘aa’ and ‘zz’ 这样可以根据条件插入不同的表
4目标对象—-分区表或者某个分区
into table sale partition(east_data)…..
如果一次装在所有分区,可以用目标对象—-表的方法进行处理 into table sale….
5记录生成模式—Insert,Replace,Append
Insert–缺省模式,装在之前,table必须为空表;;
Replace –先删除所有记录,然后装在满足when条件的行;(需要delete的权限)
Append–表中原有记录保存,加入新的行.;;

处理定长记录

load data
infile ‘account.dat’
into table count_trans append
when year=’1990′
(account_nbr position(01:10) character,
day position(11:12) character,
month position(13:14) character,
transaction_code position(15:16) character,
credit_amount position(17:30) character)
into table count replace
when year>’1990′
(account_nbr position(01:10) character,
day position(11:12) character,
month position(13:14) character,
transaction_code position(15:16) character,
credit_amount position(17:30) character)

处理变长记录(从access到oracle)

分隔符:概念.在一行中将一项信息与另一项信息分离开的一个字符标记.
load data
infile ‘customer.dat’
into table aa append
(customer_id char terminated by ‘ ‘,
status char terminated by ‘ ‘,
dsc_class char enclosed by ‘ ‘,
source char terminated by whitespace)
1>单引号分割.末尾的信息项不已逗号结束,用关键字whitespace

FIELDS TERMINATED BY x’09′ （制表符）

LOAD DATA
INFILE
INTO TABLE DEPT1
REPLACE
FIELDS TERMINATED BY X’09′
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
by x’09′遇见一个制表符.它将输出一个直 也就是在制表符之间的数据
SVRMGR> host sqlldr scott/scott control=c:control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on 星期六 8月 24 21:04:26 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数1
SVRMGR> select from dept1;
DEPTNO DNAME LOC
———- ————– ————-
10 Sales
已选择 1 行。

使用filler 跳过 在导入数据文本中不想进行导入的列

demo
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001

u can see upper functions
sqlldr is a tool that can parse input variables and form its insert sql using bind values.
like normal controlfile
FIELDS TERMINATED BY ‘,’
(DEPTNO,
DNAME ,
LOC ,
LAST_UPDATED date
)
oracle’s sqlldr change it equal insert into table values(:deptno,:dname,:loc,:last_updated);
when load data sqlldr parse each record row and bind variables
once parse else execute!

so like
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’
)
sql : insert into table values(:deptno,upper(name),upper(:loc),:last_updated )
the ” LAST_UPDATED date ‘dd/mm/yyyy’ ” inside date ‘dd/mm/yyyy’ is only the variable ’s datatype not functions
u can aslo use function to_date() then it deference

SVRMGR> host sqlldr scott/scott control=control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on 星期日 8月 25 00:46:04 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数4
SVRMGR> select *from dept1;
DEPTNO DNAME LOC LAST_UPDA
———- ————– ————- ———
10 SALES VIRGINIA 01-5月 -0
20 ACCOUNTING VIRGINIA 21-6月 -9
30 CONSULTING VIRGINIA 05-1月 -0
40 FINANCE VIRGINIA 15-3月 -0
已选择4行。

TRAILING NULLCOLS

my compute just down i lose some article yet not been submit
i am so lazy not want to repeat just go on topic
about trailing nullcols
see the control file just TRAILING NULLCOls the purpose of our sqlldr is explicit. we want to load entire_line into table but it doesn’t exist in BEGINDATA segements .
so oracle provide a flag TRAILING NULLCOLS IF U DON’T USE IT ALL ROWS WILL BE DISCARD INTO DISCARD FILE IF U appoint it u shoud try what i said in no using TRAILING NULLCOLS mode

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’,
ENTIRE_LINE “:deptno||:dname||:loc||:last_updated”
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001

let’s goon
TRAILING NULLCOLS for u to appoint the last column a value “NULL” so in begindata it form a noexist column with value null
do it
SVRMGR> select *from dept1;
DEPTNO DNAME LOC LAST_UPDA ENTIRE_LINE
———- ————– ————- ——— ——————————
10 SALES VIRGINIA 01-5月 -0 10SalesVirginia1/5/2000
40 FINANCE VIRGINIA 15-3月 -0 40FinanceVirginia15/3/2001
20 ACCOUNTING VIRGINIA 21-6月 -9 20AccountingVirginia21/6/1999
30 CONSULTING VIRGINIA 05-1月 -0 30ConsultingVirginia5/1/2000
已选择4行。
SVRMGR>

sqlldr with function is powerful magic

power function CASE When…..END relation discard file
just provide a controlf file
lazy to do it
———————————————–
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “case when length(:last_updated) <= 10
then to_date(:last_updated,’dd/mm/yyyy’)
else to_date(:last_updated,’dd/mm/yyyy hh24:mi:ss’)
end”
)
BEGINDATA
10,Sales,Virginia,1/5/2000 12:03:03
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000 01:23:00
40,Finance,Virginia,15/3/2001

sometimes customer give our data.txt date format are too different so we have ablility to create own functions
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date

i copy a good fucniton from TOM
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( ‘dd-mon-yyyy’, ‘dd-month-yyyy’,
‘dd/mm/yyyy’,
‘dd/mm/yyyy hh24:mi:ss’ );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date(’01011970′,’ddmmyyyy’) + 1/24/60/60 *
p_string, ‘GMT’, ‘EST’ );
end if;
return l_return;
end;
/

use of it if other format it would in bad file we kan reload it

使用sqlldr 应该注意的问题

1 不能选择使用哪个回滚段
装载的时候 使用replace 来slqldr时候 在装载之前,它会发出delete命令 这样将产生大量的回滚. 为了实现这个操作 你可能想要指定sqlldr使用一个特定的回滚段

你必须保证任何一个回滚段有足够的长度来容纳delete 或者使用truncate 选项 由于insert并没有产生过多的回滚,因为它只写 rowid into redo 理解没有问题吧??

2truncate选项 为truncate table t reuse storage

SQLLDR默认输入流为数据类型为CHAR 长度为 255 所以当 begindata section里面的 输入流 长于255 的时候会报错的
应该是 Record N Rejected Eorr for columnn
field in data file exceed max length

sqlldr希望你输入小于255字节或更少字节的数据,而获得的比这要多,解决的方法是在控制文件中只是单纯地使用 char(N) n是可以包括你输入列地最大长度

比方说load long类型 地时候 可以 输入 char(10000) ^_^

就到这里 大家如果发现有什么bug 请贴出来 thx

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
( DEPTNO,
FILLER_1 FILLER, //see it filler
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,”Virginia,USA”
当装载数据时候 需要在输入记录中跳过各种不同的列是非常普通的

例如你想 装入 1.3.5列 跳过2.4列 可以利用filler
它可以让我们在数据流里面指定一个列 不把他放到数据库中
SVRMGR> host sqlldr scott/scott control=c:control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on 星期六 8月 24 21:16:52 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数1
SVRMGR> select *from dept1;
DEPTNO DNAME LOC
———- ————– ————-
20 Accounting Virginia,USA

Oracle sqlldr使用的几个注意事项

Oracle sqlldr是将大量数据批量导入Oracle数据表的工具，直接可以在命令符下运行。

最近同事在使用sqlldr的时候，碰到一些问题同时也做了些研究，现借题整理如下：

1. “SQL*Loader-566”错误

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 5月 5 21:53:27 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.达到提交点 - 逻辑记录计数 1 SQL*Loader-501: 无法读取文件 (data_cn_01.dat) SQL*Loader-566: 在数据文件的结尾处找到部分记录 SQL*Loader-2026: 加载因 SQL 加载程序无法继续而被终止。 数据处理完成, 按任意键结束

经过跟踪测试，发现是数据文件的问题。问题出在最后一行数据分隔符号后面没有回车，特别注意下面数据文件####后面还有一行空行。

CN200780027488.5||||CN101490018||||本发明涉及新的游离碱形式或酸加成盐形式的式(I)的大环化合物 ####

2. “数据文件的字段超出最大长度”错误

这个问题网上很容易找到答案，sqlldr对于字符类型默认长度为255，如果超过255需要指定长度，见红色下面ctl文件中的红色字体：

LOAD DATA INFILE 'data_cn_01.dat' "STR X'0D0A232323230D0A'" INTO TABLE tpis_pat_cn APPEND FIELDS TERMINATED BY '||||' TRAILING NULLCOLS ( an, pn, ab CHAR(4000), source CONSTANT 'SIPO', patent_id "seq_tpis_pat_base.NEXTVAL" )

3. “ORA-12899: 列的值太大”错误

记录 1: 被拒绝 - 表 TPIS_PAT_CN 的列 AB 出现错误。 ORA-12899: 列 "PIS"."TPIS_PAT_CN"."AB" 的值太大 (实际值: 2800, 最大值: 2000)

这个错误很明显，和INSERT等DML语句提示错误一致。其错误原因在于从文本中读取的字段值超过了数据库表字段的长度，需要用Oracle函数解决：

LOAD DATA ...TRAILING NULLCOLS ( ... ab CHAR(4000) "SUBSTRB(:ab,1,2000)", ... )

4. “数据文件的字段超出最大长度”错误

情况一：记录 1: 被拒绝 - 表 TPIS_PAT_CN 的列 AB 出现错误。 数据文件的字段超出最大长度 情况二：记录 1: 被拒绝 - 表 TPIS_PAT_CN 出现错误。 ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

以上第一种错误情况是由于数据文件中的字段值真实长度超过指定的4000长度，所以提示“数据文件的字段超出最大长度”错误。可能有人准备将“CHAR(4000)”改为“CHAR(8000)”，这时就会出现第二种错误。其原因在于：字符类型在PL-SQL中做为变量存大，最大可支持32767个字节，但在SQL中通常只能够支持到4000字节(NCHAR为2000)，因此如果声明的变量长度超出了SQL中类型长度，并且变量实际值也超出类型可接受最大值时，就会触发ORA-01461错误。

所以当数据文件中的字段值真实长度超过4000长度时，只有一个方法：将数据表字段类型改为CLOB类型或者新增加一个临时CLOB字段，sqlldr中的“CHAR(32767)”对于CLOB字段有效。导入后再通过SQL语句更新到真实字段中。

LOAD DATA ...TRAILING NULLCOLS ( ... ab_bk CHAR(32767), ... ) -- 将ab_bk更新到ab中的SQL语句UPDATE TPIS_PAT_CN SET ab=SUBSTR(ab_bk,1,1000) WHERE ab_bk IS NOT NULL AND patent_id>=337462

很遗憾，查阅了大量国外资料，sqlldr没有更好的方法处理值超过4000长度的非CLOB字段导入工作。所以只能有以下两种选择：

方案一：当然在导入之前通过程序进行预处理，但这也不是件简单的事。

方案二：忽略此字段的内容。通过在控制文件中指定“ac FILLER CHAR(32767)”即可实现忽略此字段的内容。

Oracle 的SQL*Loader可以将外部数据加载到数据库表中。下面是SQL*Loader的基本特点：
1）能装入不同数据类型文件及多个数据文件的数据
2）可装入固定格式，自由定界以及可度长格式的数据
3）可以装入二进制，压缩十进制数据
4）一次可对多个表装入数据
5）连接多个物理记录装到一个记录中
6）对一单记录分解再装入到表中
7）可以用 数对制定列生成唯一的KEY
8）可对磁盘或 磁带数据文件装入制表中
9）提供装入错误报告
10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
1.2控制文件
控制文件是用一种语言写的文本文件，这个文本文件能被SQL*Loader识别。SQL*Loader根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成：
l 全局选件，行，跳过的记录数等；
l INFILE子句指定的输入数据；
l 数据特性说明。
1.3输入文件
对于 SQL*Loader, 除控制文件外就是输入数据。SQL*Loader可从一个或多个指定的文件中读出数据。如果 数据是在控制文件中指定，就要在控制文件中写成 INFILE 格式。当数据固定的格式（长度一样）时且是在文件中得到时，要用INFILE "fix n"
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
当数据是可变格式（长度不一样）时且是在文件中得到时，要用INFILE "var n"。如：
load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,
1.4坏文件
坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。
坏文件的名字由 SQL*Loader命令的BADFILE 参数来给定。
1.5日志文件及日志信息
当SQL*Loader 开始执行后，它就自动建立 日志文件。日志文件包含有加载的总结，加载中的错误信息等。
控制文件语法
控制文件的格式如下：
OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
1）要加载的数据文件：
1．INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 则表示数据就在控制文件内。在INFILE 后可以跟几个文件。
2．STRAM 表示一次读一个字节的数据。新行代表新物理记录（逻辑记录可由几个物理记录组成）。
3．RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。
3． FIXED length 要读的记录长度为length字节，
4． VARIABLE 被读的记录中前两个字节包含的长度，length 记录可能的长度。缺伤为8k字节。
5． BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
6． DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
7． DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。
2）加载的方法：
1．APPEND 给表添加行。
2．INSERT 给空表增加行（如果表中有记录则退出）。
3．REPLACE 先清空表在加载数据。
4． RECLEN 用于两种情况，1）SQLLDR不能自动计算记录长度，2）或用户想看坏文件的完整记录时。对于后一种，Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录，则可以将整条记录写到坏文件中。
3）指定最大的记录长度：
1． CONCATENATE 允许用户设定一个整数，表示要组合逻辑记录的数目。
4）建立逻辑记录：
1．THIS 检查当前记录条件，如果为真则连接下一个记录。
2．NEXT 检查下一个记录条件。如果为真，则连接下一个记录到当前记录来。
2． Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列，以确定是否进行连接。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'
5）指定要加载的表：

1．INTO TABLE 要加的表名。
2．WHEN 和select WHERE类似。用来检查记录的情况，如：when(3-5)='SSM' and (22)='"
6）介绍并括起记录中的字段：
1． FIELDS给出记录中字段的分隔符，FIELDS格式为：
FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]
TERMINATED 读完前一个字段即开始读下一个字段直到介绍。
WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断但字符，可以用单引号括起，如X'1B'等。
OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED。
ENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ，则它们的顺序决定计算的顺序。
7）定义列：
column 是表列名。列的取值可以是：
BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。
CONSTANT 表示赋予常数。
SEQUENCE 表示序列可以从任意序号开始，格式为：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 给出列在逻辑记录中的位置。可以是绝对的，或相对前一列的值。格式为：
POSITION ( {start[end] | [+integer] } )
Start 开始位置
* 表示前字段之后立刻开始。
+ 从前列开始向后条的位置数。
8）定义数据类型：
可以定义14种数据类型：
CHAR
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC
1．字符类型数据
CHAR[ (length)] [delimiter]
length缺省为 1.
2.日期类型数据
DATE [ ( length)]['date_format' [delimiter]
使用to_date函数来限制。
3．字符格式中的十进制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常规格式的十进制数（不是二进制=> 一个位等于一个bit）。
4．压缩十进制格式数据
DECIMAL （digtial [,precision]）
5．双精度符点二进制
DOUBLE
6．普通符点二进制
FLOAT
7．字符格式符点数
FLOAT EXTERNAL [ (length) ] [delimiter]
8．双字节字符串数据
GRAPHIC [ (legth)]
9．双字节字符串数据
GRAPHIC EXTERNAL[ (legth)]
10．常规全字二进制整数
INTEGER
11．字符格式整数
INTEGER EXTERNAL
12．常规全字二进制数据
SMALLINT
13．可变长度字符串
VARCHAR
14．可变双字节字符串数据
VARGRAPHIC

2.2写控制文件CTL
1． 各数据文件的文件名;
2．各数据文件格式;
3．各数据文件里各数据记录字段的属性；
4．接受数据的ORACLE表列的属性；
5．数据定义；
6．其它
数据文件的要求：
数据类型的指定
CHAR 字符型
INTEGER EXTERNAL 整型
DECIMAL EXTERNAL 浮点型
3.1数据文件的内容
可以在OS下的一个文件；或跟在控制文件下的具体数据。数据文件可以是：
1、 二进制与字符格式：LOADER可以把二进制文件读（当成字符读）列表中
2、 固定格式：记录中的数据、数据类型、 数据长度固定。
3、 可变格式：每个记录至少有一个可变长数据字段，一个记录可以是一个连续的字符串。
数据段的分界（如姓名、年龄）如用“，”作字段的 分 ；用，"’作数据
括号等
4、 LOADER可以使用多个连续字段的物理记录组成一个逻辑记录，记录文件运行情况文件：包括以下内容：
1、 运行日期：软件版本号
2、 全部输入，输出文件名；对命令行的展示信息，补充信息，
3、 对每个装入信息报告：如表名，装入情况；对初始装入， 加截入或更新装
入的选择情况，栏信息
4、 数据错误报告：错误码；放弃记录报告
5、 每个装X报告：装入行；装入行数，可能跳过行数；可能拒绝行数；可能放
弃行数等
6、 统计概要：使用空间（包大小，长度）；读入记录数，装入记录数，跳过记录数；拒绝记录数，放弃记录数；运行时间等。

sql load的一点小总结
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要导入的文件
// INFILE 'tt.date' // 导入多个文件
// INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容
INTO TABLE table_name // 指定装入的表
BADFILE 'c:bad.txt' // 指定坏文件地址
以下是4种装入表的方式
APPEND // 原先的表有数据 就加在后面
// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
// REPLACE // 原先的表有数据 原先的数据会全部删除
// TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据
指定的TERMINATED可以在表的开头 也可在表的内部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 装载这种数据: 10,lg,"""lg""","lg,lg"
// 在表中结果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六进制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 装载这种数据: 10 lg lg
TRAILING NULLCOLS 表的字段没有对应的值时允许为空
下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载
// 如: lg,lg,not 结果 lg lg
)
// 当没声明FIELDS TERMINATED BY ',' 时
// (
// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
// 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(:16), // 这个字段的开始位置在前一字段的结束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的类型
// )
BEGINDATA // 对应开始的 INFILE 要导入的内容就在control文件里
10,Sql,what

20,lg,show

//////////// 注意begindata后的数值前面不能有空格
1 普通装载
LOAD DATA
INFILE
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia // loc 列将为空
60,"Finance",,Virginia // loc 列将为空
2 * FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
LOAD DATA
INFILE
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 指定不装载那一列
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( DEPTNO,
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 将不会被装载
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"Virginia,USA"
4 * position的列子
LOAD DATA
INFILE
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(:16), // 这个字段的开始位置在前一字段的结束位置
LOC position(:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
5 使用函数 日期的一种表达 TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应
// 的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函数
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001
6 **** 使用自定义的函数 // 解决的时间问题
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60
p_string, 'GMT', 'EST' );
end if;
return l_return;
end;
/
LOAD DATA
INFILE
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数
)
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date
7 合并多行记录为一行记录
LOAD DATA
INFILE
concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
// 这列子用 continueif list="," 也可以
告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行
LOAD DATA
INFILE
continueif this(1:1) = '-' // 找每行的开始是否有连接字符 - 有就把下一行连接为一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示从第一行开始 并在第一行结束 还有continueif next 但continueif list最理想
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA // 但是好象不能象右面的那样使用
-10,Sales,Virginia, -10,Sales,Virginia,
1/5/2000 1/5/2000
-40, 40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001
8 * 载入每行的行号
load data
infile
into table t
replace
( seqno RECNUM //载入每行的行号
text Position(1:1024))
BEGINDATA
fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1
fasdjfasdfl // 此行为 2 ...
9 载入有换行符的数据
注意: unix 和 windows 不同 n & /n
< 1 > 使用一个非换行符的字符
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'n',chr(10))" // replace 的使用帮助转换换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
< 2 > 使用fix属性
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia
// 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同
LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,"This is the Sales
Office in Virginia"
20,Accounting,Virginia,13/04/2001,"This is the Accounting
Office in Virginia"
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
Office in Virginia"
40,Finance,Virginia,987268297,"This is the Finance
Office in Virginia"
< 3 > 使用var属性
LOAD DATA
INFILE demo19.dat "var 3"
// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia
< 4 > 使用str属性
// 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)
此列中记录是以 a|rn 结束的
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
结果 7C0D0A
LOAD DATA
INFILE demo20.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo20.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia|
40,Finance,Virginia,987268297,This is the Finance

Office in Virginia|

象这样的数据 用 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.
id position(1:3) nullif id=blanks // 这里可以是blanks 或者别的表达式
// 下面是另一个列子 第一行的 1 在数据库中将成为 null
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
(n position(1:2) integer external nullif n='1',
v position(3:8)
)
BEGINDATA
1 10
20lg
------------------------------------------------------------

如果是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format

Oracle SQL*Loader 使用指南（转载）
SQL*Loader是Oracle数据库导入外部数据的一个工具.它和DB2的Load工具相似,但有更多的选择，它支持变化的加载模式，可选的加载及多表加载.
如何使用 SQL*Loader 工具
我们可以用Oracle的sqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 将加载一个外部数据文件（含分隔符). loader.ctl如下:
load data
infile 'c:datamydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
下面是一个指定记录长度的示例控制文件。"" 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。
load data
infile
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader这样的工具
Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQLPlus的select 及 format 数据来输出到一个文件：
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
另外，也可以使用使用 UTL_FILE PL/SQL 包处理:
rem Remember to update initSID.ora, utl_file_dir='c:oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
加载可变长度或指定长度的记录
如：
LOAD DATA
INFILE
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
下面是导入固定位置（固定长度）数据示例：
LOAD DATA
INFILE
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
跳过数据行：
可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：
LOAD DATA
INFILE
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
导入数据时修改数据：
在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：
LOAD DATA
INFILE
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
将数据导入多个表：
如:
LOAD DATA
INFILE
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
导入选定的记录：
如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
导入时跳过某些字段：
可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
导入多行记录：
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
SQL*Loader 数据的提交：
一般情况下是在导入数据文件数据后提交的。
也可以通过指定 ROWS= 参数来指定每次提交记录数。
提高 SQL*Loader 的性能：
\1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。
\2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。
\3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。
\4) 可以同时运行多个导入任务.
常规导入与direct导入方式的区别：
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

Oracle SQL*Loader使用案例

SQL*Loader是oracle提供的可以从多种平面文件中向数据库中加载数据的工具，它比较适合业务分析类型数据库（数据仓库）;使用sqlldr工具可以在很短的时间内向数据库中加载大量的数据，像把制作好的excel表格导入数据库，可以说非常方便，相关的数据加载和卸载工具还有外部表，IMP/EXP，数据泵等，其实,关于SQL*Loader的学习多数时间是花在了琢磨sqlldr控制文件的写法上，下面来总结一下SQL*Loader学习过程和一些实验案例。

一、sqlldr的命令帮助信息

[oracle@wjq ~]$ sqlldr

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 11:46:27 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

userid -- ORACLE username/password #Oracle**用户名和口令**

control -- control file name #**控制文件名**

​ log -- log file name #**日志文件名**

​ bad -- bad file name #**错误文件名**

data -- data file name #**数据文件名**

discard -- discard file name #**废弃文件名**

discardmax -- number of discards to allow (Default all) #**允许废弃的全部数目(默认全部)**

skip -- number of logical records to skip(Default 0) #**要跳过的逻辑记录数目(默认0)**

load -- number of logical records to load(Default all) #**要加载的逻辑记录数目(默认全部)**

errors -- number of errors to allow (Default 50) #**允许错误的数目（默认50）**

rows -- number of rows in conventional path bind array or between direct path data saves

​ (Default: Conventional path 64, Direct path all)#**常规路径绑定数组中或直接路径保存数据间的行数 (常规路径默认64,直接路径默认全部)**

bindsize -- size of conventional path bind array in bytes (Default 256000)#**常规路径绑定数据的大小(默认256000字节)**

silent -- suppress messages during run (header,feedback,errors,discards,partitions)#**运行过程中隐藏的信息(标题，反馈，错误，废弃，分区)**

direct -- use direct path (Default FALSE)#**使用直接路径(默认FALSE)**

parfile -- parameter file: name of file that contains parameter specifications#**参数文件，包括参数说明文件的名称**

parallel -- do parallel load (Default FALSE)#**执行并行加载(默认FALSE)**

file -- file to allocate extents from #**要从以下文件中分配区的文件**

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)#**不允许/允许使用无用的索引(默认FALSE)**

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)#**不维护索引，对受到影响的索引标记为失效(默认FALSE)**

commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)#**提交加载中断时已加载的行(默认FALSE)**

readsize -- size of read buffer (Default 1048576)#**读取缓冲区的大小(默认1048576字节)**

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)#**使用外部表进行加载:NOT_USED, GENERATE_ONLY, EXECUTE(默认NOT_USED)**

columnarrayrows -- number of rows for direct path column array (Default 5000)#**直接路径列数组的行数(默认5000)**

streamsize -- size of direct path stream buffer in bytes (Default 256000)#**直接路径流缓冲区的大小(默认256000)**

multithreading -- use multithreading in direct path #**在直接路径中使用多线程**

resumable -- enable or disable resumable for current session (Default FALSE)#**启用或禁用当前可恢复会话(默认FALSE)**

resumable_name -- text string to help identify resumable statement#**有助于标识可恢复语句的文本字符串**

resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)#RESUMABLE**的等待时间(默认7200秒)**

date_cache -- size (in entries) of date conversion cache (Default 1000)#**日期转换高速缓冲区大小（以条目计）(默认1000条)**

no_index_errors -- abort load on any index errors (Default FALSE)

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords. An example of the former case is 'sqlldr

scott/tiger foo'; an example of the latter is 'sqlldr control=foo

userid=scott/tiger'. One may specify parameters by position before

but not after parameters specified by keywords. For example,

'sqlldr scott/tiger control=foo logfile=log' is allowed, but

'sqlldr scott/tiger control=foo log' is not, even though the

position of the parameter 'log' is correct.

注意：

SQLLDR**的参数组合比较灵活，即可以直接写值，也可以写关键字=值。例如：sqlldr scott/tiger foo 和 sqlldr control=foo userid=scott/tiger 两种写法均有效。**

1.1简单例子

新建一个wjq_test1.ctl的控制文件，控制文件的名称和文件类型可以任意指定，接着在控制文件中写入内容

SCOTT@seiang11g>create table tb_loader as select * from bonus;

Table created.

SCOTT@seiang11g>desc tb_loader

Name Null? Type

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

SAL NUMBER

COMM NUMBER

控制文件内容如下：

[oracle@wjq SQL*Loader]$ vim wjq_test1.ctl
LOAD DATA
INFILE *
INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4

可以发现提示已经生成了4条数据，接着连接数据库查看一下内容

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 2523

发现查询到的内容就是控制文件中BEGINDATA中的数据，数据已经被成功载入。

提示：要插入的表必须在数据库中已经存在，再使用sqlldr向其中加载数据

2、SQL*Loader的体系分析

LOAD DATA

------------------------------------

INFILE *

INTO TABLE tb_loader

FIELDS TERMINATED BY ","

(ENAME,JOB,SAL)

BEGINDATA

------------------------------------

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891

WARD,SALESMAN,3128

KING,PRESIDENT,2523

1.2控制文件解析

①第一部分：

LOAD DATA是标准语法，控制文件一般都以此开头，LOAD DATA前还可指定 UNRECOVERABLE或RECOVERABLE来控制此次加载的数据是否可恢复，或者指定CONTINUE_LOAD，表示继续加载，控制文件的其他语句可以查看官方文档。

②中间部分：
INFILE:表示数据文件位置，如果值为，表示数据就在控制文件中，本例中没有单独的数据文件，对于大多数加载而言，都会将数据文件与控制文件分离。
INTO TABLE tbl_name: tbl_name即数据要加载到的目标表，该表在你执行sqlldr命令之前必须已经创建。
INTO前还有一些很有意思的参数需要说明：
INSERT:向表中插入数据，表必须为空，如果表非空的话，执行sqlldr命令时会报错，默认就是INSERT参数。
APPEND:向表中追加数据，不管表中是否有数据。
REPLACE:替换表中数据，相当于先DELETE表中全部数据,然后再INSERT。
TRUNCATE:类似REPLACE,只不过这里不是用DELETE方式删除表中数据，而是通过TRUNCATE的方式删除，然后再INSERT。
FIELDS TERMINATED BY "，"：设置数据部分字符串的分隔值，这里设置为逗 号（,）分隔，当然也可以换成其他任意可见字符，只要确定那是数据行中的分隔符就行。
(ENAME,JOB,SAL):要插入的表的列名，这里需要注意的是列名要与表中列名完全相同，列的顺序可以与表中列顺序不同，但是必须与数据部分的列一一对应?
BEGINDATA:表示以下为待加载数据，仅当INFILE指定为时有效。

③数据部分
在该案例中，是将数据部分与控制部分都放在控制文件中，通常这部分是独立存在于一个文本文件中。如果是独立的数据文件，只需要将控制文件中INFILE参数后面的*改为数据文件的文件名即可。

1.3日志文件解析

在默认情况下，sqlldr命令在执行过程中，会自动产生一个与控制文件同名的日志文件，文件扩展名为.log,日志文件中记录了加载过程中的各项统计信息，如一些初始化参数、读取的记录数、成功加载的记录数、加载用时等。
前例中，执行完sqlldr命令之后，相同路径下应该生成了一个ldr_case1.log文件，直接以“记事本”工具打开查看，应该显示如下内容：

[oracle@wjq SQL*Loader]$ cat wjq_test1.log

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File: /u01/app/oracle/SQL*Loader/wjq_test1.ctl
Data File: /u01/app/oracle/SQL*Loader/wjq_test1.ctl
Bad File: /u01/app/oracle/SQL*Loader/wjq_test1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TB_LOADER, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype

ENAME FIRST , CHARACTER
JOB NEXT , CHARACTER
SAL NEXT * , CHARACTER

Table TB_LOADER:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 49536 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Tue Oct 31 14:43:12 2017
Run ended on Tue Oct 31 14:43:12 2017

Elapsed time was: 00:00:00.03
CPU time was: 00:00:00.01

日志文件结构简单，前面都是初始化的参数，中间及后半部分才是我们应该关注的，包括记录的结构、操作的记录数（含成功的和错误的）、花费的时间等，如在这个日志文件中加粗的部分显示己经成功载入了 4条，共费时近40毫秒。

1.4错误文件解析

sqlldr命令在执行过程中，不仅会产生日志文件，如果加载数据过程中由于数据不符合规范导致加载错误，还会产生一个同名的错误文件，文件扩展名为bad(如果DBA不 显式指定的话）。该文件中记录了出错的数据。错误文件中数据的格式与数据文件完全相同，因此如果发现加载时出现错误文件，根据日志文件分析出错原因，解决后修改控制文件中infile参数为错误文件，然后重新执行sqlldr命令即可。

1.5废弃文件解析

除了日志文件和错误文件，执行sqlldr命令时还有可能生成一个同名的废弃文件,文件扩展名为.dsc，在默认情况下不会有，必须在执行sqlldr命令时显式指定废弃文件，并确实存在不符合导入逻辑的记录，里面记录了未被插入的数据
上一篇文章中，介绍了SQL*Loader的使用方法及简单的案例 Oracle SQL*Loader使用案例（一）,本篇将根据实际的使用案例来更深层次的来解读SQL*Loader

目录

SQL*Loader对不同文件及格式的处理方法

2.1 Excel文件

一般的Excel文件最大行数不超过65536行，说明数据处理量并不大，处理Excel的方式是将其另存为CSV格式文件,然后即可按照正常方式导入即可。

2.2要加载的文件不是以逗号分隔

有两种方式可以参考：
1)修改数据文件，将分隔符替换为逗号。
2)修改控制文件，将FIELDS TERMINATED BY的值修改为实际的分隔符。

2.3要加载的数据中包含分隔符

例如,要向scott.tb_loader表插入数据提供的数据格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制文件,注意下列示例代码中的粗体字符,OPTIONALLY ENCLOSED BY参数指明定界符为双引号(CSV格式文件默认定界符就是双引号,你可以根据实际情况修改OPTIONALLY的参数值),如下所示：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test2.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523sqlldr

运行如上代码，并查询结果如下：

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:56:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4

--查询结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLEAK 3904
ALLEN SALER,M 2891
WARD SALER,"S" 3128
KING PRESIDENT 2523

2.4数据文件没有分隔符

如下的数据文件专业叫做定长字符串，sqlldr中处理定长字符串也轻而易举。针对此例，我们将控制文件修改如下：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26)
)

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test3.dat
SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 252

position关键字用来指定列的开始和结束位置，如JOB position(10:18)是指从第10个字符开始截止到第18个字符作为ENAME列的列值。position的写法也很灵活，要实现上述功能还可以换成下列几种形式：

①position(+2:18):直接指定数值的方式叫作绝对偏移量，如果使用号，专业名词叫相对偏移量，表示上一个字段从哪里结束，这次就从哪里开始，相对偏移量也 可以再做运算，比如Position(*+2:15)就表示从上次结束的位置+2的地方开始。

②position(*) char(9):这种相对偏移量+类型和长度的优势在于，你只需要为第一列 指定开始位置，其他列只需要指定列长度就可以了，实际使用中比较省事。

sqlldr运行如上代码，并查询结果如下：

--sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test3.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:04:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4

--查询结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 252

2.5数据文件中的列比要导入的表中的列少

在前面几个例子中，数文件中的列比表中的列要少的环境中演示的，这说明列少不怕，关键是看控制文件中的配置。但是如果缺少的列必须赋值又怎么办呢？只需稍改下控制文件即可，直接指定COMM列，并赋初始值0(这里仍然引用ldr_case3.dat中的数据）：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "0"
)

--sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test4.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:08:50 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4

--查看结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLEAK 3904 0
ALLEN SALESMAN 2891 0
WARD SALESMAN 3128 0
KING PRESIDENT 252 0

COMM的值也可以根据其他列的值而定，修改控制文件如下

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test5.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "substr(:SAL,1,1)"
)

sqlldr执行上述代码,结果如下，很明显发现COMM的值是根据SAL的值的第1位数字获得

--sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test5.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:12:00 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4

--执行结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLEAK 3904 3
ALLEN SALESMAN 2891 2
WARD SALESMAN 3128 3
KING PRESIDENT 252 2

这里COMM列的值根据SAL列值而定，我们通过一个SQL中的函数substr取SAL值的第一列，赋予COMM列，当然这只是一个示例，DBA可以根据实际需求进行适当的 修改，通过SQL中的函数可以实现很多很有意思的转换，也许能够为你省下很大力气，而且如果现有函数无法实现，甚至可以通过PL/SQL编写自定义的函数，然后在sqlldr的 控制文件中调用，调用方式与系统自带函数方式完全相同，这样就可以根据需求对要加载 的列做审灵活的处理。

2.6数据文件中的列比要导入的表中列多

如果数据文件中的列比要导入的表中的列少，处理的时候可能麻烦些，多了反倒更简单，针对不同情况，一般有以下两种处理方式：
方式一：修改数据文件，将多余的数据删除，不过以这种方式处理，小数据量时还可行，一旦数据文件较大，几百甚至上千兆，修改数据文件耗时耗力。

方式二：使用sqlldr中控制文件FILLER来排除不需要的列
1）演示数据文件如下

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test6.dat
SMITH 7369 CLERK 1020 20
ALLEN 7499 SALESMAN 1930 30
WARD 7521 SALESMAN 1580 30
JONES 7566 MANAGER 3195 20
MARTIN 7654 SALESMAN 1580 30
BLAKE 7698 MANAGER 3180 30
CLARK 7782 MANAGER 2172 10
SCOTT 7788 ANALYST 3220 20
KING 7839 PRESIDENT 4722 10
TURNER 7844 SALESMAN 1830 30
ADAMS 7876 CLERK 1320 20
JAMES 7900 CLERK 1280 30
FORD 7902 ANALYST 3220 20
MILLER 7934 CLERK 1022 10

此时我们的需求希望我们导入第1、3、4列而跳过2、5列，创建控制文件如下

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test6.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test6.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:6),
COL1 FILLER position(10:13),
JOB position(17:25),
SAL position(28:31)
)

sqlldr的控制文件中对列定义时支持FILLER关键字，可以用来指定过滤列，在上述控制文件中，我们就使用该关键字来过滤列，相当于第10到第13列之间的数据不导入。
事实上由于此处为定长字串，我们在控制文件中指定的position参数，己经限定了读取的内容，你甚至可以删除控制文件中TCOL FILLER position (10:13)那行。

执行sqlldr命令：

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test6.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:24:36 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 14

--查询结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022

2）如果数据文件中字符串不是定长格式，而是通过分隔符来处理的，那控制文件中就需要注意，如数据文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test7.dat
SMITH,7369,CLERK,1020,20
ALLEN,7499,SALESMAN,1930,30
WARD,7521,SALESMAN,1580,30
JONES,7566,MANAGER,3195,20
MARTIN,7654,SALESMAN,1580,30
BLAKE,7698,MANAGER,3180,30
CLARK,7782,MANAGER,2172,10
SCOTT,7788,ANALYST,3220,20
KING,7839,PRESIDENT,4722,10
TURNER,7844,SALESMAN,1830,30
ADAMS,7876,CLERK,1320,20
JAMES,7900,CLERK,1280,30
FORD,7902,ANALYST,3220,20
MILLER,7934,CLERK,1022,10

此时创建控制文件时，控制文件中就必须制定FILLER，不然列中的值可能不对应，创建控制文件如下

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test7.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test7.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(
ENAME,COL1 FILLER,JOB,SAL
)

执行sqlldr命令，并查看结果

--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test7.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:32:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 14

--查看结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022

上两篇文章中，介绍了SQL*Loader的使用方法及使用案例,本篇将根据实际的使用案例来更深层次的来解读SQL*Loader
Oracle SQL*Loader使用案例（一）
Oracle SQL*Loader使用案例（二）

目录

SQL*Loader对不同文件及格式的处理方法

3.1多个数据文件，导入同一张表

通常对于逻辑比较复杂的系统可能存在这种情况，因为导出的数据来源于多个系统， 因此可能提供给DBA的也是多个数据文件。这种情况并不一定需要执行多次加载，只需要在控制文件中做适当配置即可。不过有一点非常重要，提供的数据文件中的数据存放格式必须完全相同。

创建演示表tb_manager表

--创建tb_manager表

SCOTT@seiang11g>create table tb_manager(mgrno number,mname varchar2(30),job varchar2(300),remark varchar2(4000));

Table created.

有多个数据文件，分别如下：

--数据文件1
[oracle@wjq SQL*Loader]$ vim wjq_test8_1.dat
10,SMITH,SALES MANAGER
11,ALLEN.W,TECH MANAGER
16,BLAKE,HR MANAGER
18,WJQ,TEACHER MASTER

--数据文件2
[oracle@wjq SQL*Loader]$ vim wjq_test8_2.dat
12,WARD,SERVICE MANAGER
13,TURNER,SELLS DIRECTOR
15,JAMES,HR DIRECTOR

--数据文件3
[oracle@wjq SQL*Loader]$ vim wjq_test8_3.dat
17,MILLER,PRESIDENT

创建控制文件，制定多个INFILE参数即可，控制文件如下：

--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test8.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_1.dat'
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_2.dat'
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_3.dat'
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(
MGRNO,MNAME,JOB
)

执行sqlldr命令，并查看结果

--执行sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test8.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:45:43 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 7
Commit point reached - logical record count 8

--查看结果

SCOTT@seiang11g>select * from tb_manager;

MGRNO MNAME JOB REMARK

3.2同一个数据文件，导入不同表

控制文件提供了多种逻辑判断方式，只要能把逻辑清晰地描述出来，SQL*Loader就能 按照指定的逻辑执行加载。

数据文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ cat wjq_test9.dat
BON SMITH CLEAK 3904
BON ALLEN SALER,M 2891
BON WARD SALER,"S" 3128
BON KING PRESIDENT 2523
MGR 10 SMITH SALES MANAGER
MGR 11 ALLEN.W TECH MANAGER
MGR 16 BLAKE HR MANAGER
TMP SMITH 7369 CLERK 1020 20
TMP ALLEN 7499 SALESMAN 1930 30
TMP WARD 7521 SALESMAN 1580 30
TMP JONES 7566 MANAGER 3195 20

需求是将MGR开头的记录导入到tb_manager表，以BON开头的记录导入到tb_loader表，其他记录存放到废弃文件中，创建控制文件如下:

--控制文件

[oracle@wjq SQL*Loader]$ cat wjq_test9.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test9.dat'
DISCARDFILE '/u01/app/oracle/SQL*Loader/wjq_test9.dsc'
TRUNCATE
INTO TABLE tb_loader
WHEN TAB='BON'
(
TAB FILLER POSITION(1:3),
ENAME POSITION(6:10),
JOB POSITION(+1:20),
SAL POSITION(+3:27)
)
INTO TABLE tb_manager
WHEN TAB='MGR'
(
TAB FILLER POSITION(1:3),
MGRNO POSITION(6:7),
MNAME POSITION(9:15),
JOB POSITION(*+2:30)
)

虽然这个控制文件看起来比之前的都要复杂，但只有一个新语法，即关键字，我们这里通过WHEN来实现判断，很容易理解。同时，指定了DISCARDFILE参数，以生成不满足加载条件的废弃文件，如果你有心，不妨等执行完sqlldr命令后査看wjq_test9.dsc文件和wjq_test9.log文件。
另外注意，控制文件中WHEN逻辑判断不支持OR关键字，因此如果你的判断条件有多个，则只能通过AND连接，而不能直接使用OR。

执行sqlldr命令，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test9.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:55:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 11

--查看结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLEAK 3904
ALLEN SALER,M 2891
WARD SALER,"S" 3128
KING PRESIDENT 2523

SCOTT@seiang11g>select * from tb_manager;

MGRNO MNAME JOB REMARK

这里贴一下log日志和废弃日志

--log日志

[oracle@wjq SQL*Loader]$ cat wjq_test9.log

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:55:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File: /u01/app/oracle/SQL*Loader/wjq_test9.ctl
Data File: /u01/app/oracle/SQL*Loader/wjq_test9.dat
Bad File: /u01/app/oracle/SQL*Loader/wjq_test9.bad
Discard File: /u01/app/oracle/SQL*Loader/wjq_test9.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TB_LOADER, loaded when TAB = 0X424f4e(character 'BON')
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype

TAB 1:3 3 CHARACTER
(FILLER FIELD)
ENAME 6:10 5 CHARACTER
JOB NEXT+1:20 19 CHARACTER
SAL NEXT+3:27 24 CHARACTER

Table TB_MANAGER, loaded when TAB = 0X4d4752(character 'MGR')
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype

TAB 1:3 3 CHARACTER
(FILLER FIELD)
MGRNO 6:7 2 CHARACTER
MNAME 9:15 7 CHARACTER
JOB NEXT+2:30 28 CHARACTER

Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Record 11: Discarded - failed all WHEN clauses.

Table TB_LOADER:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
7 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Table TB_MANAGER:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
8 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 7168 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 11
Total logical records rejected: 0
Total logical records discarded: 4

Run began on Tue Oct 31 15:55:48 2017
Run ended on Tue Oct 31 15:55:48 2017

Elapsed time was: 00:00:00.08
CPU time was: 00:00:00.02

--废弃日志

[oracle@wjq SQL*Loader]$ cat wjq_test9.dsc
TMP SMITH 7369 CLERK 1020 20
TMP ALLEN 7499 SALESMAN 1930 30
TMP WARD 7521 SALESMAN 1580 30
TMP JONES 7566 MANAGER 3195 20

3.3数据文件前N行不想导入

假如某天你接到一项数据加载需求，用户提供了一份100万行的数据文件，告诉你只导后50万行，恭喜，你接到了一个正常的需求！
实现的方式较多，比如修改数据文件，只保留后50万行（Windows下借助EditPlus 这类文本工具可以轻松实现，Linux/UNIX下通过TAIL等命令也可以轻易实现），如果你人很懒，不想修改文件，那正合sqlldr胃口，人家早早地就提供好了SKIP参数专用于满足此类需求。

数据文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test10.dat
#This is data of emp
ENAME MGR JOB SAL

SMITH 7902 CLERK 1020
LEN 7698 SALESMAN 1930
▽ARD 7698 SALESMAN 1580
JONES 7839 MANAGER 3195
MARTIN 7698 SALESMAN 1580
BLAKE 7839 MANAGER 3180
CLARK 7839 MANAGER 2172
SCOTT 7566 ANALYST 3220
KING PRESIDENT 4722
TURNER 7698 SALESMAN 1830
ADAMS 7788 CLERK 1320
JAMES 7698 CLERK 1280
FORD 7566 ANALYST 3220
MILLER 7782 CLERK 1022

我们只对该数据文件只从第4行开始导入，即前3行不进行导入，创建控制文件如下：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test10.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test10.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:6),
XCOL FILLER position(13:16),
JOB position(18:26),
SAL position(32:35)
)

执行sqlldr命令，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:12:11 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 14

--查看结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022

如果用户要求较高，明确指定只加载第XX到第XX行的记录，sqlldr还有一个参数叫LOAD,配置LOAD参数即可轻松实现。
这里仍使用上述数据文件，需求改为只导入第4到9行的记录，我们连控制文件都不需要修改，只需要在执行sqlldr时再加上LOAD参数即可：
执行sqlldr命令(即skip=3跳过前3行，load=6，加载接下来的6行记录)，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3 load=6

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:15:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 6

--查看结果

SCOTT@seiang11g>select * from tb_loader;

ENAME JOB SAL COMM

SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180

3.4加载的数据中有换行符

由于标准换行符也是sqlldr识别数据行结束的标志符，因此要将含换行符的数据加载到表中稍复杂一点点，而且需要根据实际情况来处理，不同情况的处理方式也不一样， 但基本思路是相同的，就是要同sqlldr指明什么时候才需要进行换行操作。

3.4.1手工指定的换行符

在手工指定换行符的情况下，数据文件中的换行符并不是标准的换行标志，而是用户自定义的一个标识字符（或多个字符组成），这种情况的处理比较简单，如数据文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_1.dat
10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.\nHe is a Tech Manager.
16,BLAKE,HR MANAGER,This is BLAKE.\nHe is a Hr Manager.

我们可以通过控制文件，在数据加载前处理remark列的数据，将用户指定的字符替换为chr(10),即标准换行符，创建控制文件如下：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_1.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_1.dat'
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(
MGRNO,
MNAME,
JOB,
REMARK "REPLACE(:remark,'\n',chr(10))"
)

这里需要注意的是，替换时必须指定"\n"而不只是"\n"，因为"\n"会被SQLLDR 识别成换行符并转换成换行标志，这样可能导致数据加载出错。而是默认转义符，指定该转义符后sqlldr就会将"\n"识别成普通字符了。

执行sqlldr命令，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:21:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 3

--查看结果

SCOTT@seiang11g>select * from tb_manager;

MGRNO MNAME JOB REMARK

​ 11 ALLEN.W TECH MANAGER This is ALLEN.W.
​ He is a Tech Manager.

​ 16 BLAKE HR MANAGER This is BLAKE.
​ He is a Hr Manager.

3.4.2指定FIX属性处理换行符

数据文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ cat wjq_test11_2.dat
10 SMITH SALES MANAGER THIS IS SMITH.
HE IS A SALES MANAGER.
11 ALLEN.W TECH MANAGER THIS IS ALLEN.W
HE IS A TECH MANAGER.
16 BLAKE HR MANAGER THIS IS BLAKE.
HE IS A HR MANAGE.

（特别注意：因为使用的FIX固定长度，所以一定要注意每一行数据的长度，不够的使用空格来代替，否则在加载的使用会报错，报错信息如下所示：

SQL*Loader-501: Unable to read file (wjq_test11_2.dat)

SQL*Loader-566: partial record found at end of datafile

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

报错的原因并不是说读不到要加载的数据文件，相反，虽然出现了报错的信息，也有可能部分数据已经导入进去了，报错因为你的存在数据并不是你所FIX的长度，所以出现报错，故在使用FIX的时候一定要注意这一点）

创建控制文件如下：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_2.dat' "FIX 65"
TRUNCATE INTO TABLE tb_manager
(
MGRNO POSITION(1:2),
MNAME POSITION(+1:10),
JOB POSITION(+1:24),
REMARK POSITION(*+1:63)
)

FIX是INFILE关键字的一个属性，INFILE不仅有FIX属性，还有VAR和STR等属性

执行sqlldr命令，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 10:37:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 3

--查看结果

SCOTT@seiang11g>select * from tb_manager;

MGRNO MNAME JOB REMARK

​ 11 ALLEN.W TECH MANAGER THIS IS ALLEN.W.
​ HE IS A TECH MANAGER.

​ 16 BLAKE HR MANAGER THIS IS BLAKE.
​ HE IS A HR MANAGER.

这种方式其实就是在加载数据文件之前，先通过FIX属性指定每行的长度（这里每行65个字符，包括换行符在内,该例子上不够字符的用空格来代替了，注意上面数据文件中的结尾的空格），到了指定长度就换行，不管中间有没有换行符，因此仅能用于定长字符串的数据文件，因为只有字符串定长，你才知道应该在INFILE处指定什么值。

针对上述的数据文件也可以通过使用CONCATENATE属性处理换行符来处理换行符，控制文件内容如下所示：
[oracle@wjq SQL*Loader]$ vim wjq_test11_2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_2.dat'
CONCATENATE 2 INTO TABLE tb_manager
(
MGRNO POSITION(1:2),
MNAME POSITION(*+1:10),
JOB POSITION(*+1:24),
REMARK POSITION(*+1:63) "REPLACE(upper(:REMARK),'HE',chr(10)||'HE')"
)

3.4.3指定VAR属性处理换行符（行头部标识换行）

前面提到INFILE关键字还支持VAR属性，语法格式为INFILE filename "var n", n的值不能超过40,否则会报错，如果不指定n则默认值为5。
本小节就演示通过这种方式处理换行符。总的来说，这确实是相当有才的一种方式， 首先通过VAR属性在每行开头指定一个固定长度的字符串，该字符串指明该行的长度，通过这种方式支持变长字符串。

数据文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_3.dat
06110,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.
06311,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.
05516,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.

数据文件中每行开头的061，063，055分别表示该行取61，63，55个字符

创建控制文件如下：

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_3.dat' "var 3"
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

执行sqlldr命令，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_3.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 09:51:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 3

--查看结果

SCOTT@seiang11g>select * from tb_manager;

MGRNO MNAME JOB REMARK

​ 11 ALLEN.W TECH MANAGER This is ALLEN.W.
​ He is a Tech Manager.

​ 16 BLAKE HR MANAGER This is BLAKE.
​ He is a Hr Manager.

①这种方式可以视为第2种方式的变种，该方法不在INFILE处指定行长度，而改为在每行的行首位置标注该行长度，以支持变长类型的数据格式。

②综合来看，这两种方式实用价值都比较低，先不说用户是否真有如此耐心来生成这样格式的数据文件，就算用户按照这种格式提供，要知道windows、Linux/UNIX下因操作系统自身原因,对换行符识别也不同，在Windows环境下换行标志由"回车chr(13)+换行chr(10)" 两个字节组成，而Linux/UNIX环境则是"换行chr(10)"一个字节，也就是说指定了长度，操作系统变了以后，长度有可能也得跟着变，这就造成用户提供的数据文件通用性较差。

③有没有一种更简单的方式呢？事实上确实没有太简单的方式，在前面的内容中就提到， 你需要为sqlldr指明什么时候应该换行，因此对数据文件进行预处理是必然的，不过相对来讲，下面将要介绍的方式更易于操作，也更可行一些。

3.4.4指定STR属性处理换行符（行尾部标识换行）

这种方式也需要先对数据文件做处理，在记录换行处打上一个标记，比如"丨"（当然可以定义为其他字符，但注意不要与要导入的数据有冲突），这样sqlldr见到该字符就知道换行的时候到了。
由于单个字符出现在导入数据中的机率较高，因此建议换行标志尽可能由多个字符组成，通常习惯于定义“字符+换行符”作为新的换行标记，这里我们也采用这种方式。

数据文件和控制文件如下：

--数据文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_4.dat
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.|

--控制文件

[oracle@wjq SQL*Loader]$ vim wjq_test11_4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_4.dat' "str '|\n'"
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

执行sqlldr命令，并查看结果

--执行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_4.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 09:45:28 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 3

--查看结果

SCOTT@seiang11g>select * from tb_manager;

MGRNO MNAME JOB REMARK

​ 11 ALLEN.W TECH MANAGER This is ALLEN.W.
​ He is a Tech Manager.

​ 16 BLAKE HR MANAGER This is BLAKE.
​ He is a Hr Manager.

STR属性中支持两种字符指定方式：
'char_string':普通字符，即标准的可见字符，不过也有些不可见字符可以通过下列反斜杠标识的方式在字符串模式中指定：
\n:表示换行。
\t:表示行制表符(tab)。
\f:表示换页。
\v:表示列制表符。
\r:表示回车。
说到这里，又不得不再次提及Windows和Linux/UNIX对换行符识别的差异，Linux/UNIX下指定"\n"即可，Windows下需要指定"\r\n"才表示一个完整的换行符。
X'hex_string':二进制字符。对于一些不可见字符，如像回车换行这类字符，可以将其转换成十六进制，然后再通过str X'hex_str'方式指定。

比如上述控制文件中的功能如果用二进制字符表示，形式如下:

INFILE ldr_case11_4.dat "str X'7C0A'"

要査看指定字符的十六进制编码，可以通过UTL_RAW.CAST_TO_RAW生成，例如:

SCOTT@seiang11g>select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
--------------------------------------------------------------------------------
7C0A

这种方式相当于自定义一个换行标志，标准换行符不再拥有特殊的意义，只是作为要加载数据的一部分。较第一种方式而言最大的优势是，数据文件相对更容易处理，只需要在生成数据文件时，最后一列附加一个指定字符即可，对于稍有SQL基础的人来说，这都是小case。