sqlldr所有命令及用法
SQL*Loader使用说明
1/28 Oracle之sqlldr使用
目录 1. sqlldr简介 .............................................................................................................. 3 2. sqlldr结构 .............................................................................................................. 4
2.1. sqlldr结构图 ............................................................................................................. 4
2.2. sqlldr功能描述 ......................................................................................................... 5
2.3. 命令结构 ................................................................................................................... 6
2.3.1. 程序参数 ........................................................................................................... 6
2.4. 控制文件 ................................................................................................................... 8
2.4.1. 控制文件语法 ................................................................................................... 8
2.4.2. 控制文件结构说明 ........................................................................................... 9
2.5. 数据文件 ................................................................................................................. 11
2.5.1. 数据文件要求 ................................................................................................. 11
2.5.2. 数据文件内容 ................................................................................................. 11
2.6. 日志文件 ................................................................................................................. 12
2.7. BAD文件 ............................................................................................................... 12
2.8. DISCARD文件 ...................................................................................................... 12 3. sqlldr 装载 ........................................................................................................... 13
3.1. 一般装载 ................................................................................................................. 14
3.1.1. 控制文件 ......................................................................................................... 14
3.1.2. 数据文件 ......................................................................................................... 14
3.1.3. 装载结果 ......................................................................................................... 14
3.2. 指定字段长度装载 ................................................................................................. 15
3.2.1. 控制文件 ...............................................................
.......................................... 15
3.2.2. 数据文件 ......................................................................................................... 15
3.2.3. 装载结果 ......................................................................................................... 15
3.3. 指定类型装载 ......................................................................................................... 16
3.3.1. 控制文件 ......................................................................................................... 16
3.3.2. 数据文件 ......................................................................................................... 16
3.3.3. 装载结果 ......................................................................................................... 17
3.4. 跳过装载 ................................................................................. 错误未定义书签。
3.5. 多表装载 ................................................................................................................. 17
3.5.1. 控制文件 ......................................................................................................... 17
3.5.2. 数据文件 ......................................................................................................... 18
3.5.3. 装载结果 ......................................................................................................... 18
3.6. 直接路径装载 ......................................................................................................... 19
3.6.1. 控制文件 ......................................................................................................... 19
3.6.2. 数据文件 ......................................................................................................... 20 SQL*Loader使用说明
2/28 3.6.3. 装载结果 ......................................................................................................... 20
3.7. 使用函数装载 ......................................................................................................... 20
3.7.1. 控制文件 ......................................................................................................... 20
3.7.2. 装载结果 ......................................................................................................... 21
3.8. 多文件多表装载 ..................................................................................................... 21
3.8.1. 控制文件 ......................................................................................................... 22
3.8.2. 数据文件 ......................................................................................................... 22
3.8.3. 装载结果 ...................................................................................................
...... 23
3.9. 默认值装载 ............................................................................................................. 23
3.9.1. 控制文件 ......................................................................................................... 23
3.9.2. 数据文件 ......................................................................................................... 24
3.9.3. 装载结果 ......................................................................................................... 24
3.10. LOB数据装载 ........................................................................................................ 24
3.10.1. 控制文件 ......................................................................................................... 25
3.10.2. 数据文件 ......................................................................................................... 25
3.10.3. 装载结果 ......................................................................................................... 26
3.11. 外部表装载 ............................................................................................................. 26
3.11.1. 装载结果 ......................................................................................................... 27
SQL*Loader使用说明
3/28 1. sqlldr简介 当我们在使用Oracle数据库的时候经常需要对数据进行装载入库而这些数据很多时候
不一定是来自Oracle数据库本身可能只是一些文本数据。Oracle在对数据入库提供了比
较多工具如常见的有
imp只适合使用Oracle的exp工具导出来的文件
impdp只适合由Oracle的expdp工具导出来的文件
外部表适合文本数据的装载不过装载的数据必须在数据库服务器的本地机器上不能实
现远程的装载当然如果采用数据同步的流也可以实现远程装载不过不现实。
sqlldrSQL*LOADER适合文本数据的装载使用相对比较简单而且相对比较灵活
因此在对文本装载的时候多数采用这种装载方法。
本文就主要介绍SQL*LOADER的文本装载方式。在不同的数据库中基本上每个数据库都
支持文本数据的装载因此通过熟悉sqlldr的数据的装载可以实现不同数据库的数据与
Oracle数据库之间进行数据交换。
注本文所有测试均在AIX系统上测试在讲述装载时也主要基于linux/Unix系统
SQL*Loader版本: Release 10.2.0.2.0 SQL*Loader使用说明
4/28 2. sqlldr结构 sqlldr主要由sqlldr本身命令参数装载控制文件装载bad文件装载log文件装载数据
文件discard文件这几部分组成。通过sqlldr的装载可以实现对数据做如下几部分的操作
1INSERT 在表原有的基础上实现插入
2REP
LACE 如果表存在数据中需要的记录则进行替换
3TRUNCATE 删除原有的表进行新数据的插入 2.1. sqlldr结构图
Sqlldr基本组成图 SQL*Loader使用说明
5/28
Sqlldr直接路径与普通装载区别图 2.2. sqlldr功能描述 1. 可以通过网络客户端连接到服务器实现远程数据装载。
2. 可以在同一个session装载数据入多个表
3. 可以在同一个session装载多个数据文件
4. 可以指定数据字符集从而实现不同编码的转化
5. 可以实现对数据选择性的转载
6. 可以通过sql函数来操作数据实现数据操作性的装载
7. 可以实现唯一序列转载通过制定列
8. 装载数据可以从磁盘磁带或者命名管道来获取
9. 完整的错误报告描述可以方便找到出错原因
10. 可以装载复杂的对象关系数据如XML格式数据
11. 提供多种装载方式insertapenddirect SQL*Loader使用说明
6/28 2.3. 命令结构 Sqlldr命令使用比较简单主要就是程序加参数就可以实现数据的装载。Sqlldr通过返回
值来判断是否装载成功。
在UNIX/LINUX系统下sqlldr返回值如下
返回值宏 返回值
EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL 3
在Windows NT系统下,sqlldr返回值如下
返回值宏 返回值
EX_SUCC 0
EX_WARN 2
EX_FAIL 3
EX_FTL 4
各宏值对应的说明
值宏 说明
EX_SUCC 数据在制定控制范围内成功装载
EX_WARN 数据装载存在警告可能是reject记录数到
达等原因通常出现警告需要查对应的装载
日志文件找出警告原因查看是否需要重
新装载数据
EX_FAIL 语法错误导致sqlldr无法装载
EX_FTL 在装载过程中遭遇到致命错误可以通过log
可以确定发生此种错误的原因
2.3.1. 程序参数 Sqlldr命令参数组要有以下
userid – 装载使用到的帐号信息包括数据库信息格式如userid=user/passwd@dbname
control – 指定装载使用到的控制文件
log - 指定装载使用到的日志文件
bad -指定装载使用到的BAD文件
data -指定装载使用到的数据文件,如果在控制文件中也制定infile文件那么将优先使用命
令行的data文件control文件中的第一个infile文件将忽略如果control文件中只有
一个data文件那么sqlldr将给出一个警告。
discard – discard 文件不符合转载条件的记录文件
discardmax – 允许discards 数目默认是全部 SQL*Loader使用说明
7/28 skip – 跳过记录数
load – 指定装载数据的逻辑数目默认是说有记录。
注逻辑记录和物理记录的区别逻辑记录是指导入数据库的记录物理记录数指文
件中存在的记录。一般
情况下逻辑记录跟物理记录相等但是有些情况下如多条
物理记录联合装载这样可能出现不相等的情况。
errors – 允许出错数目如果达到出错数就退出装载默认50
rows – 装载提交数目普通装载默认64在直接路径如果没有指定默认为全部记录通过
这个特性可以用来控制sqlldr的事务一致性。
bindsize – 指定普通转载绑定数组的值默认256000该值之适用于普通装载而不适用于
直接路径装载因为直接路径装载使用的是直接路径API而不是普通装载的
insert。设置大的bindsize可以提供每次insert的记录数目。
silent – 提示在装载时候终端上显示的内容有以下几种可以选择。
Header禁止sqlldr装载标题提示信息但在log文件依然出现。
Feedback禁止"commit point reached"提示信息。
Errors禁止数据错误信息写在log日志文件但是rejected records依然会写入。
Discards禁止在log日志里面的记录信息写入discard文件。
Partitions在采用直接路径装载的时候取消写每个分区统计信息入日志文件。
ALL包含以上所有的值
direct – 使用直接路径装载
parfile – 参数文件指定。
parallel – 并行装载 默认为FALSE只能用于direct装载方式。
file – 指定装载过程中使用的数据库中的数据文件该选项只适用于并行装载通过该选项
可以提高磁盘的读写速度。不同的装载可以存放不同的数据库文件。
skip_unusable_indexes –该值跟数据库配置文件中的skip_unusable_indexes类似在装载过程
中遇到无效索引的时候不停下来继续进行装载。
skip_index_maintenance –停止索引的维护在直接路径转载的时候。使用该选项将插入一个
没用的索引键值代替把索引制成无效。这样不会因为前面的转载使
索引无效而影响后面的装载。默认为FALSE。
commit_discontinued – 提交已经装载的行数如果装载失败的时候。默认为FALSE。
readsize – 该参数用于设置读取数据的大小如果读控制文件中的值就使用64K大小不变的
值readsize默认值为1048576最大可设置的值为20M在普通装载的情况下
bindsize的大小受限制与readsise的设置如果readsize的值小于bindsize的值
sqlldr将会自动让readsize等于bindsize的值。
external_table –使用sqlldr来使用外部表的功能; 本选项提供三个可选项
NOT_USED默认为不使用外部表装载功能
GENERATE_ONLY通过该选项可以生成需要通过外部表装载的所有步骤
EXECUTE直接使用外部表功能装载
具体
说明参见外部表装载
columnarrayrows –该选项适用于direct路径装载,默认为5000。该选项sqlldr将不做技术如
果使用该选项则需要用户设置正确或者接受默认的值。
streamsize –该选项为直接路径装载所用需要跟columnarrayrows配对使用。
multithreading - 在多cpu的系统中默认为TRUE在单cpu的系统中默认为FALSE。使用多
线程装载在direct path的情况下。推荐使用FALSE因为在对于大数
据量的转载中可能出现中途挂起的情况。 SQL*Loader使用说明
8/28 resumable -设定可恢复的空间分配默认为FALSE。通过该选项可以用于转载过程中空间不足
引起的错误。 resumable_name –该值定义空间使用的语句。默认
'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'
resumable_timeout – 空间处理时间默认7200秒。如果在这个时间内没有处理好空间那
么sqlldr将因为空间不足而错误退出。该选项需要首先设resumable
为TRUE。
date_cache –用于数据转化的临时空间 默认是1000。本选项只能用于direct路径装载。在需
要数据转化如datetimestamp类新装载上可以使用当然如果转换的数据唯
一性很高可能就很难有作用对于很多重复的需要转换的比较有用可以大
大提高转化的时间。
sqlldr的命令行参数可以直接放在命令行也可以放在控制文件也可以直接放在参数文件
PARFILE (parameter file)里面。
注意在重复指定的参数里面命令行的参数凌驾于任何写在参数文件和控制参数文件之上
的参数。 2.4. 控制文件 控制文件是用一种规定语言写的文本文件这个文本文件能被sqlldr读取。sqlldr根据控制
文件可以找到需要加载的控制参数及其数据。并且根据控制参数分析和解释这些数据从而
实现数据的装载。
控制文件由三个部分组成
1,全局选项即可以用于命令行输入的sqlldr选项,rows,skip 等
2,INFILE子句指定的输入数据
3,数据特性说明。
以下将详细讨论控制文件的结构。 2.4.1. 控制文件语法 控制文件编写的语法格式如下
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 ] SQL*Loader使用说明
9/28 [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]
2.4.2. 控制文件结构说明 一要加载的数据文件
1INFILE 和INDDN是同义词它们后面都是要加载的数据文件。如果用 * 则表示数据
就在控制文件内。在INFILE 后可以跟几个文件详细可以参考多文件装载的例子。
2STRAM 表示一次读一个字节的数据。
3RECORD 使用宿主操作系统文件及记录管理系统。
3FIXED length 要读的记录长度为length字节
4VARIABLE 被读的记录中前两个字节包含的长度length 记录可能的长度。默认为8k
字节。
5BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
6DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
7DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。
二加载方法
1APPEND 给表添加行。
2INSERT 给空表增加行如果表中有记录则退出。
3REPLACEtruncate 先清空表在加载数据。
4RECLEN 用于两种情况: SQL*Loader使用说明
10/28 1SQLLDR不能自动计算记录长度
2用户想看坏文件的完整记录时。
对于后一种Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录则可
以将整条记录写到坏文件中。
三指定最大的记录长度
CONCATENATE 允许用户设定一个整数表示要组合逻辑记录的数目
四建立逻辑记录
1THIS 检查当前记录条件如果为真则连接下一个记录。
2NEXT 检查下一个记录条件。如果为真则连接下一个记录到当前记录来。
3Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列以确定是否进行连接。
如continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'
五指定要加载的表
1 INTO TABLE 要加的表名。
2 WHEN 和select WHERE类似。用来检查记录的情况如when(3-5)='SSM' and (22)='*"
六介绍并括起记录中的字段
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 则它们
的顺序决定计算的顺序。
七定义列
column 是表列名。列的取值可以是
BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。
CONSTANT 表示赋予常数。
SEQUENCE 表示序列可以从任意序号开始格式为
SEQUENCE { integer | MAX |COUNT} [,increment]
POSITION 给出列在逻辑记录中的位置。可以是绝对的或相对前一列的值。格式为
POSITION ( {start[end] | * [+integer] } )
Start 开始位置
* 表示前字段之后立刻开始。
+ 从前列开始向后条的位置数。
八定义数据类型
1字符类型数据
CHAR [ (length)] [delimiter]
length缺省为 1.
2.日期类型数据
DATE [ ( length)]['date_format' [delimiter] SQL*Loader使用说明
11/28 使用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.5. 数据文件
2.5.1. 数据文件要求 数据类型的指定
CHAR 字符型
INTEGER EXTERNAL 整型
DECIMAL EXTERNAL 浮点型
2.5.2. 数据文件内容 可以在OS下的一个文件或跟在控制文件下的具体数据。数据文件可以是
1、 二进制与字符格式LOADER可以把二进制文件读当成字符读列表中
2、 固定格式记录中的数据、数据类型、 数据长度固定。 SQL*Loader使用说明
12/28 3、 可变格式每个记录至少有一个可变长数据字段一个记录可以是一个连续的字符串。
数据段的分界如姓名、年龄如用“”作字段的 分 用"’作数据括号等
4、 LOADER可以使用多个连续字段的物理记录组成一个逻辑记录。 2.6. 日志文件 当SQL*Loader 开始执行后它就自动建立 日志文件。日志文件包含有加载的总结加载中
的错误信息等。通过silent的参数可以控制日志文件的显示的详细程度。如果需要通过
程序
来读取日志文件来判断转载是否符合要求需要注意以下两点
1 单条记录的时候日志文件输出的时候会跟多条记录装载有所不同。
2 在出现异常情况的时候日志文件可能会没有写完全。
2.7. BAD文件 坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。
坏文件的名字由 SQL*Loader命令的BADFILE 参数来给定。如果装载的时候没有指定BAD
文件sqlldr将自动建立.bad文件。通过.bad文件可以检查分析数据不符合装载要求的原因。 2.8. DISCARD文件 该文件为可选文件主要存放不符合装载规则的废弃记录在多数情况下可以使用bad文件
来代替该文件。如果没有指定discard参数或者discardmax参数sqlldr将不会自动建立.dsc
文件。 SQL*Loader使用说明
13/28 3. sqlldr 装载 以下部分主要针对各种装载进行实际的测试。各种表的结构如下
CREATE TABLE dept (
deptno VARCHAR2(2),
dname VARCHAR2(20),
loc VARCHAR2(20));
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(10),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(7,2),
deptno NUMBER(2),
projno NUMBER(4),
loadseq NUMBER(3));
CREATE TABLE proj (
emp NUMBER(4),
projno NUMBER(3));
CREATE TABLE funcdemo (
last_name VARCHAR2(20),
first_name VARCHAR2(20));
CREATE TABLE decodemo (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));
CREATE TABLE denver_prj (
projno VARCHAR2(3),
empno NUMBER(5),
projhrs NUMBER(2));
CREATE TABLE orlando_prj (
projno VARCHAR2(3),
empno NUMBER(5),
projhrs NUMBER(2)); SQL*Loader使用说明
14/28
CREATE TABLE misc_prj (
projno VARCHAR2(3),
empno NUMBER(5),
projhrs NUMBER(2));
CREATE TABLE po_tab OF XMLTYPE;
注以下所有例子都是文件与数据文件分开如果要使用控制文件与数据文件一起只需要
在INFILE 后面加*即可。然后在BEGINDATA后面加入需要装载的数据。 3.1. 一般装载 此种装载方法是最常用到的装载方法也是比较简单的装载。 3.1.1. 控制文件 LOAD DATA INFILE '/app/etl/_xqy/sqlldr/sql.dat'
INTO TABLE dept
FIELDS TERMINATEd BY '&' TRAILING NULLCOLS
(deptno,
dname,
loc
) 3.1.2. 数据文件 120&RESEARCH&SARATOGA
10&ACCOUNTING&CLOVELAND
11&ART&SALEM
AA&FINANCE&BOSTON
21&SALES&ROCHESTER
42&INT'L&SAN FRANCISCO 3.1.3. 装载结果 通过查看可以看见数据装载结果如下 SQL*Loader使用说明
15/28
由上面可以看出有一条记录没有装载进来因为附近不符合规则长度超出了表的定义长
度。 3.2. 指定字段长度装载
3.2.1. 控制文件 LOAD DATA INFILE '/app/etl/_xqy
/sqlldr/sql.dat'
INTO TABLE emp
FIELDS TERMINATEd BY '&' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL
) 3.2.2. 数据文件 7781 CLARK MANAGER 7838 2572.50 10
7839XKING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.01 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20 3.2.3. 装载结果 SQL*Loader使用说明
16/28
注该装载使用POSITION关键字来定义字段的范围。从结果可以看出7839XKING中的X
没有装载进来。如果采用该方法装载需要明确知道数据的长度不然容易出现储物。 3.3. 指定类型装载
3.3.1. 控制文件 LOAD DATA INFILE '/app/etl/_xqy/sqlldr/sql.dat'
APPEND INTO TABLE emp
FIELDS TERMINATEd BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
empno,
ename,
job,
mgr,
hiredate DATE "DD-Month-YYYY",
sal,
comm,
deptno CHAR TERMINATED BY ':',
projno,
loadseq SEQUENCE(MAX,1)
) 3.3.2. 数据文件 9782,"Clark",Manager",7839, 09-June-2000, 2572.50,, 10:101
9839,"King","President", , 17-November-1999, 5500.00,,10:102
9934,"Miller","Clerk",7782, 23-January-2001, 920.00,, 10:102
9566,"Jones","Manager",7839, 02-April-2001, 3123.75,, 20:101
9499,"Allen","Salesman",7698, 20-February-2001, 1600.00, 300.00, 30:103
9654,"Martin","Salesman",7698, 28-September-2000, 1312.50, 1400.00, 30:103
9658, "Chan", "Analyst", 7566, 03-May-1999, 3450,, 20:101 SQL*Loader使用说明
17/28 3.3.3. 装载结果
注
该装载使用了日期类型的装载还是用了序列SEQUENCE装载的形式。在deptno字段使用
了TERMINATED BY ':'的方式来截断后面的字符。 3.4. 多表装载
3.4.1. 控制文件 LOAD DATA
INFILE '/app/etl/_xqy/sqlldr/sql.dat'
BADFILE '/app/etl/_xqy/sqlldr/sql.bad'
DISCARDFILE '/app/etl/_xqy/sqlldr/sql.dsc'
REPLACE
INTO TABLE emp (
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 != ' ' (
emp POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(25:27) INTEGER EXTERNAL)
INTO TABLE proj
WHEN projno != ' ' (
emp POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(29:31) INTEGER EXTERNAL)
INTO TABLE proj
WHEN projno != ' ' (
emp POSITION(1:4) INTEGER EXTERNAL,
projno P
OSITION(33:35) INTEGER EXTERNAL) SQL*Loader使用说明
18/28 3.4.2. 数据文件 1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 2A6 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200 3.4.3. 装载结果
表一图 SQL*Loader使用说明
19/28
表二图
注该表通过 3.5. 直接路径装载 sqlldr userid=etl/etl control=sql.ctl log=sql.log direct=true 3.5.1. 控制文件 LOAD DATA
INFILE '/app/etl/_xqy/sqlldr/sql.dat'
INSERT
INTO TABLE emp
-- SORTED INDEXES (emp_empno)
(
empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR, SQL*Loader使用说明
20/28 mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS
) 3.5.2. 数据文件 7781 CLARK MANAGER 7838 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20 3.5.3. 装载结果
3.6. 使用函数装载
3.6.1. 控制文件 控制文件一
LOAD DATA
INFILE *
INSERT
INTO TABLE funcdemo
(
LAST_NAME position(1:7) CHAR "UPPER(:LAST_NAME)",
FIRST_NAME position(8:15) CHAR "LOWER(:FIRST_NAME)" SQL*Loader使用说明
21/28 )
BEGINDATA
Locke Phil
Cline Jack
控制文件二
LOAD DATA
INFILE *
INSERT
INTO TABLE decodemo
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
fld1,
fld2 "DECODE(:fld1, 'hello', 'goodbye', :fld1)"
)
BEGINDATA
hello,""
goodbye,""
this is a test,""
hello,"" 3.6.2. 装载结果
3.7. 多文件多表装载 上面的例子中实现了多表的装载除了一个文件可能需要导入到多个表里面以后还有就是SQL*Loader使用说明
22/28 有些时候可能需要把几个文件一次转载到一个表或者多个表里面。如果每次都要写控制文
件一个个文件来装载的话大大减低了效率。因此sqlldr提供了一次装载多个文件的功能。 3.7.1. 控制文件 LOAD DATA
INFILE '/app/etl/_xqy/sqlldr/sqla.dat' badfile ‘/app/etl/_xqy/sqlldr/sqla.bad’
INFILE '/app/etl/_xqy/sqlldr/sqlb.dat' ba
dfile ‘/app/etl/_xqy/sqlldr/sqlb.bad’
APPEND
INTO TABLE denver_prj
WHEN projno = '101'
(projno position(1:3) CHAR,
empno position(4:8) INTEGER EXTERNAL,
projhrs position(9:10) INTEGER EXTERNAL)
INTO TABLE orlando_prj
WHEN projno = '202'
(projno position(1:3) CHAR,
empno position(4:8) INTEGER EXTERNAL,
projhrs position(9:10) INTEGER EXTERNAL)
INTO TABLE misc_prj
WHEN projno != '101' AND projno != '202'
(projno position(1:3) CHAR,
empno position(4:8) INTEGER EXTERNAL,
projhrs position(9:10) INTEGER EXTERNAL) 3.7.2. 数据文件 Sqla.dat:
1011234515
1015432140
1012345620
3032345610
Sqlb.dat:
2021234515
2022345610
4041234510 SQL*Loader使用说明
23/28 3.7.3. 装载结果
3.8. 默认值装载 在使用装载的过程中可能需要一些列是自定义一个值也就是默认的一个值这个值是没
有出现在数据文件里面的。在sqlldr的装载中提过了这个功能用户可以自定义来装载需
要的默认值。CONSTANT为sqlldr关键字告诉sqlldr该列为自定义值列。 3.8.1. 控制文件 OPTIONS (ERRORS=100, SILENT=(FEEDBACK))
LOAD DATA
INFILE '/app/etl/_xqy/sqlldr/sql.dat'
REPLACE
INTO TABLE dept
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
deptno CONSTANT "XX", SQL*Loader使用说明
24/28 dname,
loc
)
注本控制文件头加入了OPTIONS (ERRORS=100, SILENT=(FEEDBACK))选项这样不需
要在命令行中输入如果了上面的选项还有很多选项可以放在上面具体有哪些选项以及
对应的意思可以参考前面的sqlldr参数说明部分。 3.8.2. 数据文件 RESEARCH,"SARATOGA"
"ACCOUNTING",CLOVELAND
"ART",SALEM
FINANCE,"BOSTON"
"SALES",ROCHESTER
"INT'L","SAN FRANCISCO" 3.8.3. 装载结果
注从结果可以看出deptno列已经采用了自定义的列。另外除了CONSTANT关键字以外
还可以采用RECNUM每行的行号只是本文件中的行号SYSDATE 来自定义列。控制文
件写法为列名 RECNUM 形式。 3.9. LOB数据装载 在oracle中对于大数据的操作LOB ,XMLTYPE的操作会比较麻烦。但是通过sqlldr还是
可以装载不同类型的大数据。以下就针对BLOB的类型来测试如果是其他类型基本可
以使用相同的办法来处理。在XMLTYPE中其实就是CLOB的类型一样可以采用LOB
装载的方法。
另外LOB类型分成两种一种为所有记录只有一个文件即非lob类型与lob类型只有一
个文件。另一种为需要装载的文件有多个如用户注册资料用户对应相片为LOB类型
而记录中只记录用户图片的路径具体图片在另外一个文件之中。例如文件user.dat文件如SQL*Loader使用说明
25/28 下
Xqy&21&pic1.jpeg
test&22&pic2.jpeg
其中第一个为用户名称第二个为年龄第三个图片为图片名称。但在数据装载入库的时候
不是转载图片路径而是建立lob类型把整个图片装入内存之中。
如果是第一种情况即只有一个文件需要装载那么跟普通的装载一样在LOB字段后面
使用CHAR(xx),即可装载成功。
一下说明的主要是针对第二种情况的装载。 3.9.1. 控制文件 Load DATA
INFILE *
INTO TABLE demolob
REPLACE
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
vtext filler char,
context LOBFILE (vtext) TERMINATED BY EOF NULLIF context= 'NULL'
)
BEGINDATA
sqla.dat
sqlb.dat
sqlc.dat
注
filler是loader的保留字表示vtext是变量而不是字段名filler char表示是字符串变量为
后面的lobfile使用lobfile是loader的函数表示该字段的值从lobfile取得
TERMINATED BY EOF 表示每行的每个lob字段都来自一个独立的文件 3.9.2. 数据文件 Sqla.dat:
1011234515
1015432140
1012345620
3032345610
--空行
Sqlb.dat:
2021234515 SQL*Loader使用说明
26/28 2022345610
4041234510
-空行
Sqlc.dat:
Sqlc.dat 3.9.3. 装载结果
注
在sqlplus如果想查看LOB类型的数据会有如下的提示
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
解决的办法可以通过oracle提供的大数据转换包来实现:
select utl_raw.cast_to_varchar2(context) from demolob;通过转化就可以正常显示。
另外在通常情况下想要insert数据入lob也会出现如下的错误
insert into demolob values('test)
ERROR at line 1:
ORA-01465: invalid hex number
解决的办法可以通过RAWTOHEX函数插入数据
etl@CXDM> insert into demolob values( RAWTOHEX('zhong') );
1 row created. 3.10. 外部表装载 外部表的装载跟普通装载的控制文件一样因此没有给出控制文件使用外部表的装载只需
要在命令行或者参数文件里面加入EXTERNAL_TABLE=GENERATE_ONLY三个可选项SQL*Loader使用说明
27/28 中的一个就可以了。本例子因为测试环境问题所以只做GENERATE_ONLY情况下的装载
也就是只生成外部表装载需要的脚本。通过GENERATE_ONLY选项得到的脚本放于日志文
件中可以直接通过日志文件查看。
以下对外部表装载选项说明
NOT_USED默认为不使用外部表装载功能。
GENERATE_ONLY通过该选项可以生成需要通过外部表装载的所有步骤
EXECUTE直接使用外部表功能装载。该选项会运行GENERATE_ONLY中生成的脚本如果运
行过程中出现sql语句返回错误装载将中断。如果在装载过程中使用到了
sequence那sqlldr将建立一个临时的sequence进行装然等装载完成将删
除
sequence。因此在使用sequence装载的情况下sequence列出现的结果可能会
不一样。在运行外部表的过程中因为使用到了目录因此需要装载的用户有相应
的数据库权限如CREATE ANY DIRECTORY , DELETE ANY
DIRECTORY.
3.10.1. 装载结果 通过查看日志文件可以看见如下的结果
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/app/etl/_xqy/sqlldr'
"sql.log" 100 lines, 2660 characters
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/app/etl/_xqy/sqlldr'
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/app/etl/_xqy/sqlldr/'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_XQY"
(
"A" NUMBER,
"B" VARCHAR2(20),
"C" VARCHAR2(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'kksql.bad' SQL*Loader使用说明
28/28 LOGFILE 'sql.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "&" OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"A" RECNUM,
"B" CHAR(255)
TERMINATED BY "&" OPTIONALLY ENCLOSED BY '"',
"C" CHAR(255)
TERMINATED BY "&" OPTIONALLY ENCLOSED BY '"'
)
TERMINATED BY "&" OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'kksql.dat'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO XQY
(
A,
B,
C
)
SELECT
"A",
"B",
"C"
FROM "SYS_SQLLDR_X_EXT_XQY"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_XQY"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000