再上一篇:15.1 SQL*Loader
上一篇:15.2外部表
主页
下一篇:15.4数据泵卸载
再下一篇:15.5小结
文章列表

15.3平面文件卸载

Oracle 9i 10g编程艺术:深入数据库体系结构

有一件事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@ORA10G> 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@ORA10G> 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

)>0)

if (dbms_utility.get_parameter_value( 'control_files', l_dummy, l_cfiles

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_datef

mt || '''';
192 RAISE;
193 end;
194 end run;
195
196
197 end unloader;
198 /
Package body created.
要运行这个代码,可以使用以下命令(要注意,当然以下代码需要你将SCOTT.EMP的SELECT权限授

予某个角色,或者直接授予你自己):
ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> create or replace directory my_dir as '/tmp'; Directory created.
ops$tkyte@ORA10G> 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'7E0A'" (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)
)
关于这个控制文件,要注意以下几点:
(2)行:使用了SQLLDR的STR特性。可以指定用什么字符或串来结束一个记录。这样就 能很容易地加载有内嵌换行符的数据。串x’7E0A’只是换行符后面跟一个波浪号“~“。
(5)行:使用了我们的分隔符和包围符。这里没有使用OPTIONALLY ENCLOSED BY,因为我 们将把原数据中包围字符的所有出现都重复两次,再把每个字段括起来。
(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文件中要注意的问题如下:
每个字段都用包围字符括起来。 DATE卸载为很大的数字。
这个文件中的数据行按要求以一个~结束。
现在可以使用SQLLDR很容易地重新加载这个数据。你可以向SQLLDR命令行增加你认为合适的选项。 如前所述,卸载包的逻辑可以用多种语言和工具来实现。在Ask Tom网站上,你会看到这个实例不仅
用PL/SQL实现(如在此所示),还使用了Pro*C和SQL*Plus脚本来实现。Pro*C是最快的实现,总会写至 客户工作站文件系统。PL/SQL是一种很好的通用实现(没有必要在客户工作站上编译和安装),但是总是 写至服务器文件系统。SQL*Plus是一个很好的折衷,可以提供不错的性能,而且可以写至客户文件系统。