客服微信
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
SQL*Loader 是一个批量加载实用程序，用于将数据从外部文件移动到 Oracle 数据库中。 SQL*Loader支持多种加载格式、选择性加载、多表加载。
SQL*Loader (sqlldr) 是用于高性能数据加载的实用程序。数据可以从任何文本文件加载并插入到数据库中。
可以使用 sqlldr（在某些平台上为 sqlload）实用程序将数据加载到 Oracle 数据库中。调用不带参数的实用程序以获取可用参数的列表。看下面的例子：
sqlldr username/password@server control=loader.ctl
此示例控制文件 (loader.ctl) 将加载包含分隔数据的外部数据文件：
load data infile '/home/oracle/sqlloader/mydata.csv' into table emp fields terminated by "," optionally enclosed by '"' ( empno, empname, sal, deptno ) 参数说明： INTO TABLE emp --要插入记录的表 Fields terminated by "," --数据中每行记录用 "," 分隔 optionally enclosed by '"' --数据中每个字段用 '"' 框起，导入的时间不会将"作为数据导入表字段中。比如empname字段中存储Scott Tiger和"Scott Tiger"的区别
mydata.csv 文件如下所示：
10001,"Scott Tiger", 1000, 40 10002,"Frank Naude", 500, 20
创建emp表语句如下：
create table emp(empno number,empname varchar2(100),sal number,deptno int);
导入成功的输出日志如下：
Path used: Conventional Commit point reached - logical record count 2 Table EMP: 2 Rows successfully loaded. Check the log file: loader.log for more information about the load.
或者，您可以使用以下语法之一来处理制表分隔文件：
fields terminated by "\t" fields terminated by X'09'
此外，如果您的文件采用 Unicode，您可以进行以下添加。
load data CHARACTERSET UTF8 infile '/home/oracle/sqlloader/mydata.csv' into table emp fields terminated by "," optionally enclosed by '"' ( empno, empname, sal, deptno )
另一个示例控制文件，其中内联数据格式化为固定长度记录。诀窍是指定“*”作为数据文件的名称，并使用 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
导入成功之后的数据如下
DEPT DEPTNAME ---------------------------------------- ---------------------------------------- OSC OMPUTER SCIENCE NGL NGLISH LITERATURE ATH ATHEMATICS OLY OLITICAL SCIENCE
打开 MS-Excel 电子表格并将其另存为 CSV（逗号分隔值）文件。现在可以将该文件复制到 Oracle 计算机并使用 SQL*Loader 实用程序加载。
可能出现的问题和解决方法：
电子表格可能包含带有换行符 (ALT+ENTER) 的单元格。 SQL*Loader 期望整个记录位于一行上。运行以下宏来删除换行符（工具 - 宏 - Visual Basic 编辑器）：
Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
Dim TheCell As Range
On Error Resume Next
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub
Oracle 不提供任何数据卸载实用程序。以下是一些解决方法：
4.1、使用SQL*Plus
您可以使用 SQL*Plus 选择数据并格式化数据，然后将其假脱机到文件中。此示例假脱机输出可导入 MS-Excel 的 CSV（逗号分隔值）文件：
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
警告：如果您的数据包含逗号，请选择数据中没有的另一个分隔符。您还可以将包含逗号的列括起来。
如果您不想手动输入逗号，也可以使用“set colsep”命令。这可以节省大量的打字工作。例子：
set colsep ',' set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on spool oradata1.txt select col1, col2, col3 from tab1 where col2 = 'XYZ'; spool off
4.2、使用PL/SQL
PL/SQL的UTL_FILE包也可以用来卸载数据。例子：
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BEGIN
HANDLE := UTL_FILE.FOPEN('HR_OUT','EMP.TXT','W',1000);
FOR I IN (SELECT EMPNO|| ',' || EMPNAME|| ',' || SAL||','|| DEPTNO as MSG FROM HR.EMP T) LOOP
UTL_FILE.PUT_LINE(HANDLE,I.MSG);
END LOOP;
UTL_FILE.FFLUSH(HANDLE);
UTL_FILE.FCLOSE(HANDLE);
END;
/
5.1、加载可变长度的数据
在第一个示例中，我们将展示如何将分隔（可变长度）数据加载到 Oracle 中：
LOAD DATA
INFILE '/home/oracle/sqlloader/mydata1.csv'
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
trailing nullcols --表的字段没有对应的值时允许为空
mydata1.csv
11111,AAAAAAAAAA
22222,"A,B,C,D,"
示例
create table load_delimited_data(data1 number,data2 varchar2(50)); sqlldr hr/hr@pdb2 control=load5.ctl
注意：SQL*Loader 中的默认数据类型是 CHAR(255)。要加载长度超过 255 个字符的字符字段，请在控制文件中对类型和长度进行编码。通过这样做，Oracle 将分配一个足够大的缓冲区来容纳整个列，从而消除潜在的“数据文件中的字段超出最大长度”错误。例子：
... resume char(4000), ...
5.2、加载固定长度的数据
如果需要加载位置数据（固定长度），请查看以下控制文件示例：
LOAD DATA
INFILE '/home/oracle/sqlloader/mydata2.csv'
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
mydata2.csv
11111AAAAAAAAAA
22222BBBBBBBBBB
例如，position(01:05) 将给出第 1 到第 5 个字符（11111 和 22222）。
示例
create table load_positional_data(data1 number,data2 varchar2(50)); sqlldr hr/hr@pdb2 control=load6.ctl
通过指定“SKIP=n”关键字，可以跳过不需要的标头记录或继续中断的加载（例如，如果空间不足）。 “n”指定要跳过的逻辑行数。看看这些例子：
OPTIONS ( SKIP=3) LOAD DATA INFILE '/home/oracle/sqlloader/mydata3.csv' INTO TABLE load_positional_data ( data1 POSITION(1:5), data2 POSITION(6:15) ) mydata3.csv 11111AAAAAAAAAA 22222BBBBBBBBBB 33333CCCCCCCCCC 44444DDDDDDDDDD 55555EEEEEEEEEE 66666FFFFFFFFFF sqlldr userid=ora_id/ora_passwd control=control_file_name.ctl skip=3 加载成功的数据 44444DDDDDDDDDD 55555EEEEEEEEEE 66666FFFFFFFFFF
如果要继续进行多表直接路径加载，则可能需要使用 CONTINUE_LOAD 子句而不是 SKIP 参数。 CONTINUE_LOAD 允许您为正在加载的每个表指定要跳过的不同行数。
数据加载到 Oracle 数据库时可以进行修改。还可以使用静态值或派生值填充列。但是，这仅适用于常规加载路径（不适用于直接路径加载）。这里有些例子：
LOAD DATA
INFILE '/home/oracle/sqlloader/mydata4.csv'
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')"
)
mydata4.csv
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
示例
create table modified_data(rec_no number,region varchar2(10),time_loaded date,data1 number,data2 varchar2(20),data3 date);
CREATE SEQUENCE my_db_sequence
INCREMENT BY 1
START WITH 1
MINVALUE 0
NOCYCLE
CACHE 20
sqlldr hr/hr@pdb2 control=load8.ctl
select * from modified_data;
REC_NO REGION TIME_LOADED DATA1 DATA2 DATA3
---------- ---------- -------------------- ---------- -------------------- -------------------
4 31 16:58 111.11 AAAAAAAAAA 2099-12-01 12:00:00
5 31 16:58 222.22 BBBBBBBBBB 2099-01-12 12:00:00
坏文件记录不符合要求的数据
LOAD DATA
INFILE '/home/oracle/sqlloader/mydata4.csv'
BADFILE '/home/oracle/sqlloader/mydata4.bad'
DISCARDFILE '/home/oracle/sqlloader/mydata4.dis'
APPEND
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')"
)
BADFILE 被SQL*Loader拒绝的记录，被拒绝的记录可能是不符合要求的记录。
DISCARDFILE 记录没有通过的数据。
APPEND 追加数据
比如
mydata4.csv
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
33333CCCCCCCCCC998711-->这行数据将被记录到badfile里
6.1、从多个文件加载数据
可以通过重复 INFILE 子句从多个输入文件加载，前提是它们使用相同的记录格式。这是一个例子：
LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( EMPNO POSITION(1:4) INTEGER EXTERNAL,
EMPNAME POSITION(6:15) CHAR,
SAL POSITION(17:18) CHAR,
DEPTNO POSITION(20:23) INTEGER EXTERNAL
)
示例
file1.dat 1001AAAAAAAAAAA39403 file2.dat 1002BBBBBBBBBBB40506 file3.dat 1003CCCCCCCCCCC50708 sqlldr hr/hr@pdb2 control=load9.ctl
还可以在 SQL*Loader 控制文件中指定多个“INTO TABLE”子句来加载到多个表中。看下面的例子：“选项卡”字段被标记为 FILLER，因为我们不想加载它。
LOAD DATA
INFILE '/home/oracle/sqlloader/mydata5.csv'
INTO TABLE tab1 WHEN tab = 'tab1'
( tab FILLER CHAR(4),
col1 INTEGER
)
INTO TABLE tab2 WHEN tab = 'tab2'
( tab FILLER POSITION(1:4),
col1 INTEGER
)
mydata5.csv
tab1|1
tab1|2
tab2|2
tab3|3
示例
create table tab1(tab varchar2(20),col1 int); create table tab2(tab varchar2(20),col1 int); sqlldr hr/hr@pdb2 control=load10.ctl
“选项卡”字段被标记为 FILLER，因为我们不想加载它。
请注意在第二个路由值上使用“POSITION”（tab = 'tab2'）。默认情况下，对于新的 INTO TABLE 子句，字段扫描不会从记录的开头重新开始。相反，扫描会从中断处继续。需要 POSITION 才能将指针重新重置到记录的开头。在分隔格式中，在第一列后使用“POSITION(1)”来重置指针。
另一个例子：
LOAD DATA
INFILE 'mydata.dat'
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
)
是否可以有选择地仅加载需要的记录？
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) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
注意：SQL*Loader 不允许在 WHEN 子句中使用 OR。您只能使用 AND，如上例所示！要解决此问题，请编写多个“INTO TABLE ... WHEN”子句。这是一个例子：
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
不能将 POSITION(x:y) 与分隔数据一起使用。幸运的是，从 Oracle 8i 开始，我们可以指定 FILLER 列。 FILLER 列用于跳过加载文件中的列/字段，忽略不需要的字段。看这个例子：
LOAD DATA TRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )
如果稍后再次需要跳过的列的值，则可以使用 BOUNDFILLER（适用于 Oracle 9i 及更高版本）。这是一个例子：
LOAD DATA INFILE '/home/oracle/sqlloader/mydata6.csv' TRUNCATE INTO TABLE t1 FIELDS TERMINATED BY "," trailing nullcols ( c1, field2 BOUNDFILLER, field3 BOUNDFILLER, field4 BOUNDFILLER, field5 BOUNDFILLER, c2 ":field2 || :field3", c3 ":field4 + :field5" )
示例
create table t1(c1 varchar2(100),c2 varchar2(100),c3 varchar2(100)); mydata6.csv aa,bb,cc,11,22,ff,gg sqlldr hr/hr@pdb2 control=load11.ctl
可以使用以下两个子句之一从多个物理记录创建一个逻辑记录：
CONCATENATE - 当 SQL*Loader 应该将相同数量的物理记录组合在一起形成一个逻辑记录时使用它。
示例
#创建测试表
create table multi_line(
x1 varchar2(100),x2 varchar2(100),
x3 varchar2(100),x4 varchar2(100),
x5 varchar2(100),x6 varchar2(100));
#准备测试数据
mydate12.csv
a1,b1,c1,
a2,b2,c2,
a3,b3,c3,
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
#编写控制文件
load12.ctl
LOAD DATA
INFILE 'mydate12.csv'
concatenate 2
INTO TABLE multi_line
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
#导入
sqlldr hr/hr@pdb2 control=load12.ctl
#验证
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
select * from multi_line;
a1 b1 c1 a2 b2 c2
a3 b3 c3 a4 b4 c4
a5 b5 c5 a6 b6 c6
a7 b7 c7 a8 b8 c8
a9 b9 c9
CONTINUEIF - 如果条件指示应将多条记录视为一条记录，则使用。例如。在第 1 列中添加“#”字符。
#编写控制文件
load13.ctl
LOAD DATA
INFILE 'mydate13.csv'
continueif next (1:2) = '--'
INTO TABLE multi_line
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
x2,
x3,
x4,
x5,
x6
)
#准备数据
mydata13.csv
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,
#导入
sqlldr hr/hr@pdb2 control=load13.ctl
#验证
set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
select * from multi_line;
b1 c1 a2 b2 c2
b3 c3 a4 b4 c4
b5 c5
b6 c6 a7 b7 c7
b8 c8
b9 c9
#这里X1是空值,因为过滤条件要第一个字符是--
使用流记录格式，您可以定义记录分隔符，以便允许您在字段内容中使用默认分隔符（'
'）。
在 INFILE 子句之后设置分隔符：
load data infile "test.dat" "str '|\n'" into test_table fields terminated by ';' TRAILING NULLCOLS ( desc, txt )
这里有|和换行符作为字段隔离判断
test.dat:
one line;hello dear world;| two lines;Dear world, hello!;|
示例
select * from test_table;
DESC1 TXT
------------------------------ --------------------------------------------------
one line hello dear world
two lines Dear world,
hello!
请注意，这似乎不适用于内联数据（INFILE * 和 BEGINDATA）。
SQL*Loader Express 实际上只是命令行界面的简单扩展，因此所有默认值都旨在处理与表结构匹配的基本 CSV 文件。例如，如果我们要将数据加载到 EMP2 表中，默认设置将查找名为“EMP2.dat”的转储文件，该文件内部与 EMP2 表的结构匹配。因此，如果派生设置和默认设置不可接受，我们只需指定命令行参数。
注意事项
命令行中的表名大小写一定要和操作系统上对应的文件名大小写一样，而且文件扩展名必须是.dat
数据文件内的数据分割必须是以逗号（CSV格式）做分隔符。
表列必须是标量数据类型（字符、数字或日期时间）
CONN scott/tiger@pdb1
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
-- Create data file.
SET PAGESIZE 0 LINESIZE 100 TRIMSPOOL ON FEEDBACK OFF
SPOOL /tmp/EMP.dat.tmp
SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' ||
hiredate || ',' || sal || ',' || comm || ',' || deptno
FROM emp;
SPOOL OFF
SET PAGESIZE 14 FEEDBACK ON
-- Clean up the file.
HOST cat /tmp/EMP.dat.tmp | grep '[0-9]\{4\}' > /tmp/EMP.dat
#创建emp2表
CREATE TABLE emp2 AS
SELECT *
FROM emp
WHERE 1=2;
$ cd /tmp
$ cp EMP.dat EMP2.dat
$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\"
不能，但通过将 ROWS= 参数设置为较大的值，可以减少提交。当您使用较高的 ROWS= 值时，请确保准备好大的回滚段。
rows -- 常规路径绑定数组中或直接路径保存数据间的行数
一个非常简单但容易被忽视的提示是在加载过程中不要在加载表上有任何索引和/或约束（主键）。即使将 ROWS= 设置为较高值，这也会显着减慢加载时间。
在命令行中添加以下选项：DIRECT=TRUE。这将有效地绕过大部分 RDBMS 处理。但是，有些情况下您无法使用直接加载。有关详细信息，请参阅下面有关传统路径加载器和直接路径加载器之间差异的常见问题解答。
通过指定 UNRECOVERABLE 选项关闭数据库日志记录。此选项只能用于直接数据加载。
同时运行多个加载作业。
传统的路径加载器本质上是使用标准 INSERT 语句加载数据。直接路径加载器 (DIRECT=TRUE) 绕过与之相关的大部分逻辑，并直接加载到 Oracle 数据文件中。有关直接路径加载限制的更多信息可以从 Oracle Server Utilities Guide 获取。
直接路径加载的一些限制是：
加载的数据不会被复制
不能总是在控制文件中使用 SQL 字符串进行列处理（类似这样的操作可能会失败：col1 date 'ddmonyyyy' 'substr(:period,1,9)'）
SQL*Loader 可以从“主数据文件”、SDF（辅助数据文件 - 用于加载嵌套表和 VARRAY）或 LOBFILE 加载数据。 LOBFILE 方法提供了一种将文档、照片、图像和音频剪辑加载到 BLOB 和 CLOB 列中的简单方法。看这个例子：
给出下表：
CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
控制文件：
LOAD DATA INFILE * INTO TABLE image_table REPLACE FIELDS TERMINATED BY ',' ( image_id INTEGER(5), file_name CHAR(30), image_data LOBFILE (file_name) TERMINATED BY EOF ) BEGINDATA 001,image1.gif 002,image2.jpg 003,image3.jpg
