第15章 数据加载和卸载
在这一章中，我们将讨论数据的加载和卸载，换句话说，也就是如何将数据放入Oracle数据库以及如何从Oracle取出数据。这一章的重点是介绍以下批量数据加载工具：
q SQL*Loader（读作“sequel loader”）：这仍是加载数据的主流方法。
q 外部表（external table）：这是Oracle9i及以上版本中的一个新特性，允许访问操作系统文件，就好像它们是数据库表一样，在Oracle 10g 及以上版本中，甚至还允许抽取表中的数据创建操作系统文件。
在数据加载方面，我们将介绍两个技术：
q 平面文件卸载（flat file unload）：平面文件卸载是定制开发的实现，但是所提供的结果却能移植到其他类型的系统（甚至电子表格）。
q 数据泵卸载（data dump unload）：数据泵是Oracle专业的一种二进制格式，可以通过数据泵工具和外部表访问。
1.1 SQL*Loader
SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式：
q 传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。
q 直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。
利用直接路径加载，你能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还可能避开redo生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。
我们不会介绍SQLLDR的方方面面。要想全面了解有关的详细内容，请参考Oracle Utilities手册，其中有7章专门介绍Oracle 10g 的DQLLDR。在这个手册中，居然用7章介绍SQLLDR，这一点确实很引入注意，因为其他的各个实用程序（如DBVERIFY、DBNEWID和LogMiner）只占了一章或不到一章的篇幅。要了解SQLLDR的语法和所有选项，建议你参考Oracle Utilities手册，因为本书这一章只是要回答参考手册中没有提到的“如何……？”等问题。
需要指出，在Oracle 8.1.6 Release 1及以上版本中，Oracle调用接口（Oracle Call Interface，OCI）允许使用C编写你自己的直接路径加载工具。如果你要执行的操作在SQLLDR中做不到，或者如果需要SQLLDR与你的应用无缝集成，Oracle OCI就很有用。SQLLDR是一个命令行工具（也就是说，这是一个单独的程序）。它并非一个API，例如，不能“从PL/SQL调用”。
如果不带任何输入地从命令行执行SQLLDR，它会提供以下帮助：
| [tkyte@desktop tkyte]$ sqlldr SQL*Loader: Release 10.1.0 .4.0 - Production on Sat Jul 16 10:32:28 2005 Copyright (c) 1982, 2004, 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) ... |
我并不打算解释每个参数技术上的含义，而只是建议你阅读Oracle Utilities手册，特别是Oracle 10g Utilities Guide中的第7章和Oracle9i Utilities Guide中的第4章。本书这一章会展示其中为数不多的一些参数的用法。
要使用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 |
q LOAD DATA （1）：这会告诉SQLLDR要做什么（在这个例子中，则指示要加载数据）。SQLLDR还可以执行CONTINUE_LOAD，也就是继续加载。只有在继续一个多表直接路径加载时才能使用后面这个选项。
q INFILE * （2）：这会告诉SQLLDR所要加载的数据实际上包含在控制文件本身上，如第6～10行所示。也可以指定包含数据的另一个文件的文件名。如果愿意，可以使用一个命令行参数覆盖这个INFILE语句。要当心，命令行选项总会涵盖控制文件设置。
q INTO TABLE DEPT （3）：这会告诉SQLLDR要把数据加载到哪个表中（在这个例子中，数据要加载到DEPT表中）。
q FIELDS TERMINATED BY ‘,’（4）：这会告诉SQLLDR数据的形式应该是用逗号分隔的值。为SQLLDR描述输入数据的方式有数十种；这只是其中较为常用的方法之一。
q (DEPTNO, DNAME, LOC) （5）：这会告诉SQLLDR所要加载的列、这些列在输入数据中的顺序以及数据类型。这是指输入流中数据的数据类型，而不是数据库中的数据类型。在这个例子中，列的数据类型默认为CHAR(255)，这已经足够了。
q BEGINDATA （6）：这会告诉SQLLDR你已经完成对输入数据的描述，后面的行（第7～10行）是要加载到DEPT表的具体数据。
这个控制文件采用了最简单、最常用的格式之一：将定界数据加载到一个表。这一章还会看一些复杂的例子，不过可以从这个简单的控制文件入手，这是一个不错的起点。要使用这个控制文件（名为demo1.ctl），只需创建一个空的DEPT表：
| ops$tkyte@ORA 10G > create table dept 2 ( deptno number(2) constraint dept_pk primary key, 3 dname varchar2(14), 4 loc varchar2(13) 5 ) 6 / Table created. |
并运行以下命令：
| [tkyte@desktop tkyte]$ sqlldr userid=/ control=demo1.ctl SQL*Loader: Release 10.1.0 .4.0 - Production on Sat Jul 16 10:59:06 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Commit point reached - logical record count 4 |
如果表非空，就会收到一个错误消息：
| 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命令，通常会更快地执行，因为它不必物理地删除每一行。
每个加载都会生成一个日志文件。以上这个简单加载的日志文件如下：
| SQL*Loader: Release 10.1.0 .4.0 - Production on Sat Jul 16 10:59:06 2005 Copyright (c) 1982, 2004, 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
Table DEPT: 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 Sat Jul 16 10:59:06 2005 Run ended on Sat Jul 16 10:59:06 2005
Elapsed time was: 00:00:00.15 CPU time was: 00:00:00.03 |
日志文件会告诉我们关于加载的很多方面，从中可以看到我们所用的选项（默认或默认选项）；可以看到读取了多少记录，加载了多少记录等。日志文件指定了所有BAD文件和DISCARD文件的位置，甚至还会告诉我们加载用了多长时间。每个日志文件对于验证加载是否成功至关重要，另外对于诊断错误也很有意义。如果所加载的数据导致SQL错误（也就是说，输入数据是“坏的“，并在BAD文件中建立了记录），这些错误就会记录在这个日志文件中。日志文件中的信息很大程度上不言自明，所以这里不再花时间做过多的解释。
1.1.1 用SQLLDR加载数据的FAQ
现在来回答Oracle数据库中关于用SQLLDR加载数据最常问到的一些问题。
1. 如何加载定界数据？
定价数据（delimited data）即用某个特定字符分隔的数据，可以用引号括起，这是当前平面文件最常见的数据格式。在大型机上，定长、固定格式的文件可能是最可识别的文件格式，但是在UNIX和NT上，定界文件才是“标准“。在这一节中，我们将分析用于加载定界数据的常用选项。
对于定界数据，最常用的格式是逗号分隔值（comma-separated values，CSV）格式。采用这种文件格式，数据中的每个字段与下一个字段用一个逗号分隔。文本串可以用引号括起，这样就允许串本身包含逗号。如果串还必须包含引号，一般约定是使用两个引号（在下面的代码 中，我们将使用““而不是‘’）。要加载定界数据，相应的典型控制文件与前面第一个例子很相似，但是FIELDS TERMINATED BY 子句通常如下指定：
| FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' |
它指定用逗号分隔数据字段，每个字段可以用双引号括起。如果我们把这个控制文件的最后部分修改如下：
| 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时，结果如下：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales Virginia , USA 20 Accounting Va, " USA " 30 Consulting Virginia 40 Finance Virginia |
要特别注意以下几点：
q 部门10中的Virginia.USA：这是因为输入数据是“Virginia.USA”。输入数据字段必须包括在引号里才能保留数据中的逗号。否则，数据中的这个逗号会被认为是字段结束标记，这样就会只加载Virginia，而没有USA文本。
q Va,”USA”：这是因为输入数据是“Va,””USA”””。对于引号括起的串，SQLLDR会把其中“的两次出现计为一次出现。要加载一个包含可选包围字符（enclosure character）的串，必须保证这个包围字符出现两次。
另一种常用的格式是制表符定界数据（tag-delimited data），这是用制表符分隔而不是逗号分割的数据。有两种方法使用TERMINATED BY子句来加载这种数据：
q TERMINATED BY X’ 09’ （使用十六进制格式的制表符；采用ASCII时，制表符为9）
q TERMINATED BY WHITESPACE
这两种方法在实现上有很大差异，下面将会说明。还是用前面的DEPT表，我们将使用以下控制文件加载这个表：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE (DEPTNO, DNAME, LOC) BEGINDATA 10 Sales Virginia |
从字面上不太容易看得出来，不过要知道，在这里各部分数据之间都有两个制表符。这里的数据行实际上是：
| 10/t/tSales/t/tVirginia |
在此/t是普通可识别的制表符转义字符。使用这个控制文件时（包含如前所示的TERMINATED BY WHITESPACE），表DEPT中的数据将是：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales Virginia |
TERMINATED BY WHITESPACE会解析这个串，查找空白符（制表符、空格和换行符）的第一次出现，然后继续查找，直至找到下一个非空白符。因此，解析数据时，DEPTNO会赋给10，后面的两个制表符被认为是空白符，Sales会赋给DNAME等。
另一方面，如果要使用FIELDS TERMINATED BY X’ 09’ ，如以下控制文件所示，这里稍做修改：
| ... FIELDS TERMINATED BY X'09' (DEPTNO, DNAME, LOC ) ... |
可以看到DEPT中加载了以下数据：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales |
在此，一旦SQLLDR遇到一个制表符，就会输出一个值。因此，将10赋给DEPTNO，DNAME得到了NULL，因为在第一个制表符和制表符的下一次出现之间没有数据。Sales赋给了LOC。
这是TERMINATED BY WHITESPACE和TERMINATED BY <character>的有意行为。至于使用哪一种方法更合适，这取决于输入数据以及你要如何解释输入数据。
最后，加载这样的定界数据时，很可能想逃过输入记录中的某些列。例如，你可能加载字段1、3和5，而跳过第2列和第4列。为此，SQLLDR提供了FILLER关键字。这允许你映射一个输入记录中的一列，但不把它放在数据库中。例如，给定DEPT表以及先前的最高一个控制文件，可以修改这个控制文件，使用FILLER关键字正确地加载数据（跳过制表符）：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY x'09' (DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC) BEGINDATA 10 Sales Virginia |
所得到的表DEPT现在如下所示：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales Virginia |
2. 如何加载固定格式数据？
通常会有一个有某个外部系统生成的平面文件，而且这是一个定长文件，其中包含着固定位置的数据（positional data）。例如，NAME字段位于第1～10字节，ADDRESS字段位于地11～35字节等。我们将介绍SQLLDR如何为我们导入这种数据。
这种定宽的固定位置数据是最适合SQLLDR加载的数据格式。要加载这种数据，使用SQLLDR是最快的处理方法，因为解析输入数据流相当容易。SQLLDR会在数据记录中存储固定字节的偏移量和长度，因此抽取某个给定字段相当简单。如果要加载大量数据，将其转换为一种固定位置格式通常是最好的办法。当然，定宽文件也有一个缺点，它比简单的定界文件格式可能要大得多。
要加载定宽的固定位置数据，将会在控制文件中使用POSITION关键字，例如：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1:2), DNAME position(3:16), LOC position(17:29) ) BEGINDATA 10Accounting Virginia , USA |
这个控制文件没有使用FIELDS TERMINATED BY子句；而是使用了POSITION来告诉SQLLDR字段从哪里开始，到哪里结束。关于POSITION子句有意思的是，我们可以使用重叠的位置，可以在记录中来回反复。例如，如果如下修改DEPT表：
| ops$tkyte@ORA 10G > alter table dept add entire_line varchar(29); Table altered. |
并使用以下控制文件：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1:2), DNAME position(3:16), LOC position(17:29), ENTIRE_LINE position(1:29) ) BEGINDATA 10Accounting Virginia , USA |
字段ENTIRE_LINE定义的POSITION(1:29)。这会从所有29字节的输入数据中抽取出这个字段的数据，而其他字段都是输入数据的子串。这个控制文件的输出如下：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- ------------- ----------------------------- 10 Accounting Virginia , USA 10Accounting Virginia , USA |
使用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 |
*指示控制文件得出上一个字段在哪里结束。因此，在这种情况下，（*:16）与(3:16)是一样的。注意，控制文件中可以混合使用相对位置和绝对位置。另外。使用*表示法时，可以把它与偏移量相加。例如，如果DNAME从DEPTNO结束之后的2个字节处开始，可以使用(*+2:16)。在这个例子中，其作用就相当于使用(5:16)。
POSITION子句中的结束位置必须是数据结束的绝对列位置。有时，可能指定每个字段的长度更为容易，特别是如果这些字段是连续的（就像前面的例子一样）。采用这种方式，只需告诉SQLLDR：记录从第1个字节开始，然后指定每个字段的长度就行了。这样我们就可以免于计算记录中的开始和结束偏移量，这个计算有时可能很困难。为此，可以不指定结束位置，而是指定定长记录中各个字段的长度，如下：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1) char(2), DNAME position(*) char(14), LOC position(*) char(13), ENTIRE_LINE position(1) char(29) ) BEGINDATA 10Accounting Virginia , USA |
在此只需告诉SQLLDR第一个字段从哪里开始及其长度。后面的每个字段都从上一个字段结束处开始，并具有指定的长度。直至最后一个字段才需要再次指定位置，因为这个字段又要从记录起始处开始。
3. 如何加载日期？
使用SQLLDR加载日期相当简单，但是看起来这个方面经常导致混淆。你只需在控制文件中使用DATE数据类型，并指定要使用的日期掩码。这个日期掩码与数据库中TO_CHAR和TO_DATE中使用的日期掩码是一样的。SQLLDR会向数据应用这个日期掩码，并为你完成加载。
例如，如果再把DEPT表修改如下：
| ops$tkyte@ORA 10G > alter table dept add last_updated date; Table altered. |
可以用以下控制文件加载它：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME, 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 |
所得到的DEPT表如下所示：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC LAST_UPDA ---------- -------------- ------------- --------- 10 Sales Virginia 01-MAY-00 20 Accounting Virginia 21-JUN-99 30 Consulting Virginia 05-JAN-00 40 Finance Virginia 15-MAR-01 |
就这么简单。只需在控制文件中应用格式，SQLLDR就会为我们完成日期转换。在某些情况想，可能使用一个更强大的SQL函数更为合适。例如，如果你的输入文件包含多种不同格式的日期：有些有时间分量，有些没有；有些采用DD-MON-YYYY格式；有些格式为DD/MM/YYYY；等等。
在下一节中你会了解到如何在SQLLDR中使用函数来解决这些问题。
4. 如果使用函数加载数据？
在这一节中，我们将介绍加载数据时如何使用函数。
一旦你了解了SQLLDR如何构建其INSERT语句，在SQLLDR中使用函数就很容易了。要在SQLLDR脚本中向某个字段应用一个函数，只需块这个函数增加到控制文件中（用两个引号括起）。例如，假设有前面的DEPT表，你想确保所加载的数据都是大写的。可以使用以下控制文件来加载：
| 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 |
数据库中得到的数据如下：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA ------ -------------- ------------- ----------------------------- --------- 10 SALES VIRGINIA 01-MAY-00 20 ACCOUNTING VIRGINIA 21-JUN-99 30 CONSULTING VIRGINIA 05-JAN-00 40 FINANCE VIRGINIA 15-MAR-01 |
可以注意到，只需向一个绑定变量应用UPPER函数就可以很容易地将数据变为大写。要注意，SQL函数可以引用任何列，而不论将函数实际上应用于哪个列。这说明，一个列可以是对两个或更多其他列应用一个函数的结果。例如，如果你想加载ENTIRE_LINE列，可以使用SQL连接运算符。不过，这种情况下这样做稍有些麻烦。现在，输入数据集中有4个数据元素。如果只是向控制文件中如下字符ENTIRE_LINE：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (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 |
就会看到，日志文件中对于每个输入记录出现以下错误：
| Record 1: Rejected - Error on table DEPT, column ENTIRE_LINE. Column not found before end of logical record (use TRAILING NULLCOLS) |
在此，SQLLDR告诉你：没等处理完所有列，记录中就没有数据了。这种情况下，解决方案很简单。实际上，SQLLDR甚至已经告诉了我们该怎么做：这就是使用TRAILING NULLCOLS。这样一来，如果输入记录中不存在某一列的数据，SQLLDR就会为该列绑定一个NULL值。在这种情况下，增加TRAILING NULLCOLS会导致绑定变量：ENTIRE_LINE成为NULL。所以再尝试这个控制文件：
| 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 |
现在表中的数据如下：
| ops$tkyte@ORA 10G > select * from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA ------ -------------- ------------- ----------------------------- --------- 10 SALES VIRGINIA 10SalesVirginia1 /5/2000 01-MAY-00 20 ACCOUNTING VIRGINIA 20AccountingVirginia21 /6/1999 21-JUN-99 30 CONSULTING VIRGINIA 30ConsultingVirginia5 /1/2000 05-JAN-00 40 FINANCE VIRGINIA 40FinanceVirginia15 /3/2001 15-MAR-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语句，所以这样做不仅功能极为强大，而且相当容易。例如，假设你的输入文件有以下格式的日期：
q HH24:MI:SS：只有一个时间；日期默认为SYSDATE。
q DD/MM/YYYY：只有一个日期；时间默认为午夜0点。
q HH24:MI:SS DD/MM/YYYY：日期和时间都要显式提供。
可以使用如下的一个控制文件：
| 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) > 9 then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy') when instr(:last_updated,':') > 0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'dd/mm/yyyy') end" ) BEGINDATA 10,Sales, Virginia ,12:03:03 17/10/2005 20,Accounting, Virginia ,02:23:54 30,Consulting, Virginia ,01:24:00 21/10/2005 40,Finance, Virginia , 17/8/2005 |
可以得到以下结果：
| ops$tkyte@ORA 10G > alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered.
ops$tkyte@ORA 10G > select deptno, dname, loc, last_updated 2 from dept;
DEPTNO DNAME LOC LAST_UPDATED ---------- -------------- ------------- -------------------- 10 SALES VIRGINIA 17-oct-2005 12:03:03 20 ACCOUNTING VIRGINIA 01-jul-2005 02:23:54 30 CONSULTING VIRGINIA 21-oct-2005 01:24:00 40 FINANCE VIRGINIA 17-aug-2005 00:00:00 |
现在会向输入字符串应用3个日期格式中的一个（注意，这里不再加载一个DATE；而只是加载一个串）。CASE函数会查看串的长度和内容，从而确定应该使用哪一个掩码。
有意思的是，你可以编写自己的函数来由SQLLDR调用。这直接应验了可以从SQL调用PL/SQL。
5. 如何加载有内嵌换行符的数据？
过去，如果要加载可能包含换行符的自由格式的数据，这对于SQLLDR来说很成问题。换行符是SQLLDR的默认行结束符，过去对此也提出了一些解决方法，但是灵活性都不够。幸运的是，在Oracle 8.1.6 及以后版本中，我们有了一些新的选择。要加载内嵌有换行符的数据，现在的选择如下：
q 加载数据，其中用非换行符的其他字符来表示换行符（例如，在文本中应该出现换行符的位置上放上串/n），并在加载时使用一个SQL函数用一个CHR(10)替换该文本。
q 在INFILE指令上使用FIX属性，加载一个定长平面文件。
q 在INFILE指令上使用VAR属性，加载一个定宽文件，在该文件使用的格式中，每一行的前几个字节指定了这一行的长度（字节数）。
q 在INFILE指令上使用STR属性，加载一个变宽文件，其中用某个字符序列来表示行结束符，而不是用换行符来表示。
后面的几个小节将分别介绍这些方法。
l 使用一个非换行符的字符
如果你能对如何生成输入数据加以控制，这就是一种很容易的方法。如果创建数据文件时能很容易地转换数据，这种方法就能奏效。其思想是，就数据加载到数据库时对数据应用一个SQL函数，用某个字符串来替换换行符。下面向DEPT表再增加另一个列：
| ops$tkyte@ORA 10G > alter table dept add comments varchar2(4000); Table altered. |
我们将使用这一列来加载文本。下面是一个有内联数据的示例控制文件：
| LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS "replace(:comments,'//n',chr(10))" ) BEGINDATA 10,Sales, Virginia ,This is the Sales/nOffice in Virginia 20,Accounting, Virginia ,This is the Accounting/nOffice in Virginia 30,Consulting, Virginia ,This is the Consulting/nOffice in Virginia 40,Finance, Virginia ,This is the Finance/nOffice in Virginia |
注意，调用中必须使用//n来替换换行符，而不只是/n。这是因为/n会被SQLLDR识别为一个换行符，而且SQLLDR会把它转换为一个换行符，而不是一个两字符的串。利用以上控制文件执行SQLLDR时，DEPT表中将加载以下数据：
| ops$tkyte@ORA 10G > select deptno, dname, comments from dept; DEPTNO DNAME COMMENTS ---------- -------------- ------------------------- 10 SALES This is the Sales Office in Virginia 20 ACCOUNTING This is the Accounting Office in Virginia 30 CONSULTING This is the Consulting Office in Virginia 40 FINANCE This is the Finance Office in Virginia |
l 使用IFX属性
另一种可用的方法是使用FIX属性。如果使用这种方法，输入数据必须出现在定长记录中。每个记录与输入数据集中所有其他记录的长度都相同，即有相同的字节数。对于固定位置的数据，使用FIX属性就特别适合。这些文件通常是定长输入文件。使用自由格式的定界数据时，则不太可能是一个定长文件，因为这些文件通常是变长的（这正是定界文件的关键：每一行不会不必要地过长）。
使用FIX属性时，必须使用一个INFILE子句，因为FIX属性是INFILE的一个选项。另外，如果使用这个选项，数据必须在外部存储，而并非存储在控制文件本身。因此，假设有定长的输入记录，可以使用如下的一个控制文件：
| LOAD DATA INFILE demo.dat "fix 80" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS ) |
这个文件指定了一个输入数据文件（domo.dat），这个文件中每个记录有80字节，这包括尾部的换行符（每个记录最后可能有换行符，也可能没有）。在这种情况下，输入数据文件中的换行符并不是特殊字符。这只是要加载（或不加载）的另一个字符而已。要知道：记录的最后如果有换行符，它会成为这个记录的一部分。为了充分理解这一点，我们需要一个实用程序将文件的内容转储在屏幕上，以便我们看到文件中到底有什么。使用UNIX（或任何Linux版本），利用od就很容易做到，这个程序可以将文件以八进制（和其他格式）转储到屏幕上。我们将使用下面的demo.dat文件。注意以下输入中的第一列实际上是八进制，所以第2行上的数字0000012是一个八进制数，不是十进制数10.由此我们可以知道所查看的文件中有哪些字节。我对这个输出进行了格式化，使得每行显示10个字符（使用-w10），所以0、12、24和36实际上就是0、10、20和30。
| [tkyte@desktop tkyte]$ od -c -w10 -v demo.dat 0000000 1 0 , S a l e s , V 0000012 i r g i n i a , T h 0000024 i s i s t h e 0000036 S a l e s /n O f f i 0000050 c e i n V i r g 0000062 i n i a 0000074 0000106 0000120 2 0 , A c c o u n t 0000132 i n g , V i r g i n 0000144 i a , T h i s i s 0000156 t h e A c c o u 0000170 n t i n g /n O f f i 0000202 c e i n V i r g 0000214 i n i a 0000226 0000240 3 0 , C o n s u l t 0000252 i n g , V i r g i n 0000264 i a , T h i s i s 0000276 t h e C o n s u 0000310 l t i n g /n O f f i 0000322 c e i n V i r g 0000334 i n i a 0000346 0000360 4 0 , F i n a n c e 0000372 , V i r g i n i a , 0000404 T h i s i s t h 0000416 e F i n a n c e /n 0000430 O f f i c e i n 0000442 V i r g i n i a 0000454 0000466 0000500 [tkyte@desktop tkyte]$ |
注意，在这个输入文件中，并没有用换行符（/n）来指示SQLLDRE记录在哪里结束；这里的换行符只是要加载的数据而已。SQLLDR使用FIX宽度（80字节）来得出要读取多少数据。实际上，如果查看输入数据，可以看到，输入文件中提供给SQLLDR的记录甚至并非以/n结束。部门20的记录之前的字符是一个空格，而不是换行符。
既然我们知道了每个记录的长度为80字节，现在就可以用前面有FIX80子句的控制文件来加载这些数据了。完成加载后，可以看到以下结果：
| ops$tkyte@ORA 10G > select '"' || comments || '"' comments from 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 " |
你可能需要“截断“这个数据，因为尾部的空白符会保留。可以在控制文件中使用TRIM内置SQL函数来完成截断。
如果你恰好同时在使用Windows和UNIX，能你很“幸运“，在此需要提醒一句：这两个平台上的行结束标记是不同的。在UNIX上，行结束标记就是/n（SQL中的CHR(10)）。在Windows NT上，行结束标记却是/r/n（SQL中的CHR(13)||CHR(10)）。一般来讲，如果使用FIX方法，就要确保是在同构平台上创建和加载文件（UNIX上创建，UNIX上加载；或者Windows上创建，Windows上加载）。
l 使用VAR属性
要加载有内嵌换行符的数据，另一种方法是使用VAR属性。使用这种格式时，每个记录必须以某个固定的字节数开始，这表示这个记录的总长度。通过使用这种格式，可以加载包含内嵌换行符的变长记录，但是每个记录的开始处必须有一个记录长度字段。因此，如果使用如下的一个控制文件：
| LOAD DATA INFILE demo.dat "var 3" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS ) |
VAR 3指出每个输入记录的前3个字节是输入记录的长度。如果取以下数据文件：
| [tkyte@desktop tkyte]$ cat demo.dat 05510,Sales, Virginia ,This is the Sales Office in Virginia 06520,Accounting, Virginia ,This is the Accounting Office in Virginia 06530,Consulting, Virginia ,This is the Consulting Office in Virginia 05940,Finance, Virginia ,This is the Finance Office in Virginia [tkyte@desktop tkyte]$ |
可以使用该控制文件来加载。在我们的输入数据文件中有4行数据。第一行从055开始，这说明接下来55字节是第一个输入记录。这55字节包括单词Virginia后的结束换行符。下一行从065开始。这一行有65字节的文本，依此类推。使用这种格式数据文件，可以很容易地加载有内嵌换行符的数据。
同样，如果你在使用UNIX和Windows（前面的例子都在UNIX上完成，其中换行符只是一个字符长），就必须调整每个记录的长度字段。在Windows上，前例.dat文件中的长度字段应该是56、66、66和60.
l 使用STR属性
要加载有内嵌换行符的数据，这可能是最灵活的一种方法。通过使用STR属性，可以指定一个新的行结束符（或字符序列）。 就能创建一个输入数据文件，其中每一行的最后有某个特殊字符、换行符不再有“特殊“含义。
我更喜欢使用字符序列，通常会使用某个特殊标记，然后再加一个换行符。这样，在一个文本编辑器或某个实用程序中查看输入数据时，就能很容易地看到行结束符，因为每个记录的最后仍然有一个换行符。STR属性以十六进制指定，要得到所需的具体十六进制串，最容易的方法是使用SQL和UTL_RAW来生成十六进制串。例如，假设使用的是UNIX平台，行结束标记是CHR(10)（换行），我们的特殊标记字符是一个管道符号（|），则可以写为：
| ops$tkyte@ORA 10G > select utl_raw.cast_to_raw( '|'||chr(10) ) from dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(10)) ------------------------------------------------------------------------------- 7C 0A |
由此可知，在UNIX上我们需要使用的STR是X’ 7C 0A ’。
注意 在Windows上，要使用UTL_RAW.CAST_TO_RAW(‘|”||chr(13)||chr(10))。
为了使用这个方法，要有以下控制文件：
| LOAD DATA INFILE demo.dat "str X' 7C 0A '" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS ) |
因此，如果输入数据如下：
| [tkyte@desktop tkyte]$ cat demo.dat 10,Sales, Virginia ,This is the Sales Office in Virginia| 20,Accounting, Virginia ,This is the Accounting Office in Virginia| 30,Consulting, Virginia ,This is the Consulting Office in Virginia| 40,Finance, Virginia ,This is the Finance Office in Virginia| [tkyte@desktop tkyte]$ |
其中，数据文件中的每个记录都以|/n结束，前面的控制文件就会正确地加载这些数据。
l 内嵌换行符小结
关于加载有内嵌换行符的数据，这一节讨论了至少4种方法。在后面的“平面文件卸载“一节中，我们还将看到会使用这里的一种技术，可以在一个通用卸载实用程序使用STR属性来避免与文本中换行符有关的问题。
另外要注意一个问题，我先前已经多次提到，Windows（包括各种版本）上的文本文件可能以/r/n（ASCII 13+ASCII 10，回车/换行）结束。/r是记录的一部分，控制文件必须适应这一点。具体地将，FIX和VAR中的字节数已经STR使用的串必须有所调整。例如，如果取先前的某个.dat文件（目前其中只包含/n），并使用一个ASCII传输工具（默认）将其通过FTP传输到Windows，将各个/n将转换为/r/n。原来UNIX中能工作的控制文件现在却不能加载数据了。这一点你必须当心，建立控制文件时一定要有所考虑。
6. 如果加载LOB？
现在来考虑在LOB的一些方法。这不是一个LONG或LONG RAW字段，而是更可取的数据类型BLOB和CLOB。这些数据类型是Oracle 8.0及以后版本中引入的，如第12章所述，与遗留的LONG和LONG RAW类型相比，它们支持更丰富的接口/功能集。
我们将分析两种加载这些字段的方法：SQLLDR和PL/SQL。除此之外，还可以采用另外一些方法，如Java流、Pro*C和OCI。我们将首先介绍使用PL/SQL加载LOB的方法，然后介绍如何使用SQLLDR加载LOB。
l 通过PL/SQL加载LOB
DBMS_LOB包的入口点为LoadFromFile、LoadBLOBFromFile和LoadCLOBFromFile。通过这些过程，我们可以使用一个BFILE（用于读取操作系统文件）来填充数据库中的BLOB或CLOB。LoadFromFile和LoadBLOBFromFile例程之间没有显著的差别，只不过后者会返回一些OUT参数，指示已经向BLOB列中加载了多少数据。不过，LoadCLOBFromFile例程还提供了一个突出的特性：字符集转换。如果你还记得，第12章中曾讨论过Oracle数据库的某些国家语言支持（NLS）特性，还介绍过字符集的重要性。使用LoadCLOBFromFile时，我们可以告诉数据库：这个文件将以另外某种字符集（不同于数据库正在使用的字符集）来加载，而且要执行必要的字符集转换。例如，可能有一个UTF8兼容的数据库，但是收到的要加载的文件却以WE8ISO8859P1字符集编码，或反之。利用这个函数就能成功地加载这些文件。
注意 DBMS_LOB包中可以过程的全部细节及其完整的输入和输出集，请参考Oraccle9i Oracle Supplied Packages Guide和Oracle 10g Oracle PL/SQL Packages and Types Reference。
要使用这些过程，需要在数据库中创建一个DIRECTORY对象。这个对象允许我们创建并打开BFILE（BFILE指向文件系统上数据库服务器能访问的一个现有文件）。最后一句话中提到：“数据库服务器能访问的……“，这是使用PL/SQL加载LOB时一个要点。DBMS_LOB包完全在服务器中执行。它只能看到服务器能看到的文件系统。特别是，如果你通过网络访问Oracle，DBMS_LOB包将无法看到你的本地文件系统。
所以，我们需要先在数据库中创建一个DIRECTORY对象。这是一个很简单的过程。我们将为这个例子创建两个目录（注意，这些例子都在UNIX环境中执行；你要针对你的操作系统，使用适合的语法来引用目录）：
| ops$tkyte@ORA 10G > create or replace directory dir1 as '/tmp/'; Directory created. ops$tkyte@ORA 10G > create or replace directory "dir2" as '/tmp/'; Directory created. |
注意 Oracle DIRECTORY对象是逻辑目录，这说明，它们是指向操作系统中现有物理目录的指针。CREATE DIRECTORY命令并不是具体在文件系统中创建一个目录，这个操作（物理创建目录）必须单独执行。
执行这个操作的用户要有CREATE ANY DIRECTORY权限。我们之所以要创建两个目录，这是为了展示一个与DIRECTORY对象有关的常见问题，即大小写问题（大写字符还是小写字符）。Oracle创建第一个目录DIR1时，它会以大写存储对象名，因为这是默认设置。在使用dir2的第二个例子中，它创建的DIRECTORY对象保留了名字中原来使用的大小写。稍后使用BFILE对象时将说明这一点的重要性。
下面，我们希望将一些数据加载到BLOB或CLOB中。对此，方法非常简单，例如：
| ops$tkyte@ORA 10G > create table demo 2 ( id int primary key, 3 theClob clob 4 ) 5 / Table created.
ops$tkyte@ORA 10G > host echo 'Hello World!' > /tmp/test.txt ops$tkyte@ORA 10G > declare 2 l_clob clob; 3 l_bfile bfile; 4 begin 5 insert into demo values ( 1, empty_clob() ) 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( 'DIR1', 'test.txt' ); 9 dbms_lob.fileopen( l_bfile ); 10 11 dbms_lob.loadfromfile( l_clob, l_bfile, 12 dbms_lob.getlength( l_bfile ) ); 13 14 dbms_lob.fileclose( l_bfile ); 15 end; 16 / PL/SQL procedure successfully completed. ops$tkyte@ORA 10G > select dbms_lob.getlength(theClob), theClob from demo 2 /
DBMS_LOB.GETLENGTH(THECLOB) THECLOB --------------------------- --------------- 13 Hello World! |
通过分析前面的代码，可见：
q 在第5行和第6行上，我们在表中创建了一行，将CLOB设置为一个EMPTY_CLOB()，并从一个调用获取其值。除了临时LOB外，其余的LOB都“住“在数据库中，如果没有指向一个临时LOB的指针，或者指向一个已经在数据库中的LOB，将无法写至LOB变量。EMPTY_CLOB()不是一个NULL CLOB；而是指向一个空结构的合法指针（非NULL）。它还有一个作用，可以得到一个LOB定位器，指向已锁定行中的数据。如果要选择这个值，而没有锁定底层的行，写数据就会失败，因为LOB在写之前必须锁定（不同于其他结构化数据）。通过插入一行，当然我们也就锁定了这一行。如果我们要修改一个现有的行而不是插入新行，则可以使用SELECT FOR UPDATE来获取和锁定这一行。
q 在第8行上，我们创建了一个BFILE对象。注意，这里DIR1用的是大写，稍后就会看到，这是一个键。这是因为我们向BFILENAME()传入了一个对象的名称，而不是对象本身。因此，必须确保这个名称与Oracle所存储的对象名称大小写匹配。
q 第9行打开了LOB。以便读取。
q 在第11行和第12行上，我们将操作系统文件/tmp/test.txt的完整内容加载到刚插入的LOB定位器。这里使用DBMS_LOB.GETLENGTH()告诉LOADFROMFILE()例程要加载多少字节的BFILE（这里就是要加载全部字节）。
q 最后，在第14行我们关闭了所打开的BFILE，CLOB已加载。
如果前例中试图使用dir1而不是DIR1，可能会遇到以下错误：
| ops$tkyte@ORA 10G > declare ... 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( 'dir1', 'test.txt' ); 9 dbms_lob.fileopen( l_bfile ); ... 15 end; 16 / declare * ERROR at line 1: ORA-22285: non-existent directory or file for FILEOPEN operation ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 9 |
这是因为目录dir1并不存在，只有目录DIR1。如果想使用混合有大小写的目录名，在创建这样的目录四时应该使用带引号的标识符，就像我们创建dir2时一样。这样你就能编写如下所示的代码：
| ops$tkyte@ORA 10G > declare 2 l_clob clob; 3 l_bfile bfile; 4 begin 5 insert into demo values ( 1, empty_clob() ) 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( 'dir2', 'test.txt' ); 9 dbms_lob.fileopen( l_bfile ); 10 11 dbms_lob.loadfromfile( l_clob, l_bfile, 12 dbms_lob.getlength( l_bfile ) ); 13 14 dbms_lob.fileclose( l_bfile ); 15 end; 16 / PL/SQL procedure successfully completed. |
除了从文件例程加载外，还有其他一些方法，利用这些方法也可以使用PL/SQL填充LOB。如果你想加载整个文件，就可以使用DBMS_LOB及其提供的例程，这是到目前为止最容易的方法。如果需要在加载文件的同时处理文件的内容，还可以在BFILE上使用DBMS_LOB.READ来读取数据。如果读取的数据实际上是文本，而不是RAW，那么使用UTL_RAW.CAST_TO_VARCHAR2会很方便。然后你可以使用DBMS_LOB.WRITE或WRITEAPPEND将数据放入一个CLOB或BLOB。
l 通过SQLLDR加载LOB数据
现在我们来分析如何通过SQLLDR向LOB加载数据。对此方法不止一种，但是我们主要讨论两种最常用的方法：
q 数据“内联“在其他数据中。
q 数据外联存储（在外部存储），输入数据包含一个文件名，指示该行要加载的数据在哪个文件中。在SQLLDR术语中，这也称为二级数据文件（secondary data file，SDF）。
先从内联数据谈起。
加载内联的LOB数据。这些LOB通常内嵌有换行符和其他特殊字符。因此，往往会使用“如何加载有内嵌换行符的数据？“一节中详细讨论的4种方法之一来加载这种数据。下面先来修改DEPT表，使COMMENTS列是一个CLOB而不是一个大的VARCHAR2字段：
| ops$tkyte@ORA 10G > truncate table dept; Table truncated.
ops$tkyte@ORA 10G > alter table dept drop column comments; Table altered.
ops$tkyte@ORA 10G > alter table dept add comments clob; Table altered. |
例如，假设有一个数据文件（demo.dat），它有以下内容：
| 10, Sales, Virginia ,This is the Sales Office in Virginia| 20,Accounting, Virginia ,This is the Accounting Office in Virginia| 30,Consulting, Virginia ,This is the Consulting Office in Virginia| 40,Finance, 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 ->"| |
每个记录最后都是一个管道符号（|），后面是行结束标记。部门40的文本比其他部门的文本长得多，有多个换行符、内嵌的引号以及逗号。给定这个数据文件，可以创建一个如下的控制文件：
| LOAD DATA INFILE demo.dat "str X' 7C 0A '" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS char(1000000) ) |
注意 这个例子在UNIX上执行，UNIX平台上行结束标记长度为1字节，因此可以使用以上控制文件中的STR设置。在Windows上，STR设置则必须是’ 7C 0D 0A ’。
要加载这个数据文件，我们在COMMENTS列上指定了CHAR(1000000)，因为SQLLDR默认所有人们字段都为CHAR(255)。CHAR(1000000)则允许SQLLDR处理多达1,000,000字节的输入文本。可以把这个长度值设置为大于输入文件中任何可能文本块的大小。通过查看所加载的数据，可以看到以下结果：
| ops$tkyte@ORA 10G > select comments from dept; COMMENTS ------------------------------------------------------------------------------- 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 or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->
This is the Sales Office in Virginia
This is the Accounting Office in Virginia |
这里可以观察到：原来重复两次的引号不再重复。SQLLDR去除了在此放置的额外的引号。
加载外联的LOB数据。可能要把包含有一些文件名的数据文件加载在LOB中，而不是让LOB数据与结构化数据混在一起，这种情况很常见。这提供了更大程度的灵活性，因为提供给SQLLDR的数据文件不必使用上述的4种方法之一来避开输入数据中的内嵌换行符问题，而这种情况在大量的文本或二进制数据中会频繁出现。SQLLDR称这种额外的数据文件为LOBFILE。
SQLLDR还可以支持加载结构化数据文件（指向另外单独一个数据文件）。我们可能告诉SQLLDR如何从另外这个文件分析LOB数据，这样就可以加载其中的一部分作为结构化数据中的每一行。我认为这种模式的用途很有限（到目前为止，我自己还从来没有见过哪里用到这种方法），在此也不做过多的讨论。SQLLDR把这种外部引用的文件称为复杂二级数据文件（complex secondary data file）。
LOBFILE是一种相对简单的数据文件，旨在简化LOB加载。在LOBFILE中，没有记录的概念，因此换行符不会成为问题，正是这个性质使得LOBFILE与主要数据文件有所区别。在LOBFILE中，数据总是采用以下某种格式：
q 定长字段（例如，从LOBFILE加载字节100到1,000）
q 定界字段（以某个字符结束，或者用某个字符括起）
q 长度/值对，这是一个变长字段
其中最常见的类型是定界字段，实际上就是以一个文件结束符（EOF）结束。一般来讲，可能有这样一个目录，其中包含你想加载到LOB列中的文件，每个文件都要完整地放在一个BLOB中。此时，就可以使用带TERMINATED BY EOF子句的LOBFILE语句。
假设我们有一个目录，其中包含想要加载到数据库中的文件。我们想加载文件的OWNER、文件的TIME_STAMP、文件的NAME以及文件本身。要加载数据的表如下所示：
| ops$tkyte@ORA 10G > create table lob_demo 2 ( owner varchar2(255), 3 time_stamp date, 4 filename varchar2(255), 5 data blob 6 ) 7 / Table created. |
在UNIX上使用一个简单的ls –l来捕获输出（或者在Windows上使用dir/q/n），我们就能生成输入文件，并使用如下的一个控制文件加载（这里使用UNIX平台）：
| LOAD DATA INFILE * REPLACE INTO TABLE LOB_DEMO ( owner position(17:25), time_stamp position(44:55) date "Mon DD HH24:MI", filename position(57:100), data LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA -rw-r--r-- 1 tkyte tkyte 1220342 Jun 17 15:26 classes12.zip -rw-rw-r-- 1 tkyte tkyte 10 Jul 16 16:38 foo.sql -rw-rw-r-- 1 tkyte tkyte 751 Jul 16 16:36 t.ctl -rw-rw-r-- 1 tkyte tkyte 491 Jul 16 16:38 testa.sql -rw-rw-r-- 1 tkyte tkyte 283 Jul 16 16:38 testb.sql -rw-rw-r-- 1 tkyte tkyte 231 Jul 16 16:38 test.sh -rw-rw-r-- 1 tkyte tkyte 235 Apr 28 18:03 test.sql -rw-rw-r-- 1 tkyte tkyte 1649 Jul 16 16:36 t.log -rw-rw-r-- 1 tkyte tkyte 1292 Jul 16 16:38 uselast.sql -rw-rw-r-- 1 tkyte tkyte 909 Jul 16 16:38 userbs.sql |
现在，运行SQLLDR之后检查LOB_DEMO表的内容，会发现以下结果：
| ops$tkyte@ORA 10G > select owner, time_stamp, filename, dbms_lob.getlength(data) 2 from lob_demo 3 / OWNER TIME_STAM FILENAME DBMS_LOB.GETLENGTH(DATA) -------- --------- -------------- ------------------------ tkyte 17-JUN-05 classes12.zip 1220342 tkyte 16-JUL-05 foo.sql 10 tkyte 16-JUL-05 t.ctl 875 tkyte 16-JUL-05 testa.sql 491 tkyte 16-JUL-05 testb.sql 283 tkyte 16-JUL-05 test.sh 231 tkyte 28-APR-05 test.sql 235 tkyte 16-JUL-05 t.log 0 tkyte 16-JUL-05 uselast.sql 1292 tkyte 16-JUL-05 userbs.sql 909 10 rows selected. |
这不光适用于BLOB，也适用于CLOB。以这种方式使用SQLLDR来加载文本文件的目录会很容易。
将LOB数据加载到对象列。既然知道了如何将数据加载到我们自己创建的一个简单表中，可能会发现，有时需要将数据加载到一个复杂的表中，其中可能有一个包含LOB的复杂对象类型（列）。使用图像功能时这种情况最为常见。图像功能使用一个复杂的对象类型ORDSYS.ORDIMAGE来实现。我们需要告诉SQLLDR如何向其中加载数据。
要把一个LOB加载到一个ORDIMAGE类型的列中，首先必须对ORDIMAGE类型的结构有所了解。在SQL*Plus中使用要加载的一个目标表以及该表上的DESCRIBE，可以发现表中有一个名为IMAGE的ORDSYS.ORDIMAGE列，最终我们想在这一列中加载IMAGE.SOURCE.LOCALDATA。只有安装并配置好interMedia，项目的例子才能正常工作；否则，数据类型ORDSYS.ORDIMAGE将是一个未知类型：
| ops$tkyte@ORA 10G > create table image_load( 2 id number, 3 name varchar2(255), 4 image ordsys.ordimage 5 ) 6 / Table created.
ops$tkyte@ORA 10G > desc image_load Name Null? Type ---------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(255) IMAGE ORDSYS.ORDIMAGE
ops$tkyte@ORA 10G > desc ordsys.ordimage Name Null? Type ---------------------------------------- -------- ---------------------------- SOURCE ORDSYS.ORDSOURCE HEIGHT NUMBER(38) WIDTH NUMBER(38) CONTENTLENGTH NUMBER(38) ... ops$tkyte@ORA 10G > desc ordsys.ordsource Name Null? Type ---------------------------------------- -------- ---------------------------- LOCALDATA BLOB SRCTYPE VARCHAR2(4000) SRCLOCATION VARCHAR2(4000) ... |
注意 可以在SQL*Plus中执行SET DESC DEPTH ALL或SET DESC DEPTH <n>一次显示整个层次结构。由于ORDSYS.ORDIMAGE的输出可能有几项的篇幅，所以我打算逐部分地介绍。
加载这种数据的控制文件可能如下所示：
| 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,icons,icons.gif |
这里我引入了两个新构造：
q COLUMN OBJECT：这会告诉SQLLDR这不是一个列名；而是列名的一部分。它不会映射到输入文件中的一个字段，只是用来构建正确的对象列引用，从而在加载中使用。在前面的文件中有两个列对象标记，其中一个（SOURCE）嵌入在另一个（SOURCE）嵌入在另一个（IMAGE）中。因此，根据我们的需要，要使用的列名是IMAGE.SOURCE.LOCALDATA。注意，我们没有加载这两个对象类型的任何其他属性（例如，IMAGE.HEIGHT、IMAGE.CONTENTLENGTH和IMAGE.SOURCE.SRCTYPE）。稍后，我们将介绍如何填充这些属性。
q NULL IF FILE_NAME = ‘NONE’：这会告诉SQLLDR，如果字段FILE_NAME包含单词NONE，则向对象列中加载一个NULL。
一旦已经加载了一个interMedia类型，通常需要使用PL/SQL对已经加载的数据进行后处理，以便interMedia能够处理该数据。例如，对于前面的数据，可能想运行以下代码来正确地为图像设置属性：
| begin for c in ( select * from image_load ) loop c.image.setproperties; end loop; end; / |
SETPROPERTIES 是ORDSYS.ORDIMAGE类型提供的对象方法，它处理图像本身，并用适当的值更新对象的其余属性。
7. 如何从存储过程调用SQLLDR？
这个问题的答案很简单：这是办不到的。SQLLDR不是一个API：它不能调用。SQLLDR是一个命令行程序。你完全可以用Java或C编写一个运行SQLLDR的外部过程，但是这与“调用”SQLLDR是两码事。加载会在另一个会话中发生，它不受你的事务控制。另外，你必须解析所得到的日志文件来确定加载是否成功，以及成功的程度如何（也就是说，由于程序一个错误而导致加载终止之前已经加载了多少行）。我不建议从存储过程调用SQLLDR。
过去，在Oracle9i之前你可以实现你自己的类SQLLDR过程。例如，可以有以下选择：
q 用PL/SQL编写一个微型SQLLDR。它可以使用BFILE来读取二进制数据，或使用UTL_FILE读取文本数据来解析和加载。
q 用Java编写一个微型SQLLDR。与基于PL/SQL的加载工具相比，这可能稍有点复杂，这样能利用许多可用的Java例程。
q 用C编写一个SQLLDR，并作为一个外部过程来调用。
幸运的是，在Oracle9i及以后的版本中，我们可以使用外部表，这不仅能提供SQLLDR的几乎所有功能，另外，还可以做SQLLDR做不到的一些事情。这一章将介绍一个外部表的简单例子，其中将使用外部表来自动执行一个并行直接路径加载。稍后会用更多的篇幅来介绍这个内容。不过，在以上讨论的最后，下面对SQLLDR给出几个警告。
1.1.2 SQLLDR警告
在这一节中，我们将讨论使用SQLLDR时要注意的几个问题。
1. TRUNCATE的工作好像不太一样
SQLLDR的TRUNCATE选项看上去好像与SQL*Plus（或其他如何工具）中的TRUNCATE有所不同。SQLLDR有一个假设，认为你会向表中重新加载同样数目的数据，因此会使用一种扩展形式的TRUNCATE。具体地将，它会执行以下命令：
| truncate table t reuse storage |
REUSE STORAGE选项并不释放已分配的区段，它只是将这些区段标记为“空闲空间”。如果这不是你想要的结果，就应当在执行SQLLDR之前先对表完成截除（truncate）。
2. SQLLDR默认地使用CHAR(255)
默认的输入字段长度为255字符。如果你的字段比这要长，就会将收到一个错误消息：
| Record N: Rejected - Error on table T, column C. Field in data file exceeds maximum length |
这并不是说这个数据无法放在数据库列中；而是说，它指示SQLLDR希望有不少或等于255字节的输入数据，不过稍多一些也会接收。对此解决方案很简单，只需在控制文件中使用CHAR(N)，在此N要足够大，能容纳输入文件中最长的字段长度。
3. 命令行会覆盖控制文件
SQLLDR的许多选项既可以放在控制文件中，也可以在命令行上使用。例如，可以使用INFILE FILENAME，也可以使用SQLLDR…DATA=FILENAME。命令行会覆盖控制文件中的任何选项。不能指望一定会使用控制文件中的选项，因为执行SQLLDR的人可能会通过命令行覆盖这些选项。
1.1.3 SQLLDR小结
在这一节中，我们分析了加载数据的许多方面。在此介绍了每天可能遇到的一些典型问题：加载定界文件、加载定长文件、加载包含图像文件的一个目录，以及在输入数据上使用函数来转换输入等。我们没有详细介绍如何使用直接路径加载工具来加载大量数据；而只是简单地提了一下。我们的目标是回答使用SQLLDR时最常出现而且影响面最广的问题。
1.2 外部表
外部表在Oracle9i Release 1中首次引入。简单地说，利用外部表，我们可以把一个操作系统文件当成是一个只读的数据库表。它们不是“实际”表的替代品，也并非用来取代实际表；而只是用作一种简化加载的工具（在Oracle 10g 中还可以简化数据卸载）。
外部表特性首次出现时，我常常称之为“SQLLDR的替代品”。大多数情况下，这种想法还是对的。既然如此，你可能会奇怪，为什么我们还要在前面花那么多时间来介绍SQLLDR。原因是，SQLLDR的使用历史已久，有相当多的遗留控制文件都以它为基础。SQLLDR仍是一个常用的工具；这也是许多人很了解和一直在使用的工具。我们还处在从使用SQLLDR向外部表转变的中间阶段，所以SQLLDR仍很重要。
许多DBA可能不知道：他们对SQLLDR控制文件的了解在使用外部表时也完全可以用上。学习本章这部分的例子时，你会发现，外部表结合了许多SQLLDR语法和许多SQLLDR技术。
在以下3种情况下，应该选择SQLLDR而不是外部表：
q 必须通过网络加载数据，也就是说，输入文件不在数据库服务器上。外部表要求必须能在数据库服务器上访问输入文件，这是外部表的限制之一。
q 多个用户必须并发地使用相同的外部表来处理不同的输入文件。
q 必须使用LOB类型。外部表不支持LOB。
在考虑这些情况的前提下，通常我强烈建议使用外部表来得到其扩展功能。SQLLDR是一个相对简单的工具，能生成一个INSERT并加载数据。其使用SQL的能力仅限于逐行地调用SQL函数。外部表则不受此限制，可以充分利用所有SQL功能集来加载数据。在我看来，外部表超越SQLLDR的关键功能特性如下：
q 可以使用复杂的WHERE条件有选择地加载数据。尽管SQLLDR有一个WHEN子句用来选择要加载的行，但是你只能使用AND表达式和执行相等性比较的表达式，在WHEN子句中不能使用区间（大于、小于），没有OR表达式，也没有IS NULL等。
q 能够合并（MERGE）数据。可以取一个填满数据的操作系统文件，并由它更新现有的数据库记录。
q 能执行高效的代码查找。可以将一个外部表联结到另一个数据库表作为加载过程的一部分。
q 使用INSERT更容易地执行多表插入。从Oracle9i开始，通过使用复杂的WHEN条件，可以用一个INSERT语句插入一个或多个表。尽管SQLLDR也可以加载到多个表中，但是相应的语法相当复杂。
q 对于开发新手来说，学习曲线更短。SQLLDR作为“另外一种工具”，除了学习编程语言、开发工具、SQL语句等之外，还要另外学习。但另一方面，只要开发人员懂SQL，就可以直接将这些知识应用到批量数据加载，而不必学习一个新工具（SQLLDR）。
记住以上几点，下面来看如何使用外部表。
1.2.1 建立外部表
作为外部表的首次简单展示，首先再来运行前面的SQLLDR例子，向DEPT表中加载批量数据。你可能已经想不起来了，所以下面再次列出我们所用的简单控制文件，如下：
| 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 |
目前，作为起步，最容易的方法是使用这个现有的遗留控制文件来提供外部表的定义。以下SQLLDR命令会为我们的外部表生成CREATE TABLE语句：
| [tkyte@desktop tkyte]$ sqlldr / demo1.ctl external_table=generate_only SQL*Loader: Release 10.1.0 .4.0 - Production on Sat Jul 16 17:34:51 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. [tkyte@desktop tkyte]$ |
EXTERNAL_TABLE参数有以下3个值：
q NOT_USED：其含义不言自明，这是默认值。
q EXECUTE：这个值说明SQLLDR不会生成并执行一个SQL INSERT语句；而是会创建一个外部表，并使用一个批量SQL语句来加载。
q GENERATE_ONLY：这个值使得SQLLDR并不具体加载任何数据，而只是会生成所执行的SQL DDL和DML语句，并放到它创建的日志文件中。
警告 DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。
使用GENERATE_ONLY时，可以在demo.log文件中看到以下内容：
| CREATE DIRECTORY statements needed for files ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/tkyte' |
我们可能会看到日志文件中有一个CREATE DIRECTORY语句（也可能看不到）。在生成外部表脚本期间，SQLLDR连接到数据库，并查询数据字典来查看是否已经存在合适的目录。在这个例子中，由于没有合适的目录，所以SQLLDR为我们生成一个CREATE DIRECTORY语句。接下来，它为外部表生成CREATE TABLE语句：
| CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT" ( "DEPTNO" NUMBER(2), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ) |
SQLLDR已经登录到数据库；只有这样它才知道这个外部表定义中要用的具体数据类型（例如，DEPTNO是一个NUMBER(2)）。SQLLDR根据数据字典来确定这些数据类型。接下来，我们来看这个外部表定义开始处的内容：
| ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 |
ORGANIZATION EXTERNAL子句告诉Oracle：这不是一个“正常”表。我们以前在第10章讨论IOT时曾经见过这个子句。目前有3种组织类型：HEAP对应“正常”表，INDEX对应IOT，EXTERNAL对应外部表。余下的文本则告诉Oracle有关这个外部表的更多信息。ORACEL_LOADER类型是目前支持的两种类型之一（Oracle9i中只支持这一种类型）。另一种类型是ORACLE_DATAPUMP，这是Oracle 10g 及以上版本中Oracle的专用数据泵格式。我们将在后面介绍数据卸载一节中讨论这个类型，这种格式不仅可以用于加载数据，也可以卸载数据。外部表可以用于创建一个数据泵格式文件，再读取这个文件。
下一部分是外部表的ACCESS PARAMETERS部分。在此我们告诉数据库如何处理这个输入文件。看到这个描述时，应该注意到，这与SQLLDR控制文件非常相似；这绝非偶然。在大多数情况下，SQLLDR和外部表使用的语法都很相似。
| ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' LOGFILE 'demo1.log_xt' READSIZE 1048576 SKIP 7 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM REJECT ROWS WITH ALL NULL FIELDS ( "DEPTNO" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "DNAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "LOC" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ) ) |
这些访问参数显示了如何建立一个外部表，使之能像SQLLDR一样几乎以同样的方式处理文件：
q RECORDS：记录默认以换行符结束，SQLLDR中的记录就是如此。
q BADFILE：在刚创建的目录中建立了一个坏文件（无法处理的记录都记录到这个文件中）。
q LOGFILE：在当前的工作目录中记录了一个等价于SQLLDR日志文件的日志文件。
q READSIZE：这是Oracle读取输入数据文件所用的默认缓冲区。在这里是1MB。如果采用占用服务器模式，这个内存来自PGA，如果采用共享服务器模式，则来自SGA。它用于缓存输入数据文件中对应一个会话的信息（参见第4章，其中讨论了PAG和SGA内存）。如果你在使用共享服务器，要记住一点：内存从SGA分配。
q SKIP 7：在确定了应该跳过输入文件中的多少记录。你可能会问：“为什么有’skip 7’ ？“是这样，在这个例子中我们使用了INFILE *；使用SKIP 7就是跳过控制文件本身来得到内嵌的数据。如果没有使用INFILE *，就根本不会有SKIP子句。
q FIELDS TERMINATED BY：这与控制文件中的用法一样。不过，外部表没有增加LDRTRIM，这代表LoaDeR TRIM。这是一种截断模式，模拟了SQLLDR截断数据的默认做法。还有另外一些选项，包括LRTRIM、LTRIM和RTRIM，表示左截断/右截断空白符；NOTRIM表示保留所有前导/尾随的空白符。
q REJECT ROWS WITH ALL NULL FIELDS：这导致外部表会在坏文件中记录所有全空的行，而且不加载这些行。
q 列定义本身：这是有关所期望输入数据值的元数据。它们是所加载数据文件中的字符串，长度最多可达255个字符（SQLLDR的默认大小），以逗号（,）结束，（还可以选择用引号括起来）。
注意 要全面了解使用外部表时可用的所有选项，请参考Oracle Utilities Guide手册。这本手册中有一节专门讨论外部表。Oracle SQL Reference Guide手册提供了基本语法，但是不包括ACCESS PARAMETERS部分的细节。
最后，我们来看外部表定义中的LOCATION部分：
| location ( 'demo1.ctl' ) ) REJECT LIMIT UNLIMITED |
这告诉Oracle所加载文件的文件名，在这里就是demo1.ctl，因为我们在原控制文件中使用了INFILE *。控制文件中的下一条语句是默认的INSERT，可以用于从外部表本身加载表：
| INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO DEPT ( DEPTNO, DNAME, LOC ) SELECT "DEPTNO", "DNAME", "LOC" FROM "SYS_SQLLDR_X_EXT_DEPT" |
如果可能，这会执行一个逻辑上与直接路径加载等价的操作（假设可以遵循APPEND提示；如果存在触发器或外键约束，可能不允许发生直接路径操作）。
最后，在日志文件中，我们会看到一些语句，这些语句可以用于删除加载完成之后SQLLDR我我们创建的对象：
| statements to cleanup objects created by previous statements: ------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_DEPT" DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 |
这就可以了。如果取这个日志文件，并在适当的地方插入/，使之成为一个合法的SQL*Plus脚本，就万事俱备了；也可能还不行，这取决于当前的权限。例如，假设我登录的模式有CREATE ANY DIRECTORY权限，或者能READ访问一个现有的目录，则可能观察到一个错误：
| ops$tkyte@ORA 10G > INSERT /*+ append */ INTO DEPT 2 ( 3 DEPTNO, 4 DNAME, 5 LOC 6 ) 7 SELECT 8 "DEPTNO", 9 "DNAME", 10 "LOC" 11 FROM "SYS_SQLLDR_X_EXT_DEPT" 12 / INSERT /*+ append */ INTO DEPT * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04063: unable to open log file demo1.log_xt OS error Permission denied ORA-06512: at "SYS.ORACLE_LOADER", line 19 ORA-06512: at line 1 |
初看上去好像不对劲。我作为TKYTE登录到操作系统，我登录的目录是/home/tkyte，而且我拥有这个目录，所以我当然能写这个目录（毕竟，我在那里创建了SQLLDR日志文件）。为什么会发生错误呢？发生了什么？现在的情况是，外部表代码在Oracle服务器软件中运行（在我的专业或共享服务器上）。试图读取输入数据文件的进程是Oracle软件所有者，而不是我的账户。试图创建日志文件的进程也是Oracle软件所有者，而不是我的账户。显然，Oracle没有必要的权限来写我的目录，因此，对外部表的访问会失败。这一点很重要。要读一个表，运行数据库的账户（Oracle软件所有者）必须能做下面的事情：
q 读我们指向的文件。在UNIX中，这说明Oracle软件所有者必须对指向这个文件的所有目录路径有读和执行权限。在Windows中，Oracle软件所有者必须能读该文件。
q 写日志文件目录（即要写日志文件的目录；或者绕过日志文件生成，但是一般来讲，不建议这样做）。实际上，如果已经存在日志文件，Oracle软件所有者必须能写现有的文件。
q 写所指定的如何坏文件，就像日志文件一样。
再回到前面的例子，以下命令使得Oracle能写我的目录：
| ops$tkyte@ORA 10G > host chmod a+rw . |
警告 这个命令实际上会使所有人都能写我的目录！这只是一个演示；通常我会使用Oracle软件所有者自己拥有的一个特殊目录来做这个工作。
接下来，再运行INSERT语句：
| ops$tkyte@ORA 10G > l 1 INSERT /*+ append */ INTO DEPT 2 ( 3 DEPTNO, 4 DNAME, 5 LOC 6 ) 7 SELECT 8 "DEPTNO", 9 "DNAME", 10 "LOC" 11* FROM "SYS_SQLLDR_X_EXT_DEPT"
ops$tkyte@ORA 10G > / 4 rows created.
ops$tkyte@ORA 10G > host ls -l demo1.log_xt -rw-r--r-- 1 ora 10g ora 10g 578 Jul 17 10:45 demo1.log_xt |
可以看到，这一次访问文件时，我成功地加载了4行，并创建了日志文件，另外实际上这个日志文件有“Oracle“拥有，而不属于我的操作系统账户。
1.2.2 处理错
理想世界中是没有错误的。输入文件中的数据如果是理想的，就会全部正确地加载。这几乎是不可能的。那么，如何跟踪加载过程的错误呢？
最常用的方法是使用BADFILE选项。Oracle会在这个坏文件中记录所有未能处理的记录。例如，如果我们的控制文件包含一个DEPTNO ‘ABC’的记录，这个记录会失败，最后出现在坏文件中，因为’ABC’无法转换为一个数字。我们将在下面的例子中展示这一点。
首先，将下面一行添加到demo1.ctl中，作为最后一行（这会向输入添加一行无法加载的数据）：
| ABC,XYZ,Hello |
接下来，运行以下命令，来证明demo1.bad文件尚不存在：
| ops$tkyte@ORA 10G > host ls -l demo1.bad ls: demo1.bad: No such file or directory |
然后查询外部表来显示内容：
| ops$tkyte@ORA 10G > select * from SYS_SQLLDR_X_EXT_DEPT; DEPTNO DNAME LOC ---------- -------------- ------------- 10 Sales Virginia 20 Accounting Virginia 30 Consulting Virginia 40 Finance Virginia |
现在，我们发现存在这个坏文件，而且可以获取其内容：
| ops$tkyte@ORA 10G > host ls -l demo1.bad -rw-r--r-- 1 ora 10g ora 10g 14 Jul 17 10:53 demo1.bad ops$tkyte@ORA 10G > host cat demo1.bad ABC,XYZ,Hello |
但是，如何通过程序来检查这些坏记录以及生成的日志呢？幸运的是，利用另一个外部表就能很容易地做到。假设我们建立了这个外部表：
| ops$tkyte@ORA 10G > create table et_bad 2 ( text1 varchar2(4000) , 3 text2 varchar2(4000) , 4 text3 varchar2(4000) 5 ) 6 organization external 7 (type oracle_loader 8 default directory SYS_SQLLDR_XT_TMPDIR_00000 9 access parameters 10 ( 11 records delimited by newline 12 fields 13 missing field values are null 14 ( text1 position(1:4000), 15 text2 position(4001:8000), 16 text3 position(8001:12000) 17 ) 18 ) 19 location ('demo1.bad') 20 ) 21 / Table created. |
这是一个可以读取任何文件而不会遭遇数据类型错误的表（只要文件中的行少于12,000个字符）。如果行多于12,000个字符，可以再增加更多的文本列来容纳它们。
可以通过一个简单的查询看到被拒绝的记录：
| ops$tkyte@ORA 10G > select * from et_bad; TEXT1 TEXT2 TEXT3 --------------- --------------- --------------- ABC,XYZ,Hello |
COUNT(*)会告诉我们有多少记录被拒绝。根据与这个外部表相关的日志文件，可以创建另一个外部表，它能告诉我们为什么这些记录被拒绝。不过，我们想更进一步，使得这成为一个可重复的过程。原因是：如果使用外部表时没有出现错误，坏文件不会“置空“。所以，如果有一些现有的坏文件，其中包含一些数据，尽管这一次使用外部表时没有生成任何错误，但是看到坏文件中原有的数据，我们可能会被误导，误以为外部表中存在错误。
我过去采用过3种方法来解决这个问题：
q 使用UTL_FILE，并重置坏文件，实际上，就是以写模式打开坏文件，再将其关闭，这样就能清除坏文件中原有的数据。
q 使用UTL_FILE重命名现有的坏文件，保留其内容，同时允许创建一个新的坏文件。
q 在坏文件（和日志文件）名中加入PID。我们将在后面的“多用户问题“一节中介绍这个方法。
采用这些方法，我们就能区别坏文件中的坏记录是我们刚才生成的，还是这个文件本身以前版本中留下来的（这对我们来说没有意义）。
| ALTER TABLE T PROJECT COLUMN REFERENCED|ALL |
| 这一节前面的COUNT(*)使我想到Oracle 10g 中的一个新特性：只访问外部文件中在查询中引用的字段来优化外部表访问。也就是说，如果外部表定义为有100个数字字段，但是你只选择了其中一个字段，可以指示Oracle绕过其他99个串转换为数字的过程。听上去很不错，但是可能导致从每个查询返回不同数目的行。假设外部表中有100行数据。C1列的所有行都是“合法“的，都可以转换为一个数字。而C2列中的所有数据都不是”合法“的，它们都不能转换为一个数字。如果从这个外部表选择C1，会返回100行。但是如果从这个外部表选择C2，则会得到0行。 必须显式地启用这个优化，而且你要考虑这样使用是否“安全“（只有对你的应用及其处理有足够的了解，才能回答这个”是否安全？“的问题）。还是前面的例子，增加一行坏数据，可以想见，查询外部表时会看到以下输出： ops$tkyte@ORA 10G > select dname 2 from SYS_SQLLDR_X_EXT_DEPT 3 / DNAME -------------- Sales Accounting Consulting Finance
ops$tkyte@ORA 10G > select deptno 2 from SYS_SQLLDR_X_EXT_DEPT 3 / DEPTNO ---------- 10 20 30 40 我们知道，“坏”记录已经记入BADFILE。但是，如果只是ALTER外部表，并告诉Oracle只“投影”（处理）所引用的列，如下： ops$tkyte@ORA 10G > alter table SYS_SQLLDR_X_EXT_DEPT 2 project column referenced 3 / Table altered.
ops$tkyte@ORA 10G > select dname 2 from SYS_SQLLDR_X_EXT_DEPT 3 / DNAME -------------- Sales Accounting Consulting Finance XYZ
ops$tkyte@ORA 10G > select deptno 2 from SYS_SQLLDR_X_EXT_DEPT 3 / DEPTNO ---------- 10 20 30 40 从各个查询会得到不同数目的行。输入文件中每一行记录的DNAME字段都是合法的，但是DEPTNO列则不然。如果没有获取DEPTNO列，就不会拒绝DEPTNO列非法的这个坏记录，所以结果集有显著改变。 |
1.2.3 使用外部表加载不同的文件
通常需要在一段时期内使用一个外部表从不同名的文件加载数据。也就是说，我们现在必须加载file1.dat，下一周再加载file2.dat，等等。到目前为止，我们一直只是从一个固定的文件名（demo1.dat）加载。如果随后需要从另一个文件（demo2.dat）加载会怎么样呢？
幸运的是，这很容易满足。可以用ALTER TABLE命令重新指示外部表的位置设置：
| ops$tkyte@ORA 10G > alter table SYS_SQLLDR_X_EXT_DEPT 2 location( 'demo2.dat' ); Table altered. |
仅此而已。对该外部表的下一个查询就会访问文件demo2.dat。
1.2.4 多用户问题
在这一节的引言中，我指出过去3种情况下外部表可能没有SQLLDR那么有用。其中之一就是一种特定的多用户问题。我们刚才看到了如果改变一个外部表的位置，如果使之读取文件2 而不是文件1，等等。如果多个用户都试图并发地使用这个外部表，而在各个会话中将其指向不同的文件，就会出现这个多用户问题。
这是不允许的。在任何给定的时刻，外部表会指向一个文件（或一组文件）。如果我登录后，将表修改为指向文件1，而你几乎同时做了同样的事情，如何我们都查询这个表，就会访问同一个文件。
一般都不会遇到这个问题。外部表不是用来取代“数据库表“；它们只是一种加载数据的方法，你不能过分倚重外部表，把它们作为你的应用的一部分频繁使用。外部表通常只是DBA或开发人员用来加载信息的一个工具，可以一次性地加载，或者按周期复发加载（类似于数据仓库加载）。如果DBA要使用同一个外部表向数据库中加载10个文件，就不应该顺序地加载它们，也就是说，将外部文件指向文件1，并处理，再指向文件2，并处理，如此继续。而应该将外部表指向这两个文件，让数据库来处理：
| ops$tkyte@ORA 10G > alter table SYS_SQLLDR_X_EXT_DEPT 2 location( 'file1.dat', 'file2.dat') 3 / Table altered. |
如果需要“并行处理“，这是完全可以做到的，数据库已经有相应的内置功能，这在上一章已经介绍过。
所以，多用户问题只可能是：两个会话试图几乎同时修改文件位置。不过这只是一种需要当心的可能情况，而我不认为你真的会经常遇到这种情况。
另一个多用户问题与坏文件名和日志文件名有关。如果有多个会话在并发地查看同一个外部表，或者在使用并行处理（从某种程度上讲是一个多用户情况），会怎么样呢？如果能按会话聚集这些文件，那该多好，幸运的是，确实可以这样做。可以在文件名中结合以下特殊串：
q %P：PID。
q %a：并行执行服务器代理ID。为并行执行服务器指定了数字001、002、003等。
采用这种方式，每个会话都会生成自己的坏文件和日志文件。例如，如果你在先前的CREATE TABLE命令中使用以下BADFILE语法：
| RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1_%p.bad' LOGFILE 'demo1.log_xt' |
就会看到如下命名的一个文件：
| $ ls *.bad demo1_7108.bad |
不过，如果时间长了，还是可能遇到问题。大多数操作系统上都会重用PID。所以前面所列的处理错误的技术还是很有用，你要重置你的坏文件；或者如果已经存在坏文件，而且你认为这会带来问题，则要对它重命名。
1.2.5 外部表小结
在这一节中，我们分析了外部表。这是Oracle9i及以后版本中的一个新特性，从很大程度上讲，它可以取代SQLLDR。我们分析了使用外部表的最快捷的方法：使用SQLLDR来转换以前使用的控制文件。这里还展示了通过坏文件检测和处理错误的一些技术，最后，我们讨论了有关外部表的一些多用户问题。
下面进入这一章最后一节，我们将讨论如何从数据库卸载数据。
1.3 平面文件卸载
有一件事SQLLDR做不了，而且Oracle对此没有提供任何命令行工具，这就是以SQLLDR或者其他程序可以理解的格式卸载数据。要把数据从一个系统移动到另一个系统，如果没有使用EXP/IMP或EXPDP/IMPDP（用于取代EXP和IMP的新数据泵），平面卸载就很有用。尽管使用EXP(DP)/IMP(DP)可以很好地将数据从一个系统移到另一个系统，但要求两个系统都是Oracle。
注意 HTML DB提供了一个数据导出特性，作为SQL Workshop的一部分。你可以很容易地以一种CSV格式导出信息。这对于几MB的信息可以很好地工作，但是不适用于数十MB（或更多）的数据。
我们将开发一个很小的PL/SQL实用程序，用来以一种SQLLDR友好的格式在服务器上卸载数据。另外，Ask Tom网站（http://asktom.oracle.com/~tkyte/flat/index.html）上还提供了用Pro*C和SQL*Plus编写的等价工具。这个PL/SQL实用程序在大多数小规模情况下可以很好地工作，但是使用Pro*C可以得到更好的性能。如果你需要在客户机上生成文件，而不是在服务器上（PL/SQL就会在服务器上创建文件），则Pro*C和SQL*Plus也很有用。
我们创建的包的规范如下：
| ops$tkyte@ORA 10G > create or replace package unloader 2 AUTHID CURRENT_USER 3 as 4 /* Function run -- unloads data from any query into a file 5 and creates a control file to reload that 6 data into another table 7 8 p_query = SQL query to "unload". May be virtually any query. 9 p_tname = Table to load into. Will be put into control file. 10 p_mode = REPLACE|APPEND|TRUNCATE -- how to reload the data 11 p_dir = directory we will write the ctl and dat file to. 12 p_filename = name of file to write to. I will add .ctl and .dat 13 to this name 14 p_separator = field delimiter. I default this to a comma. 15 p_enclosure = what each field will be wrapped in 16 p_terminator = end of line character. We use this so we can unload 17 and reload data with newlines in it. I default to 18 "|/n" (a pipe and a newline together) and "|/r/n" on NT. 19 You need only to override this if you believe your 20 data will have that sequence in it. I ALWAYS add the 21 OS "end of line" marker to this sequence, you should not 22 */ 23 function run( p_query in varchar2, 24 p_tname in varchar2, 25 p_mode in varchar2 default 'REPLACE', 26 p_dir in varchar2, 27 p_filename in varchar2, 28 p_separator in varchar2 default ',', 29 p_enclosure in varchar2 default '"', 30 p_terminator in varchar2 default '|' ) 31 return number; 32 end; 33 / Package created. |
注意这里使用了AUTHID CURRENT_USER。这样一来，这个包就可以在数据库上只安装一次，可由任何人用来卸载数据。要卸载数据，只要求一点：对所卸载的表要有SELECT权限，另外对这个包有EXECUTE权限。如果这里没有使用AUTHID CURRENT_USER，则需要这个包的所有者在要卸载的所有表上都有直接的SELECT权限。
注意 SQL会以这个例程的调用者的权限执行。不过，所有PL/SQL调用都会以所调用例程定义者的权限运行；因此，对于具有这个包执行权限的所有人，都隐含地允许他使用UTL_FILE写至一个目录。
包体如下。我们使用UTL_FILE来写一个控制文件和一个数据文件。DBMS_SQL用于动态地处理所有查询。我们在查询中使用了一个数据类型：VARCHAR2(4000)。这说明，如果LOB大于4,000字节，就不能使用这个方法来卸载LOB。不过，只需使用DBMS_LOB.SUBSTR，我们就可以使用这个方法卸载任何最多4,000字节的LOB。另外，由于我们用一个VARCHAR2作为惟一的输出数据类型，所以可以处理长度最多2,000字节的RAW（4,000个十六进制字符），除了LONG RAW和LOB外，这对其他类型都足够了。另外，如果查询引用了一个非标量属性（一个复杂的对象类型，嵌套表等），则不能使用这个简单的实现。以下是一个90%可用的解决方案，这说明90%的情况下它都能解决问题：
| ops$tkyte@ORA 10G > create or replace package body unloader 2 as 3 4 5 g_theCursor integer default dbms_sql.open_cursor; 6 g_descTbl dbms_sql.desc_tab; 7 g_nl varchar2(2) default chr(10); 8 |
以上是这个包体中使用的一些全局变量。全局游标打开一次，即第一次引用这个包时打开，它会一起打开，直到我们注销。这就不用每次调用这个包时都要得到一个新游标，从而避免相应的开销。G_DESCTBL是一个PL/SQL表，将保存DBMS_SQL.DESCRIBE调用的输出。G_NL是一个换行符。在需要内嵌有换行符的串中会使用这个变量。我们无需针对Windows调整这个变量，UTL_FILE会看到字符串中的CHR(10)，并自动为我们将其转换为一个回车/换行符。
接下来，我们使用了一个很小的便利函数，它能将字符转换为一个十六进制数。为此使用了内置函数：
| 9 10 function to_hex( p_str in varchar2 ) return varchar2 11 is 12 begin 13 return to_char( ascii(p_str), 'fm0x' ); 14 end; 15 |
最后，我们又创建了另一个便利函数IS_WINDOWS，它会返回TRUE或FALSE，这取决于我们所用的是否是Windows平台，如果在Windows平台上，行结束符就是一个两字符的串，而大多数其他平台上的行结束符只是单字符。我们使用了内置DBMS_UTILITY函数：GET_PARAMETER_VALUE，可以用这个函数读取几乎所有参数。我们获取了CONTROL_FILES参数，并查找其中是否存在/，如果有，则说明在Windows平台上：
| 16 function is_windows return boolean 17 is 18 l_cfiles varchar2(4000); 19 l_dummy number; 20 begin 21 if (dbms_utility.get_parameter_value( 'control_files', l_dummy, l_cfiles )>0) 22 then 23 return instr( l_cfiles, '/' ) > 0; 24 else 25 return FALSE; 26 end if; |
注意 IS_WINDOWS函数依赖于CONTROL_FILES参数中使用了/。要记住，其中也有可能使用/，但这极为少见。
下面的过程会创建一个控制文件来重新加载卸载的数据，这里使用了DBMS_SQL.DESCRIBE_COLUMN生成的DESCRIBE表。它会为我们处理有关操作系统的细节，如操作系统是否使用回车/换行符（用于STR属性）：
| 28 29 procedure dump_ctl( p_dir in varchar2, 30 p_filename in varchar2, 31 p_tname in varchar2, 32 p_mode in varchar2, 33 p_separator in varchar2, 34 p_enclosure in varchar2, 35 p_terminator in varchar2 ) 36 is 37 l_output utl_file.file_type; 38 l_sep varchar2(5); 39 l_str varchar2(5) := chr(10); 40 41 begin 42 if ( is_windows ) 43 then 44 l_str := chr(13) || chr(10); 45 end if; 46 47 l_output := utl_file.fopen( p_dir, p_filename || '.ctl', 'w' ); 48 49 utl_file.put_line( l_output, 'load data' ); 50 utl_file.put_line( l_output, 'infile ''' || 51 p_filename || '.dat'' "str x''' || 52 utl_raw.cast_to_raw( p_terminator || 53 l_str ) || '''"' ); 54 utl_file.put_line( l_output, 'into table ' || p_tname ); 55 utl_file.put_line( l_output, p_mode ); 56 utl_file.put_line( l_output, 'fields terminated by X''' || 57 to_hex(p_separator) || 58 ''' enclosed by X''' || 59 to_hex(p_enclosure) || ''' ' ); 60 utl_file.put_line( l_output, '(' ); 61 62 for i in 1 .. g_descTbl.count 63 loop 64 if ( g_descTbl(i).col_type = 12 ) 65 then 66 utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 67 ' date ''ddmmyyyyhh24miss'' '); 68 else 69 utl_file.put( l_output, l_sep || g_descTbl(i).col_name || 70 ' char(' || 71 to_char(g_descTbl(i).col_max_len*2) ||' )' ); 72 end if; 73 l_sep := ','||g_nl ; 74 end loop; 75 utl_file.put_line( l_output, g_nl || ')' ); 76 utl_file.fclose( l_output ); 77 end; 78 |
这是一个简单的函数，会返回一个加引号的串（使用所选择的包围字符作为引号）。注意，串不只是包含字符，倘若串中还存在包围字符，还会把包围字符重复两次，从而保留这些包围字符：
| 79 function quote(p_str in varchar2, p_enclosure in varchar2) 80 return varchar2 81 is 82 begin 83 return p_enclosure || 84 replace( p_str, p_enclosure, p_enclosure||p_enclosure ) || 85 p_enclosure; 86 end; 87 |
下面是主函数RUN。因为这个函数相当大，我会一边列出函数一边解释：
| 88 function run( p_query in varchar2, 89 p_tname in varchar2, 90 p_mode in varchar2 default 'REPLACE', 91 p_dir in varchar2, 92 p_filename in varchar2, 93 p_separator in varchar2 default ',', 94 p_enclosure in varchar2 default '"', 95 p_terminator in varchar2 default '|' ) return number 96 is 97 l_output utl_file.file_type; 98 l_columnValue varchar2(4000); 99 l_colCnt number default 0; 100 l_separator varchar2(10) default ''; 101 l_cnt number default 0; 102 l_line long; 103 l_datefmt varchar2(255); 104 l_descTbl dbms_sql.desc_tab; 105 begin |
我们将NLS_DATE_FORMAT保存到一个变量中，从而在将数据转储到磁盘上时可以把它改为一种保留日期和时间的格式。采用这种方式，我们会保留日期的时间分量。然后建立一个异常块，从而在接收到错误时重置NLS_DATE_FORMAT：
| 106 select value 107 into l_datefmt 108 from nls_session_parameters 109 where parameter = 'NLS_DATE_FORMAT'; 110 111 /* 112 Set the date format to a big numeric string. Avoids 113 all NLS issues and saves both the time and date. 114 */ 115 execute immediate 116 'alter session set nls_date_format=''ddmmyyyyhh24miss'' '; 117 118 /* 119 Set up an exception block so that in the event of any 120 error, we can at least reset the date format. 121 */ 122 begin |
接下来，解析并描述这个查询。将G_DESCTBL设置为L_DESCTBL来“重置“全局表；否则，其中会包含前一个DESCRIBE生成的数据，而不只是当前查询生成的数据。一旦完成，再调用DUMP_CTL具体创建控制文件：
| 123 /* 124 Parse and describe the query. We reset the 125 descTbl to an empty table so .count on it 126 will be reliable. 127 */ 128 dbms_sql.parse( g_theCursor, p_query, dbms_sql.native ); 129 g_descTbl := l_descTbl; 130 dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl ); 131 132 /* 133 Create a control file to reload this data 134 into the desired table. 135 */ 136 dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator, 137 p_enclosure, p_terminator ); 138 139 /* 140 Bind every single column to a varchar2(4000). We don't care 141 if we are fetching a number or a date or whatever. 142 Everything can be a string. 143 */ |
现在可以将具体数据转储到磁盘上了。首先将每个列定义为VARCHAR2(4000)来获取数据。所有类型（NUMBER、DATE、RAW）都要转换为VARCHAR2。在此之后，执行查询来准备获取：
| 144 for i in 1 .. l_colCnt loop 145 dbms_sql.define_column( g_theCursor, i, l_columnValue, 4000); 146 end loop; 147 148 /* 149 Run the query - ignore the output of execute. It is only 150 valid when the DML is an insert/update or delete. 151 */ |
现在打开数据文件准备写，从查询获取所有行，并将其打印到数据文件：
| 152 l_cnt := dbms_sql.execute(g_theCursor); 153 154 /* 155 Open the file to write output to and then write the 156 delimited data to it. 157 */ 158 l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w', 159 32760 ); 160 loop 161 exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 ); 162 l_separator := ''; 163 l_line := null; 164 for i in 1 .. l_colCnt loop 165 dbms_sql.column_value( g_theCursor, i, 166 l_columnValue ); 167 l_line := l_line || l_separator || 168 quote( l_columnValue, p_enclosure ); 169 l_separator := p_separator; 170 end loop; 171 l_line := l_line || p_terminator; 172 utl_file.put_line( l_output, l_line ); 173 l_cnt := l_cnt+1; 174 end loop; 175 utl_file.fclose( l_output ); 176 |
最后，将日期格式设置回原来的样子（如果先前的代码由于某种原因失败了，异常块也会做这个工作），并返回：
| 177 /* 178 Now reset the date format and return the number of rows 179 written to the output file. 180 */ 181 execute immediate 182 'alter session set nls_date_format=''' || l_datefmt || ''''; 183 return l_cnt; 184 exception 185 /* 186 In the event of ANY error, reset the data format and 187 re-raise the error. 188 */ 189 when others then 190 execute immediate 191 'alter session set nls_date_format=''' || l_datefmt || ''''; 192 RAISE; 193 end; 194 end run; 195 196 197 end unloader; 198 / Package body created. |
要运行这个代码，可以使用以下命令（要注意，当然以下代码需要你将SCOTT.EMP的SELECT权限授予某个角色，或者直接授予你自己）：
| ops$tkyte@ORA 10G > set serveroutput on
ops$tkyte@ORA 10G > create or replace directory my_dir as '/tmp'; Directory created.
ops$tkyte@ORA 10G > declare 2 l_rows number; 3 begin 4 l_rows := unloader.run 5 ( p_query => 'select * from scott.emp order by empno', 6 p_tname => 'emp', 7 p_mode => 'replace', 8 p_dir => 'MY_DIR', 9 p_filename => 'emp', 10 p_separator => ',', 11 p_enclosure => '"', 12 p_terminator => '~' ); 13 14 dbms_output.put_line( to_char(l_rows) || 15 ' rows extracted to ascii file' ); 16 end; 17 / 14 rows extracted to ascii file PL/SQL procedure successfully completed. |
由此生成的控制文件显示如下（注意，括号里粗体显示的数字并不是真的包括在文件中；加上这些数字只是为了便于引用）：
| load data (1) infile 'emp.dat' "str x'7E 0A '" (2) into table emp (3) replace (4) fields terminated by X' 2c ' enclosed by X'22' (5) ( (6) EMPNO char(44 ), (7) ENAME char(20 ), (8) JOB char(18 ), (9) MGR char(44 ), (10) HIREDATE date 'ddmmyyyyhh24miss' , (11) SAL char(44 ), (12) COMM char(44 ), (13) DEPTNO char(44 ), (14) ) |
关于这个控制文件，要注意以下几点：
q （2）行：使用了SQLLDR的STR特性。可以指定用什么字符或串来结束一个记录。这样就能很容易地加载有内嵌换行符的数据。串x’7E 0A ’只是换行符后面跟一个波浪号“～“。
q （5）行：使用了我们的分隔符和包围符。这里没有使用OPTIONALLY ENCLOSED BY，因为我们将把原数据中包围字符的所有出现都重复两次，再把每个字段括起来。
q （11）行：使用了一个很大的“数值“日期格式。这有两个作用：可以避免与日期有关的所有NLS问题，还可以保留日期字段的时间分量。
从前面的代码生成的原始数据（.dat）文件如下：
| "7369","SMITH","CLERK","7902","17121980000000","800","","20"~ "7499","ALLEN","SALESMAN","7698","20021981000000","1600","300","30"~ "7521","WARD","SALESMAN","7698","22021981000000","1250","500","30"~ "7566","JONES","MANAGER","7839","02041981000000","2975","","20"~ "7654","MARTIN","SALESMAN","7698","28091981000000","1250","1400","30"~ "7698","BLAKE","MANAGER","7839","01051981000000","2850","","30"~ "7782","CLARK","MANAGER","7839","09061981000000","2450","","10"~ "7788","SCOTT","ANALYST","7566","19041987000000","3000","","20"~ "7839","KING","PRESIDENT","","17111981000000","5000","","10"~ "7844","TURNER","SALESMAN","7698","08091981000000","1500","0","30"~ "7876","ADAMS","CLERK","7788","23051987000000","1100","","20"~ "7900","JAMES","CLERK","7698","03121981000000","950","","30"~ "7902","FORD","ANALYST","7566","03121981000000","3000","","20"~ "7934","MILLER","CLERK","7782","23011982000000","1300","","10"~ |
.dat文件中要注意的问题如下：
q 每个字段都用包围字符括起来。
q DATE卸载为很大的数字。
q 这个文件中的数据行按要求以一个～结束。
现在可以使用SQLLDR很容易地重新加载这个数据。你可以向SQLLDR命令行增加你认为合适的选项。
如前所述，卸载包的逻辑可以用多种语言和工具来实现。在Ask Tom网站上，你会看到这个实例不仅用PL/SQL实现（如在此所示），还使用了Pro*C和SQL*Plus脚本来实现。Pro*C是最快的实现，总会写至客户工作站文件系统。PL/SQL是一种很好的通用实现（没有必要在客户工作站上编译和安装），但是总是写至服务器文件系统。SQL*Plus是一个很好的折衷，可以提供不错的性能，而且可以写至客户文件系统。
1.4 数据泵卸载
Oracle9i引入了外部表，作为向数据库中读取数据的一种方法。Oracle 10g 则从另一个方向引入了这个特性，可以使用CREATE TABLE语句创建外部数据，从而由数据库卸载数据。从Oracle 10g 起，这个数据从一种专用二进制格式抽取，这种格式称为数据泵格式（Data Pump format），Oracle提供的EXPDP和IMPDP工具将数据从一个数据库移动另一个数据库所用的就是这种格式。
使用外部表卸载确实相当容易，就像使用CREATE TABLE AS SELECT语句一样简单。首先，需要一个DIRECTORY对象：
| ops$tkyte@ORA10GR1> create or replace directory tmp as '/tmp' 2 / Directory created. |
现在，准备使用一个简单的SELECT语句向这个目录中卸载数据，例如：
| ops$tkyte@ORA10GR1> create table all_objects_unload 2 organization external 3 ( type oracle_datapump 4 default directory TMP 5 location( 'allobjects.dat' ) 6 ) 7 as 8 select 9 * 10 from all_objects 11 / Table created. |
我有意关闭了ALL_OBJECTS视图，因为这是一个相当复杂的视图，有大量的联结和谓词。这个例子显示出，可以使用这个数据泵卸载技术从数据库中抽取任意的数据。我们可以使用谓词或所需的任何技术来抽取一部分数据。
注意 这个例子显示出，我们可以使用这个数据泵卸载技术从数据库中抽取任意的数据。不错，我把这句话又“啰唆“了一遍。从安全的角度看，这样一来，能访问这个信息的人可以很容易地从任何地方”取得“这些信息。对于能够创建DIRECTORY对象并写至这些目录的人，你需要控制这些人的访问，另外对物理服务器有必要的访问权限来得到卸载数据的人的访问也uyao有所控制。
最后一步是把allobjects.dat复制到另一个服务器（可能是一台执行测试的开发主机），并在这个服务器上抽取DDL重建这个表：
| ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'ALL_OBJECTS_UNLOAD' ) 2 from dual; DBMS_METADATA.GET_DDL('TABLE','ALL_OBJECTS_UNLOAD') -------------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."ALL_OBJECTS_UNLOAD" ( "OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(30), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "TMP" LOCATION ( 'allobjects.dat' ) ) |
这样就能很容易地在另一个数据库上加载这个片段信息，因为只需：
| SQL> insert /*+ append */ into some_table select * from all_objects_unload; |
就万事大吉，数据则已经加载。
1.5 小结
在这一章中，我们介绍了数据加载和卸载的许多细节。首先介绍了SQL*Loader（SQLLDR），并分析了加载定界数据、定宽数据、LOB等多种基本技术。我们讨论了将这些知识应用于外部表，这是Oracle9i及以后版本中引入的一个新特性，可以用来取代SQLLDR，不过它还是利用了SQLLDR的一些技巧。
接下来我们讨论了加载的逆过程，即数据卸载，说明了如何以其他工具（如电子表格等）可以使用的某种格式从数据库中取出数据。在讨论中，我们开发了一个PL/SQL实用程序来展示这个过程，它会以SQLLDR友好的格式卸载数据，不过可以很容易地修改这个程序来满足我们的需要。
最后，我们介绍了一个新的Oracle 10g 特性——外部表卸载，利用外部表卸载，可以很容易地从数据库向另一个数据库创建和移动数据片断。
879
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
