SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库 中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式：
传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。
直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。
利用直接路径加载，能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还 可能避开redo生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。
如果不带任何输入地从命令行执行SQLLDR，它会提供以下帮助：
[oracle@jy ~]$ sqlldr SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:32:22 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Usage: SQLLDR keyword=value [,keyword=value,...] Valid Keywords: userid -- ORACLE username/password 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) load -- number of logical records to load (Default all) errors -- number of errors to allow (Default 50) rows -- number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- size of conventional path bind array in bytes (Default 256000) silent -- suppress messages during run (header,feedback,errors,discards,partitions) direct -- use direct path (Default FALSE) parfile -- parameter file: name of file that contains parameter specifications parallel -- do parallel load (Default FALSE) file -- file to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- size of read buffer (Default 1048576) external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED) columnarrayrows -- number of rows for direct path column array (Default 5000) streamsize -- size of direct path stream buffer in bytes (Default 256000) multithreading -- use multithreading in direct path resumable -- enable or disable resumable for current session (Default FALSE) resumable_name -- text string to help identify resumable statement resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200) date_cache -- size (in entries) of date conversion cache (Default 1000) 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,需要有一个控制文件(control file).控制文件中包含描述输入数据的信息(如输入数据的布局、数据类型等),另外还包含有关目标表的信息.控制文件甚至还可以包含要加载的数据.在下面的例子中,我们将一步一步地建立一个简单的控制文件，并对这些命令提供必须的解释
(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) FIELDS TERMINATED BY ‘,’
(5) (DEPTNO, DNAME, LOC )
(6) BEGINDATA
(7) 10,Sales,Virginia
(8) 20,Accounting,Virginia
(9) 30,Consulting,Virginia
(10) 40,Finance,Virginia
[oracle@jy ~]$ vi demo1.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia
LOAD DATA:这会告诉SQLLDR要做什么(在这个例子中,则指示要加载数据).SQLLDR还可以执行CONTINUE_LOAD,也就是继续加载.只有在继续一个多表直接路径加载时才能使用后面这个选项.
INFILE *:这会告诉SQLLDR所要加载的数据实际上包含在控制文件本身上,如第6～10行所示.也可以指定包含数据的另一个文件的文件名.如果愿意,可以使用一个命令行参数覆盖这个INFILE语句.要注意的是命令行选项总会涵盖控制文件设置.
INTO TABLE DEPT:这会告诉SQLLDR要把数据加载到哪个表中(在这个例子中,数据要加载到DEPT表中).
FIELDS TERMINATED BY ‘,’:这会告诉SQLLDR数据的形式应该是用逗号分隔的值.为SQLLDR描述输入数据的方式有数十种;这只是其中较为常用的方法之一.
(DEPTNO, DNAME, LOC):这会告诉SQLLDR所要加载的列,这些列在输入数据中的顺序以及数据类型.这是指输入流中数据的数据类型,而不是数据库中的数据类型.在这个例子中,列的数据类型默认为CHAR(255),这已经足够了.
BEGINDATA:这会告诉SQLLDR你已经完成对输入数据的描述,后面的行(第7～10行)是要加载到DEPT表的具体数据.
这个控制文件采用了最简单,最常用的格式之一:将定界数据加载到一个表.要使用这个控制文件(名为demo1.ctl),只需创建一个空的DEPT表：
sys@JINGYONG> create table dept 2 (deptno number(2) constraint dept_pk primary key, 3 dname varchar2(14), 4 loc varchar2(14) 5 ); 表已创建。
并运行以下命令：
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo1.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
如果表非空，就会收到一个错误消息：
SQLLDR-601: For INSERT option, table must be empty. Error on table DEPT
这是因为,这个控制文件中几乎所有选项都取默认值,而默认的加载选项是INSERT(而不是APPEND,TRUNCATE或REPLACE).要执行INSERT,SQLLDR就认为表为空.如果想向DEPT表中增加记录,可以指定加载选项为APPEND;或者,为了替换DEPT表中的数据,可以使用REPLACE或TRUNCATE.REPLACE使用一种传统DELETE语句;因此,如果要加载的表中已经包含许多记录,这个操作可能执行得很慢.TRUNCATE则不同,它使用TRUNCATE SQL命令,通常会更快地执行,因为它不必物理地删除每一行.
每个加载都会生成一个日志文件,以上这个简单加载的日志文件如下：
[oracle@jy ~]$ cat demo1.log SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: demo1.ctl Data File: demo1.ctl Bad File: demo1.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 DEPT, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER Record 5: Rejected - Error on table DEPT, column DEPTNO. Column not found before end of logical record (use TRAILING NULLCOLS) Table DEPT: 4 Rows successfully loaded. 1 Row 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: 5 Total logical records rejected: 1 Total logical records discarded: 0 Run began on Thu Oct 03 11:43:20 2013 Run ended on Thu Oct 03 11:43:21 2013 Elapsed time was: 00:00:00.38 CPU time was: 00:00:00.09
日志文件会告诉我们关于加载的很多方面,从中可以看到我们所用的选项(默认或默认选项);可以看到读取了多少记录,加载 了多少记录等.日志文件指定了所有BAD文件和DISCARD文件的位置,甚至还会告诉我们加载用了多长时间.每个日志文件对于验 证加载是否成功至关重要,另外对于诊断错误也很有意义.如果所加载的数据导致SQL错误(也就是说,输入数据是”坏的”,并在BAD文件中建立了记录),这些错误就会记录在这个日志文件中.
如何加载定界数据
定界数据(delimited data)即用某个特定字符分隔的数据,可以用引号括起,这是当前平面文件最常见的数据格式.在大型机 上,定长,固定格式的文件可能是最可识别的文件格式,但是在UNIX和NT上,定界文件才是”标准”.
对于定界数据,最常用的格式是逗号分隔值(comma-separated values，CSV)格式.采用这种文件格式,数据中的每个字段与 下一个字段用一个逗号分隔.文本串可以用引号括起,这样就允许串本身包含逗号.如果串还必须包含引号,一般约定是使用两个引号(在下面的代码中,将使用””而不是”).要加载定界数据,相应的典型控制文件与前面第一个例子很相似,但是FIELDS TERMINATED BY 子句通常如下指定:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
它指定用逗号分隔数据字段,每个字段可以用双引号括起,如果我们把这个控制文件的最后部分修改如下:
[oracle@jy ~]$ cat demo2.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,"Virginia,USA" 20,Accounting,"Va,""USA""" 30,Consulting,Virginia 40,Finance,Virginia
使用这个控制文件运行SQLLDR时，结果如下：
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo2.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:01:03 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
使用这个控制文件运行SQLLDR时，结果如下：
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Sales Virginia,USA 20 Accounting Va,"USA" 30 Consulting Virginia 40 Finance Virginia
要注意以下几点：
部门10中的Virginia.USA:这是因为输入数据是”Virginia,USA”.输入数据字段必须包括在引号里才能保留数据中的逗号.否 则,数据中的这个逗号会被认为是字段结束标记,这样就会只加载Virginia,而没有USA文本.
Va,”USA”:这是因为输入数据是”Va,””USA”””.对于引号括起的串,SQLLDR会把其中”的两次出现计为一次出现.要加载一个包含可选包围字符(enclosure character)的串,必须保证这个包围字符出现两次.
另一种常用的格式是制表符定界数据(tag-delimited data),这是用制表符分隔而不是逗号分割的数据.有两种方法使用 TERMINATED BY子句来加载这种数据：
TERMINATED BY X’09′(使用十六进制格式的制表符;采用ASCII时,制表符为9)
TERMINATED BY WHITESPACE
这两种方法在实现上有很大差异,下面将会说明.还是用前面的DEPT表,我们将使用以下控制文件加载这个表:
[oracle@jy ~]$ vi demo3.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY WHITESPACE (DEPTNO, DNAME, LOC ) BEGINDATA 10 Sales Virginia
从字面上不太容易看得出来,不过要知道,在这里各部分数据之间都有两个制表符.这里的数据行实际上是:
10\t\tSales\t\tVirginia
在此\t是普通可识别的制表符转义字符.使用这个控制文件时(包含如前所示的TERMINATED BY WHITESPACE),表DEPT中的数据将是:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Sales Virginia
TERMINATED BY WHITESPACE会解析这个串,查找空白符(制表符,空格和换行符)的第一次出现,然后继续查找,直至找到下一 个非空白符.
另一方面,如果要使用FIELDS TERMINATED BY X’09’,如以下控制文件所示,这里稍做修改:
[oracle@jy ~]$ cat demo4.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY "X'09'" TRAILING NULLCOLS (DEPTNO,DNAME,LOC ) BEGINDATA 10 sales virginia [oracle@jy ~]$ sqlldr userid=jy/jy control=demo4.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 08:07:43 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 1
加载这样的定界数据时,很可能想逃过输入记录中的某些列.例如,你可能加载字段1,3和5,而跳过第2列和第4列.为此SQLLDR提供了FILLER关键字.这允许你映射一个输入记录中的一列,但不把它放在数据库中.例如,给定DEPT表以及先前的一个控制文件,可以修改这个控制文件,使用FILLER关键字正确地加载数据(跳过制表符):
[oracle@jy ~]$ vi demo3.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY WHITESPACE (DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC) BEGINDATA 10 Sales Virginia
所得到的表DEPT现在如下所示：
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Sales Virginia
如何加载固定格式数据
通常会有一个有某个外部系统生成的平面文件,而且这是一个定长文件,其中包含着固定位置的数据(positional data).例 如,NAME字段位于第1～10字节,ADDRESS字段位于地11～35字节等.
这种定宽的固定位置数据是最适合SQLLDR加载的数据格式.要加载这种数据,使用SQLLDR是最快的处理方法,因为解析输入数据流相当容易.SQLLDR会在数据记录中存储固定字节的偏移量和长度,因此抽取某个给定字段相当简单.如果要加载大量数据，将其转换为一种固定位置格式通常是最好的办法.当然,定宽文件也有一个缺点,它比简单的定界文件格式可能要大得多.
要加载定宽的固定位置数据,将会在控制文件中使用POSITION关键字,例如:
[oracle@jy ~]$ vi demo5.ctl LOAD DATA INFILE * INTO TABLE DEPT (DEPTNO position(1:2),DNAME position(3:16),LOC position(17:30) ) BEGINDATA 10Accounting Virginia,USA QL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:21:04 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------- 10 Accounting Vir ginia,USA
这个控制文件没有使用FIELDS TERMINATED BY子句;而是使用了POSITION来告诉SQLLDR字段从哪里开始,到哪里结束.关于 POSITION子句有意思的是,我们可以使用重叠的位置,可以在记录中来回反复.例如,如果如下修改DEPT表:
jy@JINGYONG> alter table dept add entire_line varchar2(30); 表已更改。
并使用以下控制文件:
[oracle@jy ~]$ vi demo6.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position(1:2), DNAME position(3:16), LOC position(17:30), ENTIRE_LINE position(1:30) ) BEGINDATA 10Accounting Virginia,USA
字段ENTIRE_LINE定义的POSITION(1:30).这会从所有30字节的输入数据中抽取出这个字段的数据,而其他字段都是输入数据的子串.这个控制文件的输出如下:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- -------------- ----------------------------- 10 Accounting Vir ginia,USA 10Accounting Virginia,USA
使用POSITION时,可以使用相对偏移量,也可以使用绝对偏移量.在前面的例子中使用了绝对偏移量,我们明确地指示了字段从 哪里开始,到哪里结束.也可以把前面的控制文件写作:
[oracle@jy ~]$ vi demo7.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position(1:2), DNAME position(*:16), LOC position(*:30), ENTIRE_LINE position(1:30) ) BEGINDATA 10Accounting Virginia,USA [oracle@jy ~]$ sqlldr userid=jy/jy control=demo7.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:25:53 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- -------------- ----------------------------- 10 Accounting Vir ginia,USA 10Accounting Virginia,USA
*指示控制文件得出上一个字段在哪里结束.因此,在这种情况下,(*:16)与(3:16)是一样的.注意,控制文件中可以混合使用相对位置和绝对位置.另外,使用*表示法时,可以把它与偏移量相加.例如,如果DNAME从DEPTNO结束之后的2个字节处开始,可以使用(*+2:16).在这个例子中,其作用就相当于使用(5:16).
POSITION子句中的结束位置必须是数据结束的绝对列位置.有时,可能指定每个字段的长度更为容易,特别是如果这些字段是连 续的(就像前面的例子一样).采用这种方式,只需告诉SQLLDR:记录从第1个字节开始,然后指定每个字段的长度就行了.这样我们就可以免于计算记录中的开始和结束偏移量,这个计算有时可能很困难.为此,可以不指定结束位置,而是指定定长记录中各个字段的长度,如下:
[oracle@jy ~]$ vi demo8.ctl REPLACE LOAD DATA INFILE * INTO TABLE DEPT (DEPTNO position(1) char(2), DNAME position(*) char(14), LOC position(*) char(14), ENTIRE_LINE position(1:30) ) BEGINDATA 10Accounting Virginia,USA [oracle@jy ~]$ sqlldr userid=jy/jy control=demo8.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:29:01 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- -------------- ----------------------------- 10 Accounting Vir ginia,USA 10Accounting Virginia,USA
在此只需告诉SQLLDR第一个字段从哪里开始及其长度.后面的每个字段都从上一个字段结束处开始,并具有指定的长度.直至最 后一个字段才需要再次指定位置,因为这个字段又要从记录起始处开始.
如何加载日期
使用SQLLDR加载日期相当简单,但是看起来这个方面经常导致混淆.你只需在控制文件中使用DATE数据类型,并指定要使用的日 期格式,这个日期格式与数据库中TO_CHAR和TO_DATE中使用的日期格式是一样的.SQLLDR会向数据应用这个日期格式,并为你完成加载.
例如,如果再把DEPT表修改如下:
jy@JINGYONG> alter table dept add last_updated date; 表已更改。
可以用以下控制文件加载它：
[oracle@jy ~]$ vi demo9.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC, ENTIRE_LINE, LAST_UPDATED date 'yyyy-mm-dd' ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 [oracle@jy ~]$ sqlldr userid=jy/jy control=demo9.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:47:17 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- ----------------------------- -------------- 10 Sales Virginia USA 01-5月 -00 20 Accounting Virginia USA 21-6月 -99 30 Consulting Virginia USA 05-1月 -00 40 Finance Virginia USA 15-3月 -01
就这么简单,只需在控制文件中应用格式,SQLLDR就会为我们完成日期转换.在某些情况下可能使用一个更强大的SQL函数更为合适.例如,如果你的输入文件包含多种不同格式的日期:有些有时间分量,有些没有;有些采用DD-MON-YYYY格式;有些格式为DD/MM/YYYY等等.
如果使用函数加载数据
在SQLLDR中使用函数很容易.要在SQLLDR脚本中向某个字段应用一个函数,只需将这个函数增加到控制文件中(用两个引号括起).例如,假设有前面的DEPT表,你想确保所加载的数据都是大写的.可以使用以下控制文件来加载:
[oracle@jy ~]$ vi demo10.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE "upper(:entire_line)", LAST_UPDATED date 'yyyy-mm-dd' ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 "demo10.ctl" 17L, 342C written [oracle@jy ~]$ sqlldr userid=jy/jy control=demo10.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:50:46 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- ----------------------------- -------------- 10 SALES VIRGINIA USA 01-5月 -00 20 ACCOUNTING VIRGINIA USA 21-6月 -99 30 CONSULTING VIRGINIA USA 05-1月 -00 40 FINANCE VIRGINIA USA 15-3月 -01
可以注意到,只需向一个绑定变量应用UPPER函数就可以很容易地将数据变为大写.要注意SQL函数可以引用任何列,而不论将 函数实际上应用于哪个列.这说明一个列可以是对两个或更多其他列应用一个函数的结果.例如,如果你想加载ENTIRE_LINE 列,可以使用SQL连接运算符.不过这种情况下这样做稍有些麻烦.现在,输入数据集中有4个数据元素.如果只是向控制文件中加入如下字符ENTIRE_LINE：
[oracle@jy ~]$ vi demo11.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'yyyy-mm-dd', ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 ~ ~ ~ ~ ~ "demo11.ctl" 18L, 360C written [oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:55:32 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
在demo11.log日志文件中可以看到:
Record 5: Rejected - Error on table DEPT, column DEPTNO. Column not found before end of logical record (use TRAILING NULLCOLS)
在此,SQLLDR告诉你,没等处理完所有列,记录中就没有数据了.这种情况下,解决方案很简单.实际上,SQLLDR甚至已经告诉了我们该怎么做:这就是使用TRAILING NULLCOLS.这样一来,如果输入记录中不存在某一列的数据,SQLLDR就会为该列绑定一个NULL值.在这种情况下,增加TRAILING NULLCOLS会导致绑定变量:ENTIRE_LINE成为NULL,所以再尝试这个控制文件：
[oracle@jy ~]$ vi demo11.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED date 'yyyy-mm-dd' ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 20,Accounting,Virginia,USA,1999-06-21 30,Consulting,Virginia,USA,2000-01-05 40,Finance,Virginia,USA,2001-03-15 [oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:00:49 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- ------------------------------ -------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 01-5月 -00 20 ACCOUNTING VIRGINIA 20AccountingVirginia1999-06-21 21-6月 -99 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 05-1月 -00 40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 15-3月 -01
之所以可以这样做,原因在于SQLLDR构建其INSERT语句的做法.SQLLDR会查看前面的控制文件,并看到控制文件中的DEPTNO, DNAME,LOC,LAST_UPDATED和ENTIRE_LINE这几列.它会根据这些列建立5个绑定变量.通常,如果没有任何函数,所建立的 INSERT语句就是:
INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )
VALUES (&DEPTNO,&DNAME,&LOC,&LAST_UPDATED,&ENTIRE_LINE);
然后再解析输入流,将值赋给相应的绑定变量,然后执行语句.如果使用函数,SQLLDR会把这些函数结合到INSERT语句中.在上 一个例子中,SQLLDR建立的INSERT语句如下所示：
INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( &DEPTNO, upper(&dname), upper(&loc), &last_updated,
&deptno||&dname||&loc||&last_updated );
然后再做好准备,把输入绑定到这个语句,再执行语句.所以SQL中能做的事情都可以结合到SQLLDR脚本中.由于SQL中增加了 CASE语句,所以这样做不仅功能极为强大,而且相当容易.例如,假设你的输入文件有以下格式的日期:
HH24:MI:SS:只有一个时间;日期默认为SYSDATE.
DD/MM/YYYY:只有一个日期;时间默认为午夜0点.
HH24:MI:SS DD/MM/YYYY:日期和时间都要显式提供。
可以使用如下的一个控制文件:
[oracle@jy ~]$ vi demo12.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end" ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 12:03:03 20,Accounting,Virginia,USA,02:23:54 30,Consulting,Virginia,USA,2000-01-05 01:24:00 40,Finance,Virginia,USA,2001-03-15 [oracle@jy ~]$ sqlldr userid=jy/jy control=demo12.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:06:49 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 jy@JINGYONG> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; 会话已更改。 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED ---------- -------------- -------------- --------------------------------------- --------------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00
如何加载有内嵌换行符的数据
过去,如果要加载可能包含换行符的自由格式的数据,这对于SQLLDR来说很成问题.换行符是SQLLDR的默认行结束符,要加载有内嵌换行符的数据有以下方法:
加载数据,其中用非换行符的其他字符来表示换行符(例如,在文本中应该出现换行符的位置上放上串\n),并在加载时使用一个SQL函数用一个CHR(10)替换该文本.
在INFILE指令上使用FIX属性,加载一个定长平面文件.
在INFILE指令上使用VAR属性,加载一个定宽文件,在该文件使用的格式中,每一行的前几个字节指定了这一行的长度(字节数 ).
在INFILE指令上使用STR属性,加载一个变宽文件,其中用某个字符序列来表示行结束符,而不是用换行符来表示.
使用一个非换行符的字符
如果你能对如何生成输入数据加以控制,这就是一种很容易的方法.如果创建数据文件时能很容易地转换数据,这种方法就能奏 效.其思想是,就数据加载到数据库时对数据应用一个SQL函数,用某个字符串来替换换行符.下面向DEPT表再增加另一个列:
jy@JINGYONG> alter table dept add comments varchar2(4000); 表已更改。
将使用这一列来加载文本,下面是一个有内联数据的示例控制文件:
[oracle@jy ~]$vi demo13.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS "replace(:comments,'\\n',chr(10))" ) BEGINDATA 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice in Virginia 40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia [oracle@jy ~]$ sqlldr userid=jy/jy control=demo13.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 14:45:48 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5
注意,调用中必须使用\\n来替换换行符,而不只是\n.这是因为\n会被SQLLDR识别为一个换行符,而且SQLLDR会把它转换为一 个换行符,而不是一个两字符的串.利用以上控制文件执行SQLLDR时,DEPT表中将加载以下数据:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS --------------------------------------------------------------------------------------------------------------- ------------------------------------------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia 20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:5 This is the Accountin Office in Virginia 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia 40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance Office in Virginia
使用IFX属性
另一种可用的方法是使用FIX属性.如果使用这种方法,输入数据必须出现在定长记录中.每个记录与输入数据集中所有其他记 录的长度都相同,即有相同的字节数.对于固定位置的数据,使用FIX属性就特别适合.这些文件通常是定长输入文件.使用自由格式的定界数据时,则不太可能是一个定长文件,因为这些文件通常是变长的(这正是定界文件的关键:每一行不会不必要地过长).
使用FIX属性时,必须使用一个INFILE子句,因为FIX属性是INFILE的一个选项.另外,如果使用这个选项,数据必须在外部存储 ,而并非存储在控制文件本身.因此,假设有定长的输入记录,可以使用如下的一个控制文件:
[oracle@jy ~]$ vi demo14.ctl LOAD DATA INFILE demo14.dat "fix 80" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS ) [oracle@jy ~]$ vi demo14.dat 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia\n 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice\n 40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia \n
这个文件指定了一个输入数据文件(domo14.dat),这个文件中每个记录有80字节,这包括尾部的换行符(每个记录最后可能有换行符,也可能没有).在这种情况下,输入数据文件中的换行符并不是特殊字符.这只是要加载(或不加载)的另一个字符而已.要知道:记录的最后如果有换行符,它会成为这个记录的一部分.为了充分理解这一点,我们需要一个实用程序将文件的内容转储在屏幕上,以便我们看到文件中到底有什么.使用Linux(或任何Unix版本)利用od就很容易做到,这个程序可以将文件以八进制(和其他格式)转储到屏幕上.我们将使用下面的demo.dat文件.注意以下输入中的第一列实际上是八进制,所以第2行上的数字0000012是一个八进制数,不是十进制数10.由此我们可以知道所查看的文件中有哪些字节.我对这个输出进行了格式化,使得每行显示10个字符(使用-w10),所以0,12,24和36实际上就是0,10,20和30.
[oracle@jy ~]$ od -c -w10 -v demo14.dat 0000000 a l e s , V i r g i 0000012 n i a , U S A , 2 0 0000024 0 0 - 0 5 - 0 1 1 0000036 2 : 0 3 : 0 3 , T h 0000050 i s i s t h e 0000062 S a l e s \ n O f f 0000074 i c e i n V i r 0000106 g i n i a \n 2 0 , A 0000120 c c o u n t i n g , 0000132 V i r g i n i a , U 0000144 S A , 0 2 : 2 3 : 5 0000156 4 , T h i s i s 0000170 t h e A c c o u n 0000202 t i n \ n O f f i c 0000214 e i n V i r g i 0000226 n i a \n 3 0 , C o n 0000240 s u l t i n g , V i 0000252 r g i n i a , U S A 0000264 , 2 0 0 0 - 0 1 - 0 0000276 5 0 1 : 2 4 : 0 0 0000310 , T h i s i s t 0000322 h e C o n s u l t 0000334 i n g \ n O f f i c 0000346 e i n V i r g i 0000360 n i a \n 4 0 , F i n 0000372 a n c e , V i r g i 0000404 n i a , U S A , 2 0 0000416 0 1 - 0 3 - 1 5 , T 0000430 h i s i s t h e 0000442 F i n a n c e \ n 0000454 O f f i c e i n 0000466 V i r g i n i a \n 0000477
注意,在这个输入文件中,并没有用换行符(\n)来指示SQLLDRE记录在哪里结束;这里的换行符只是要加载的数据而已.SQLLDR使用FIX宽度(80字节)来得出要读取多少数据.实际上,如果查看输入数据,可以看到,输入文件中提供给SQLLDR的记录甚至并非以\n结束.部门20的记录之前的字符是一个空格,而不是换行符.
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo14.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 15:03:05 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 4
我们知道了每个记录的长度为80字节,现在就可以用前面有FIX80子句的控制文件来加载这些数据了.完成加载后,可以看到以 下结果:
jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS --------------------------------------------------------------------------------------------------------------- ------------------- 10 SALES VIRGINIA10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales\nOffice in Virginia 20 ACCOUNTING VIRGINIA20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accountin\nOffice in Virginia\n 30 CONSULTING VIRGINIA30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting\nOffice\n 40 FINANCE VIRGINIA40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance\nOffice in Virginia \n
你可能需要截断这个数据,因为尾部的空白符会保留.可以在控制文件中使用TRIM内置SQL函数来完成截断.
Linux上 ,行结束标记就是\n(SQL中的CHR(10)).在Windows NT上,行结束标记却是\r\n(SQL中的CHR(13)||CHR(10)).一般来讲,如果使用FIX方法,就要确保是在同构平台上创建和加载文件(Linux上创建,Linux上加载;或者Windows上创建,Windows上加载)
使用VAR属性
要加载有内嵌换行符的数据,另一种方法是使用VAR属性.使用这种格式时,每个记录必须以某个固定的字节数开始,这表示这 个记录的总长度.通过使用这种格式,可以加载包含内嵌换行符的变长记录,但是每个记录的开始处必须有一个记录长度字段. 因此,如果使用如下的一个控制文件:
[oracle@jy ~]$ vi demo15.ctl LOAD DATA INFILE 'demo15.dat' "var 3" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS )
VAR 3指出每个输入记录的前3个字节是输入记录的长度,如果取以下数据文件：
[oracle@jy ~]$ cat demo15.dat 07910,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia 07820,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia 08930,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia 07440,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia
可以使用该控制文件来加载.在输入数据文件中有4行数据.第一行从079开始,这说明接下来79字节是第一个输入记录.这79字 节包括单词Virginia后的结束换行符.下一行从078开始,这一行有78字节的文本,依此类推.使用这种格式数据文件,可以很 容易地加载有内嵌换行符的数据.
同样,如果在使用Linux和Windows(前面的例子都在Linux上完成,其中换行符只是一个字符长),就必须调整每个记录的长度字 段.在Windows上,前例.dat文件中的长度字段应该是80,79,90和75.
使用STR属性 要加载有内嵌换行符的数据,这可能是最灵活的一种方法.通过使用STR属性,可以指定一个新的行结束符(或字符序列).就能创建一个输入数据文件,其中每一行的最后有某个特殊字符,换行符不再有特殊含义. 我更喜欢使用字符序列,通常会使用某个特殊标记,然后再加一个换行符.这样,在一个文本编辑器或某个实用程序中查看输入 数据时,就能很容易地看到行结束符,因为每个记录的最后仍然有一个换行符.STR属性以十六进制指定,要得到所需的具体十 六进制串,最容易的方法是使用SQL和UTL_RAW来生成十六进制串.例如,假设使用的是Linux平台,行结束标记是CHR(10)(换行 ),我们的特殊标记字符是一个管道符号(|),则可以写为:
jy@JINGYONG>select utl_raw.cast_to_raw('|'||chr(10)) from dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(1
--------------------------------------------------------------------------------
7C0A
由此可知,在Linux上我们需要使用的STR是X’7C0A’.
在Windows上,要使用UTL_RAW.CAST_TO_RAW(‘|’||chr(13)||chr(10))
为了使用这个方法,要有以下控制文件:
[oracle@jy ~]$ vi demo16.ctl LOAD DATA INFILE demo16.dat "str X'7C0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS )
因此,如果输入数据如下:
[oracle@jy ~]$ cat demo16.dat 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia| 20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia| 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia| 40,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia|
其中,数据文件中的每个记录都以|\n结束,前面的控制文件就会正确地加载这些数据.
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo16.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 07:45:30 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 4 jy@JINGYONG> select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS --------------------------------------------------------------------------------------------------------------- ----------------- 10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia 20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accounting Office in Virginia 30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia 40 FINANCE VIRGINIA 40FinanceVirginia2001-01-15 2001-01-15 00:00:00 This is the Finance Office in Virginia
如果加载LOB
现在来考虑在LOB的一些方法.这不是一个LONG或LONG RAW字段,而是更可取的数据类型BLOB和CLOB.这些数据类型是Oracle 8.0及以后版本中引入的,与遗留的LONG和LONG RAW类型相比,它们支持更丰富的接口/功能集.
将分析两种加载这些字段的方法:SQLLDR和PL/SQL.
通过PL/SQL加载LOB
DBMS_LOB包的入口点为LoadFromFile,LoadBLOBFromFile和LoadCLOBFromFile.通过这些过程,我们可以使用一个BFILE(用于读取操作系统文件)来填充数据库中的BLOB或CLOB.LoadFromFile和LoadBLOBFromFile例程之间没有显著的差别,只不过后者会返回一些OUT参数,指示已经向BLOB列中加载了多少数据.不过,LoadCLOBFromFile例程还提供了一个突出的特性:字符集转换.使用LoadCLOBFromFile时,我们可以告诉数据库:这个文件将以另外某种字符集(不同于数据库正在使用的字符集)来加载,而且要执行必要的字符集转换.例如,可能有一个UTF8兼容的数据库,但是收到的要加载的文件却以WE8ISO8859P1字符集编码,或反之利用这个函数就能成功地加载这些文件.
要使用这些过程,需要在数据库中创建一个DIRECTORY对象.这个对象允许我们创建并打开BFILE(BFILE指向文件系统上数据库 服务器能访问的一个现有文件).DBMS_LOB包完全在服务器中执行.它只能看到服务器能看到的文件系统.特别是,如果你通过 网络访问Oracle,DBMS_LOB包将无法看到你的本地文件系统.
所以,需要先在数据库中创建一个DIRECTORY对象.
jy@JINGYONG> create or replace directory dir1 as '/home/oracle'; 目录已创建。 jy@JINGYONG> create or replace directory "dir2" as '/home/oracle'; 目录已创建。 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ --------------- ----------------- SYS dir2 /home/oracle SYS DIR1 /home/oracle
我们创建的dir1在数据字典中为大写
下面,将一些数据加载到BLOB或CLOB中:
jy@JINGYONG> create table demo
2 (id int primary key,
3 theClob clob
4 );
表已创建。
[oracle@jy ~]$ echo 'Hello World!'>/home/oracle/demo.txt
jy@JINGYONG> declare
2 l_clob clob;
3 l_bfile bfile;
4 begin
5 insert into demo values(1,empty_clob()) returning theclob into l_clob;
6 l_bfile:=bfilename('DIR1','demo.txt');
7 dbms_lob.fileopen(l_bfile);
8 dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
9 dbms_lob.fileclose(l_bfile);
10 end;
11 /
PL/SQL 过程已成功完成。
jy@JINGYONG> select dbms_lob.getlength(theClob), theClob from demo;
DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ----------------------------------------------------
13 Hello World!
在第5行我们在表中创建了一行,将CLOB设置为一个EMPTY_CLOB(),并从一个调用获取其值.除了临时LOB外,其余的LOB都住在数据库中,如果没有指向一个临时LOB的指针,或者指向一个已经在数据库中的LOB,将无法写至LOB变量.EMPTY_CLOB()不是一个NULL CLOB;而是指向一个空结构的合法指针(非NULL),它还有一个作用,可以得到一个LOB定位器,指向已锁定行中的数据.如果要选择这个值,而没有锁定底层的行,写数据就会失败,因为LOB在写之前必须锁定(不同于其他结构化数据).通过插入一行,当然我们也就锁定了这一行.如果我们要修改一个现有的行而不是插入新行,则可以使用SELECT FOR UPDATE来获取和锁定这一行.
在第6行上,我们创建了一个BFILE对象.注意,这里DIR1用的是大写,稍后就会看到,这是一个键.这是因为我们向BFILENAME() 传入了一个对象的名称,而不是对象本身.因此,必须确保这个名称与Oracle所存储的对象名称大小写匹配.
第7行打开了LOB,以便读取.
在第8行将操作系统文件/home/oracle/demo.txt的完整内容加载到刚插入的LOB定位器.这里使用DBMS_LOB.GETLENGTH()告诉LOADFROMFILE()例程要加载多少字节的BFILE(这里就是要加载全部字节).
最后,在第9行我们关闭了所打开的BFILE,CLOB已加载.
如果需要在加载文件的同时处理文件的内容,还可以在BFILE上使用DBMS_LOB.READ来读取数据.如果读取的数据实际上是文本,而不是RAW,那么使用UTL_RAW.CAST_TO_VARCHAR2会很方便.然后可以使用DBMS_LOB.WRITE或WRITEAPPEND将数据放入一个CLOB或BLOB.
通过SQLLDR加载LOB数据
现在我们来分析如何通过SQLLDR向LOB加载数据.对此方法不止一种,但是我们主要讨论两种最常用的方法:
数据内联在其他数据中。
数据外联存储(在外部存储),输入数据包含一个文件名,指示该行要加载的数据在哪个文件中.在SQLLDR术语中,这也称为二级数据文件(secondary data file，SDF).
先从内联数据谈起.
加载内联的LOB数据,这些LOB通常内嵌有换行符和其他特殊字符.
下面先来修改dept表,使COMMENTS列是一个CLOB而不是一个大的VARCHAR2字段:
jy@JINGYONG> truncate table dept; 表被截断。 jy@JINGYONG> alter table dept drop column comments; 表已更改。 jy@JINGYONG> alter table dept add comments clob; 表已更改。
例如,假设有一个数据文件(demo17.dat),它有以下内容:
[oracle@jy ~]$ cat demo17.dat 10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia| 20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia| 30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia| 40,Finance,Virginia,USA,2001-01-15,"This is the Finance Office in Virginia, it has embedded commas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: ""You will need to double up those quotes!"" to preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->"|
每个记录最后都是一个管道符号(|),后面是行结束标记.部门40的文本比其他部门的文本长得多,有多个换行符,内嵌的引号以及逗号.给定这个数据文件,可以创建一个如下的控制文件:
[oracle@jy ~]$ cat demo17.ctl LOAD DATA INFILE demo17.dat "str X'7C0A'" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", ENTIRE_LINE ":deptno||:dname||:loc||:last_updated", LAST_UPDATED "case when length(:last_updated)>9 then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'yyyy-mm-dd') end", COMMENTS char(1000000) )
注意:这个例子在Linux上执行,Linux平台上行结束标记长度为1字节,因此可以使用以上控制文件中的STR设置.在Windows上,STR设置则必须是’7C0D0A’.
要加载这个数据文件,我们在COMMENTS列上指定了CHAR(1000000),因为SQLLDR默认所有人们字段都为CHAR(255).CHAR (1000000)则允许SQLLDR处理多达1,000,000字节的输入文本.可以把这个长度值设置为大于输入文件中任何可能文本块的大小.通过查看所加载的数据,可以看到以下结果:
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo17.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 09:08:31 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 1 Commit point reached - logical record count 2 Commit point reached - logical record count 3 Commit point reached - logical record count 4 SQL> set long 999; SQL> select comments from jy.dept; COMMENTS -------------------------------------------------------------------------------- This is the Sales Office in Virginia This is the Accounting Office in Virginia This is the Consulting Office in Virginia This is the Finance Office in Virginia, it has embedded commas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: "You will need to double up those quotes!" to preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->
这里可以观察到:原来重复两次的引号不再重复,SQLLDR去除了在此放置的额外的引号.
加载外联的LOB数据,可能要把包含有一些文件名的数据文件加载在LOB中,而不是让LOB数据与结构化数据混在一起,这种情况 很常见.这提供了更大程度的灵活性,因为提供给SQLLDR的数据文件不必使用上述的4种方法之一来避开输入数据中的内嵌换行 符问题,而这种情况在大量的文本或二进制数据中会频繁出现.SQLLDR称这种额外的数据文件为LOBFILE.
SQLLDR还可以支持加载结构化数据文件(指向另外单独一个数据文件).我们可能告诉SQLLDR如何从另外这个文件分析LOB数据 ,这样就可以加载其中的一部分作为结构化数据中的每一行.这种模式的用途很有限.SQLLDR把这种外部引用的文件称为复杂二级数据文件(complex secondary data file).
LOBFILE是一种相对简单的数据文件,旨在简化LOB加载.在LOBFILE中,没有记录的概念,因此换行符不会成为问题,正是这个 性质使得LOBFILE与主要数据文件有所区别.在LOBFILE中,数据总是采用以下某种格式:
定长字段(例如,从LOBFILE加载字节100到1,000)
定界字段(以某个字符结束,或者用某个字符括起)
长度/值对,这是一个变长字段
其中最常见的类型是定界字段,实际上就是以一个文件结束符(EOF)结束.一般来讲,可能有这样一个目录,其中包含你想加载到LOB列中的文件,每个文件都要完整地放在一个BLOB中.此时,就可以使用带TERMINATED BY EOF子句的LOBFILE语句.
假设我们有一个目录,其中包含想要加载到数据库中的文件.我们想加载文件的OWNER,文件的TIME_STAMP,文件的NAME以及文 件本身.要加载数据的表如下所示:
jy@JINGYONG> create table lob_demo 2 (owner varchar2(255), 3 time_stamp date, 4 filename varchar2(255), 5 data blob 6 ); 表已创建。
在Linux上使用一个简单的ls –l来捕获输出(或者在Windows上使用dir/q/n),我们就能生成输入文件,并使用如下的一个控制文件加载:
[oracle@jy ~]$ cat demo19.ctl LOAD DATA INFILE * INTO TABLE LOB_DEMO REPLACE (owner position(14:28), time_stamp position(36:46) date "MM DD HH24:MI", filename position(48:100), data LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA -rw-r--r-- 1 oracle oinstall 18432 10 3 10:42 bifile.bbd -rw-r--r-- 1 oracle oinstall 1 10 3 12:50 demo10.bad -rw-r--r-- 1 oracle oinstall 342 10 3 12:50 demo10.ctl -rw-r--r-- 1 oracle oinstall 1948 10 3 12:50 demo10.log -rw-r--r-- 1 oracle oinstall 144 10 3 12:59 demo11.bad -rw-r--r-- 1 oracle oinstall 377 10 3 13:00 demo11.ctl -rw-r--r-- 1 oracle oinstall 1913 10 3 13:00 demo11.log -rw-r--r-- 1 oracle oinstall 89 10 3 13:06 demo12.bad -rw-r--r-- 1 oracle oinstall 582 10 3 13:05 demo12.ctl -rw-r--r-- 1 oracle oinstall 2147 10 3 13:06 demo12.log -rw-r--r-- 1 oracle oinstall 790 10 3 14:45 demo13.ctl -rw-r--r-- 1 oracle oinstall 2289 10 3 14:45 demo13.log -rw-r--r-- 1 oracle oinstall 80 10 3 15:01 demo14.bad -rw-r--r-- 1 oracle oinstall 441 10 3 15:00 demo14.ctl -rw-r--r-- 1 oracle oinstall 320 10 3 15:02 demo14.dat -rw-r--r-- 1 oracle oinstall 2230 10 3 15:03 demo14.log -rw-r--r-- 1 oracle oinstall 332 10 4 05:42 demo15_bak.dat -rw-r--r-- 1 oracle oinstall 442 10 4 05:31 demo15.ctl -rw-r--r-- 1 oracle oinstall 332 10 4 05:45 demo15.dat -rw-r--r-- 1 oracle oinstall 256 10 4 05:36 demo15.dat.bak -rw-r--r-- 1 oracle oinstall 2229 10 4 05:45 demo15.log -rw-r--r-- 1 oracle oinstall 3 10 4 07:44 demo16.bad -rw-r--r-- 1 oracle oinstall 446 10 4 07:44 demo16.ctl -rw-r--r-- 1 oracle oinstall 324 10 4 07:45 demo16.dat -rw-r--r-- 1 oracle oinstall 2235 10 4 07:45 demo16.log -rw-r--r-- 1 oracle oinstall 487 10 4 09:07 demo17.ctl -rw-r--r-- 1 oracle oinstall 741 10 4 09:02 demo17.dat -rw-r--r-- 1 oracle oinstall 2321 10 4 09:08 demo17.log -rw-r--r-- 1 oracle oinstall 213 10 4 09:05 demo18.ctl -rw-r--r-- 1 oracle oinstall 665 10 4 09:05 demo18.dat -rw-r--r-- 1 oracle oinstall 1860 10 4 09:06 demo18.log -rw-r--r-- 1 oracle oinstall 1 10 3 11:43 demo1.bad -rw-r--r-- 1 oracle oinstall 177 10 3 11:43 demo1.ctl -rw-r--r-- 1 oracle oinstall 1648 10 3 11:43 demo1.log -rw-r--r-- 1 oracle oinstall 1 10 3 12:01 demo2.bad -rw-r--r-- 1 oracle oinstall 214 10 3 12:00 demo2.ctl -rw-r--r-- 1 oracle oinstall 1648 10 3 12:01 demo2.log -rw-r--r-- 1 oracle oinstall 2 10 4 07:59 demo3.bad -rw-r--r-- 1 oracle oinstall 121 10 4 07:59 demo3.ctl -rw-r--r-- 1 oracle oinstall 1525 10 4 07:59 demo3.log -rw-r--r-- 1 oracle oinstall 18 10 4 08:07 demo4.bad -rw-r--r-- 1 oracle oinstall 141 10 4 08:11 demo4.ctl -rw-r--r-- 1 oracle oinstall 1657 10 4 08:11 demo4.log -rw-r--r-- 1 oracle oinstall 137 10 3 12:20 demo5.ctl -rw-r--r-- 1 oracle oinstall 1560 10 3 12:21 demo5.log -rw-r--r-- 1 oracle oinstall 175 10 3 12:23 demo6.ctl -rw-r--r-- 1 oracle oinstall 1641 10 3 12:23 demo6.log -rw-r--r-- 1 oracle oinstall 174 10 3 12:25 demo7.ctl -rw-r--r-- 1 oracle oinstall 1641 10 3 12:25 demo7.log -rw-r--r-- 1 oracle oinstall 184 10 3 12:28 demo8.ctl -rw-r--r-- 1 oracle oinstall 1640 10 3 12:29 demo8.log -rw-r--r-- 1 oracle oinstall 1 10 3 12:47 demo9.bad -rw-r--r-- 1 oracle oinstall 290 10 3 12:47 demo9.ctl -rw-r--r-- 1 oracle oinstall 1809 10 3 12:47 demo9.log -rw-r--r-- 1 oracle oinstall 13 10 4 08:22 demo.txt -rw-r--r-- 1 oracle oinstall 547 10 3 10:44 log.bbd [oracle@jy ~]$ sqlldr userid=jy/jy control=demo19.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:26:54 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 56
现在,运行SQLLDR之后检查LOB_DEMO表的内容,会发现以下结果:
SQL> select owner, time_stamp, filename, dbms_lob.getlength(data) from jy.lob_demo; OWNER TIME_STAMP FILENAME DBMS_LOB.GETLENGTH(DATA) ----------------------------- -------------------------------------------- oracle oinstall 2013-10-4 9 demo17.log 2321 oracle oinstall 2013-10-4 9 demo18.dat 665 oracle oinstall 2013-10-4 9 demo18.log 1860 oracle oinstall 2013-10-3 1 demo1.log 1648 oracle oinstall 2013-10-3 1 bifile.bbd 18432 oracle oinstall 2013-10-3 1 demo10.bad 1 oracle oinstall 2013-10-3 1 demo10.ctl 342 oracle oinstall 2013-10-3 1 demo10.log 1948 oracle oinstall 2013-10-3 1 demo11.bad 144 oracle oinstall 2013-10-3 1 demo11.ctl 377 oracle oinstall 2013-10-3 1 demo12.bad 89 oracle oinstall 2013-10-3 1 demo12.ctl 582 oracle oinstall 2013-10-3 1 demo14.bad 80 oracle oinstall 2013-10-3 1 demo14.ctl 441 oracle oinstall 2013-10-4 5 demo15.dat.bak 256 oracle oinstall 2013-10-4 7 demo16.bad 3 oracle oinstall 2013-10-4 9 demo18.ctl 213 oracle oinstall 2013-10-3 1 demo1.bad 1 oracle oinstall 2013-10-3 1 demo1.ctl 177 oracle oinstall 2013-10-3 1 demo2.bad 1 oracle oinstall 2013-10-3 1 demo2.ctl 214 oracle oinstall 2013-10-4 7 demo3.bad 2 oracle oinstall 2013-10-4 7 demo3.ctl 121 oracle oinstall 2013-10-4 8 demo4.bad 18 oracle oinstall 2013-10-3 1 demo5.ctl 137 oracle oinstall 2013-10-3 1 demo6.ctl 175 oracle oinstall 2013-10-3 1 demo8.ctl 184 oracle oinstall 2013-10-3 1 demo9.bad 1 oracle oinstall 2013-10-4 8 demo.txt 13 oracle oinstall 2013-10-3 1 demo11.log 1913 oracle oinstall 2013-10-3 1 demo12.log 2147 oracle oinstall 2013-10-3 1 demo13.ctl 790 oracle oinstall 2013-10-3 1 demo13.log 2289 oracle oinstall 2013-10-3 1 demo14.dat 320 oracle oinstall 2013-10-3 1 demo14.log 2230 oracle oinstall 2013-10-4 5 demo15_bak.dat 332 oracle oinstall 2013-10-4 5 demo15.ctl 442 oracle oinstall 2013-10-4 5 demo15.dat 332 oracle oinstall 2013-10-4 7 demo16.ctl 446 oracle oinstall 2013-10-4 5 demo15.log 2229 oracle oinstall 2013-10-4 7 demo16.dat 324 oracle oinstall 2013-10-4 7 demo16.log 2235 oracle oinstall 2013-10-4 9 demo17.ctl 487 oracle oinstall 2013-10-4 9 demo17.dat 741 oracle oinstall 2013-10-3 1 demo2.log 1648 oracle oinstall 2013-10-4 7 demo3.log 1525 oracle oinstall 2013-10-4 8 demo4.ctl 141 oracle oinstall 2013-10-4 8 demo4.log 1657 oracle oinstall 2013-10-3 1 demo5.log 1560 oracle oinstall 2013-10-3 1 demo6.log 1641 oracle oinstall 2013-10-3 1 demo7.ctl 174 oracle oinstall 2013-10-3 1 demo7.log 1641 oracle oinstall 2013-10-3 1 demo8.log 1640 oracle oinstall 2013-10-3 1 demo9.ctl 290 oracle oinstall 2013-10-3 1 demo9.log 1809 oracle oinstall 2013-10-3 1 log.bbd 547
这不光适用于BLOB,也适用于CLOB.以这种方式使用SQLLDR来加载文本文件的目录会很容易.
将LOB数据加载到对象列.既然知道了如何将数据加载到我们自己创建的一个简单表中,可能会发现,有时需要将数据加载到一 个复杂的表中,其中可能有一个包含LOB的复杂对象类型(列).使用图像功能时这种情况最为常见.图像功能使用一个复杂的对象类型ORDSYS.ORDIMAGE来实现,我们需要告诉SQLLDR如何向其中加载数据.
要把一个LOB加载到一个ORDIMAGE类型的列中,首先必须对ORDIMAGE类型的结构有所了解.在SQL*Plus中使用要加载的一个目标表以及该表上的DESCRIBE,可以发现表中有一个名为IMAGE的ORDSYS.ORDIMAGE列,最终我们想在这一列中加载 IMAGE.SOURCE.LOCALDATA,只有安装并配置好interMedia,项目的例子才能正常工作;否则,数据类型ORDSYS.ORDIMAGE将是一 个未知类型:
jy@JINGYONG> create table image_load 2 (id number, 3 name varchar2(255), 4 image ordsys.ordimage 5 ); 表已创建。 SQL> desc jy.image_load Name Type Nullable Default Comments ----- --------------- -------- ------- -------- ID NUMBER Y NAME VARCHAR2(255) Y IMAGE ORDSYS.ORDIMAGE Y SQL> desc ordsys.ordimage Element Type -------------------- ---------------- SOURCE ORDSYS.ORDSOURCE HEIGHT INTEGER WIDTH INTEGER CONTENTLENGTH INTEGER FILEFORMAT VARCHAR2(4000) CONTENTFORMAT VARCHAR2(4000) COMPRESSIONFORMAT VARCHAR2(4000) MIMETYPE VARCHAR2(4000) INIT FUNCTION COPY PROCEDURE PROCESS PROCEDURE PROCESSCOPY PROCEDURE SETPROPERTIES PROCEDURE CHECKPROPERTIES FUNCTION GETHEIGHT FUNCTION GETWIDTH FUNCTION GETFILEFORMAT FUNCTION GETCONTENTFORMAT FUNCTION GETCOMPRESSIONFORMAT FUNCTION GETMETADATA FUNCTION PUTMETADATA PROCEDURE SETLOCAL PROCEDURE CLEARLOCAL PROCEDURE ISLOCAL FUNCTION GETUPDATETIME FUNCTION SETUPDATETIME PROCEDURE GETMIMETYPE FUNCTION SETMIMETYPE PROCEDURE GETCONTENTLENGTH FUNCTION GETCONTENT FUNCTION GETBFILE FUNCTION DELETECONTENT PROCEDURE GETDICOMMETADATA FUNCTION SETSOURCE PROCEDURE GETSOURCE FUNCTION GETSOURCETYPE FUNCTION GETSOURCELOCATION FUNCTION GETSOURCENAME FUNCTION IMPORT PROCEDURE IMPORTFROM PROCEDURE EXPORT PROCEDURE PROCESSSOURCECOMMAND FUNCTION OPENSOURCE FUNCTION CLOSESOURCE FUNCTION TRIMSOURCE FUNCTION READFROMSOURCE PROCEDURE WRITETOSOURCE PROCEDURE GETPROPERTIES PROCEDURE SQL> desc ordsys.ordsource Element Type -------------------- -------------- LOCALDATA BLOB SRCTYPE VARCHAR2(4000) SRCLOCATION VARCHAR2(4000) SRCNAME VARCHAR2(4000) UPDATETIME DATE LOCAL NUMBER SETLOCAL PROCEDURE CLEARLOCAL PROCEDURE ISLOCAL FUNCTION GETUPDATETIME FUNCTION SETUPDATETIME PROCEDURE SETSOURCEINFORMATION PROCEDURE GETSOURCEINFORMATION FUNCTION GETSOURCETYPE FUNCTION GETSOURCELOCATION FUNCTION GETSOURCENAME FUNCTION GETBFILE FUNCTION IMPORT PROCEDURE IMPORTFROM PROCEDURE EXPORT PROCEDURE GETCONTENTLENGTH FUNCTION GETSOURCEADDRESS FUNCTION GETLOCALCONTENT FUNCTION GETCONTENTINTEMPLOB PROCEDURE DELETELOCALCONTENT PROCEDURE OPEN FUNCTION CLOSE FUNCTION TRIM FUNCTION READ PROCEDURE WRITE PROCEDURE PROCESSCOMMAND FUNCTION
加载这种数据的控制文件可能如下所示:
[oracle@jy ~]$ cat demo20.ctl LOAD DATA INFILE * INTO TABLE image_load REPLACE FIELDS TERMINATED BY ',' (ID, NAME, file_name FILLER, IMAGE column object ( SOURCE column object ( LOCALDATA LOBFILE(file_name) TERMINATED BY EOF NULLIF file_name='NONE' ) ) ) BEGINDATA 1,psu3,psu3.jpg
这里引入了两个新构造:
COLUMN OBJECT:这会告诉SQLLDR这不是一个列名;而是列名的一部分.它不会映射到输入文件中的一个字段,只是用来构建正 确的对象列引用,从而在加载中使用.在前面的文件中有两个列对象标记,其中一个SOURCE嵌入在另一个SOURCE嵌入在 另一个IMAGE中.因此,根据我们的需要,要使用的列名是IMAGE.SOURCE.LOCALDATA.注意,我们没有加载这两个对象类型 的任何其他属性(例如,IMAGE.HEIGHT,IMAGE.CONTENTLENGTH和IMAGE.SOURCE.SRCTYPE).
NULL IF FILE_NAME = ‘NONE’:这会告诉SQLLDR,如果字段FILE_NAME包含单词NONE,则向对象列中加载一个NULL.
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo20.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:38:14 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 1 SQL> select * from jy.image_load ; ID NAME IMAGE --- ------- ----- 1 psu3 <Obje