平面文件卸载
要把数据从一个系统移动到另一个系统,如果没有使用EXP/IMP或EXPDP/IMPDP(用于取代EXP和IMP的新数据泵),平面卸载就很有用.尽管使用EXP(DP)/IMP(DP)可以很好地将数据从一个系统移到另一个系统,但要求两个系统都是Oracle.
jy@JINGYONG> create or replace package unloader 2 authid current_user 3 as 4 /* Funaction run--unloads data from any query into a file 5 and creates a control file to reload that 6 data into another table 7 p_query=SQL query to "unload".May be virtually and query. 8 p_tname=Table to load into.Will be put into control file. 9 p_mode=REPLACE|APPEND|TRUNCATE--how to reload the data 10 p_dir=directory we will write the ctl and dat file to. 11 p_filename=name of file to write to.I will add .ctl and .dat to this 12 name 13 p_separator=field delimiter. I default this to a comma. 14 p_enclosure=what each field will be wrapped in 15 p_terminator=end of line character. We use this so we can unload 16 and reload data with newlines in it. I default to 17 "|\n"(a pipe and a newline together) and "|\r\n" on NT. 18 You need only to override this if you believe your 19 data will have that sequence in it. I ALWAYS add the 20 OS "end of line" marker to this sequence,you should not 21 */ 22 function run(p_query in varchar2, 23 p_tname in varchar2, 24 p_mode in varchar2 default 'REPLACE', 25 p_dir in varchar2, 26 p_filename in varchar2, 27 p_separator in varchar2 default ',', 28 p_enclosure in varchar2 default '"', 29 p_terminator in varchar2 default '|') 30 return number; 31 end; 32 / 程序包已创建。
注意这里使用了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%的情况下它都能解决问题:
create or replace package body unloader
as
g_theCursor integer default dbms_sql.open_cursor;
g_descTbl dbms_sql.desc_tab;
g_nl varchar2(2) default chr(10);
以上是这个包体中使用的一些全局变量.全局游标打开一次,即第一次引用这个包时打开,它会一起打开,直到我们注销.这就 不用每次调用这个包时都要得到一个新游标,从而避免相应的开销.G_DESCTBL是一个PL/SQL表,将保存DBMS_SQL.DESCRIBE调用的输出.G_NL是一个换行符.在需要内嵌有换行符的串中会使用这个变量.我们无需针对Windows调整这个变量,UTL_FILE会看到字符串中的CHR(10),并自动为我们将其转换为一个回车/换行符.
接下来,我们使用了一个很小的便利函数,它能将字符转换为一个十六进制数,为此使用了内置函数:
function to_hex(p_str in varchar2) return varchar2
is
begin
return to_char(ascii(p_str),'fm0x');
end;
最后,我们又创建了另一个便利函数IS_WINDOWS,它会返回TRUE或FALSE,这取决于我们所用的是否是Windows平台,如果在 Windows平台上,行结束符就是一个两字符的串,而大多数其他平台上的行结束符只是单字符.我们使用了内置DBMS_UTILITY函 数:GET_PARAMETER_VALUE,可以用这个函数读取几乎所有参数.我们获取了CONTROL_FILES参数,并查找其中是否存在\,如果 有,则说明在Windows平台上:
function is_windows return boolean
is
l_cfiles varchar2(4000);
l_dummy number;
begin
if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then
return instr(l_cfiles,'\')>0;
else
return FALSE;
end if;
end;
注意:IS_WINDOWS函数依赖于CONTROL_FILES参数中使用了\.要记住,其中也有可能使用/,但这极为少见.
下面的过程会创建一个控制文件来重新加载卸载的数据,这里使用了DBMS_SQL.DESCRIBE_COLUMN生成的DESCRIBE表.它会为我们处理有关操作系统的细节,如操作系统是否使用回车/换行符(用于STR属性):
procedure dump_ctl(
p_dir in varchar2,
p_filename in varchar2,
p_tname in varchar2,
p_mode in varchar2,
p_separator in varchar2,
p_enclosure in varchar2,
p_terminator in varchar2)
is
l_output utl_file.file_type;
l_sep varchar2(5);
l_str varchar2(5):=chr(10);
begin
if(is_windows) then
l_str:=chr(13)||chr(10);
end if;
l_output:=utl_file.fopen(p_dir,p_filename|\'.ctl','w');
utl_file.put_line(l_output,'load data');
utl_file.put_line( l_output, 'infile ''' ||
p_filename || '.dat'' "str x''' ||
utl_raw.cast_to_raw( p_terminator ||
l_str ) || '''"' );
utl_file.put_line( l_output, 'into table ' || p_tname );
utl_file.put_line( l_output, p_mode );
utl_file.put_line( l_output, 'fields terminated by X''' ||
to_hex(p_separator) ||
''' enclosed by X''' ||
to_hex(p_enclosure) || ''' ' );
utl_file.put_line( l_output, '(' );
for i in 1 .. g_descTbl.count loop
if ( g_descTbl(i).col_type = 12 ) then
utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
' date ''ddmmyyyyhh24miss'' ');
else
utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
' char(' ||
to_char(g_descTbl(i).col_max_len*2) ||' )' );
end if;
l_sep := ','||g_nl ;
end loop;
utl_file.put_line( l_output, g_nl || ')' );
utl_file.fclose( l_output );
end;
这是一个简单的函数,会返回一个加引号的串(使用所选择的包围字符作为引号);注意,串不只是包含字符,倘若串中还存在包围字符,还会把包围字符重复两次,从而保留这些包围字符:
function quote(p_str in varchar2,p_enclosure in varchar2)
return varchar2
is
begin
return p_enclosure||
replace(p_str,p_enclosure,p_enclosure||p_enclosure)||
p_enclosure;
end;
下面是主函数RUN.因为这个函数相当大,会列出函数的解释:
function run(p_query in varchar2,
p_tname in varchar2,
p_mode in varchar2 default 'REPLACE',
p_dir in varchar2,
p_filename in varchar2,
p_separator in varchar2 default ',',
p_enclosure in varchar2 default '"',
p_terminator in varchar2 default '|')
return number
is
l_output utl_file.file_type;
l_columnValue varchar2(4000);
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_line long;
l_datefmt varchar2(255);
l_descTbl dbms_sql.desc_tab;
begin
我们将NLS_DATE_FORMAT保存到一个变量中,从而在将数据转储到磁盘上时可以把它改为一种保留日期和时间的格式.采用这种方式,我们会保留日期的时间分量.然后建立一个异常块,从而在接收到错误时重置NLS_DATE_FORMAT:
select value into l_datefmt
from nls_session_parameters where parameter='NLS_DATE_FORMAT';
/* 设置日期格式为是一个大数字字符串避免所有的NLS问题并保留时间和日期 */
execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';
/* 设置一个异常块在出现任何错误时重新设置日期格式 */
接下来,解析并描述这个查询.将G_DESCTBL设置为L_DESCTBL来重置全局表;否则,其中会包含前一个DESCRIBE生成的数据,而不只是当前查询生成的数据.一旦完成,再调用DUMP_CTL具体创建控制文件:
begin
/*
解析和描述这个查询.将重设descTbl为了一个空表
*/
dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
g_descTbl := l_descTbl;
dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
/*
创建一个控制文件来重新加载数据到你所期望的表中
*/
dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
p_enclosure, p_terminator );
/*
绑定每一个列转换为一个长度为4000字符串.我们不在乎我们获取是数字还是日期,因为它们都可以
看成是一个字符串
*/
现在可以将具体数据转储到磁盘上了.首先将每个列定义为VARCHAR2(4000)来获取数据.所有类型(NUMBER,DATE,RAW)都要 转换为VARCHAR2.在此之后,执行查询来准备获取:
for i in 1 .. l_colCnt loop
dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);
end loop;
/*
运行这个查询--忽略执行的输出,它只是来验证一个插入/更新或删除操作
*/
现在打开数据文件准备写,从查询获取所有行,并将其打印到数据文件:
l_cnt := dbms_sql.execute(g_theCursor);
/*
打开文件然后将带有分隔符的数据写入到文件中
*/
l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );
loop
exit when ( dbms_sql.fetch_rows(g_theCursor) < = 0 );
l_separator := '';
l_line := null;
for i in 1 .. l_colCnt loop
dbms_sql.column_value( g_theCursor, i,l_columnValue );
l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
l_separator := p_separator;
end loop;
l_line := l_line || p_terminator;
utl_file.put_line( l_output, l_line );
l_cnt := l_cnt+1;
end loop;
utl_file.fclose( l_output );
最后,将日期格式设置回原来的样子(如果先前的代码由于某种原因失败了,异常块也会做这个工作),并返回:
/*
现在重新设置日期格式并将返回的行数据写入到输出文件中
*/
execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
return l_cnt;
exception
/*
如果发现任何错误会重新设置日期并重新触发错误
*/
when others then
execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
RAISE;
end;
end run;
end unloader;
执行完整的编译
SQL> create or replace package body unloader
2 as
3 g_theCursor integer default dbms_sql.open_cursor;
4 g_descTbl dbms_sql.desc_tab;
5 g_nl varchar2(2) default chr(10);
6
7 function to_hex(p_str in varchar2) return varchar2
8 is
9 begin
10 return to_char(ascii(p_str),'fm0x');
11 end;
12
13 function is_windows return boolean
14 is
15 l_cfiles varchar2(4000);
16 l_dummy number;
17 begin
18 if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then
19 return instr(l_cfiles,'\')>0;
20 else
21 return FALSE;
22 end if;
23 end;
24
25 procedure dump_ctl(
26 p_dir in varchar2,
27 p_filename in varchar2,
28 p_tname in varchar2,
29 p_mode in varchar2,
30 p_separator in varchar2,
31 p_enclosure in varchar2,
32 p_terminator in varchar2)
33 is
34 l_output utl_file.file_type;
35 l_sep varchar2(5);
36 l_str varchar2(5):=chr(10);
37 begin
38 if(is_windows) then
39 l_str:=chr(13)||chr(10);
40 end if;
41 l_output:=utl_file.fopen(p_dir,p_filename||'.ctl','w');
42 utl_file.put_line(l_output,'load data');
43 utl_file.put_line( l_output, 'infile ''' ||
44 p_filename || '.dat'' "str x''' ||
45 utl_raw.cast_to_raw( p_terminator ||
46 l_str ) || '''"' );
47 utl_file.put_line( l_output, 'into table ' || p_tname );
48 utl_file.put_line( l_output, p_mode );
49 utl_file.put_line( l_output, 'fields terminated by X''' ||
50 to_hex(p_separator) ||
51 ''' enclosed by X''' ||
52 to_hex(p_enclosure) || ''' ' );
53 utl_file.put_line( l_output, '(' );
54
55 for i in 1 .. g_descTbl.count loop
56 if ( g_descTbl(i).col_type = 12 ) then
57 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
58 ' date ''ddmmyyyyhh24miss'' ');
59 else
60 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
61 ' char(' ||
62 to_char(g_descTbl(i).col_max_len*2) ||' )' );
63 end if;
64 l_sep := ','||g_nl ;
65 end loop;
66 utl_file.put_line( l_output, g_nl || ')' );
67 utl_file.fclose( l_output );
68 end;
69
70 function quote(p_str in varchar2,p_enclosure in varchar2)
71 return varchar2
72 is
73 begin
74 return p_enclosure||
75 replace(p_str,p_enclosure,p_enclosure||p_enclosure)||
76 p_enclosure;
77 end;
78
79
80 function run(p_query in varchar2,
81 p_tname in varchar2,
82 p_mode in varchar2 default 'REPLACE',
83 p_dir in varchar2,
84 p_filename in varchar2,
85 p_separator in varchar2 default ',',
86 p_enclosure in varchar2 default '"',
87 p_terminator in varchar2 default '|')
88 return number
89 is
90 l_output utl_file.file_type;
91 l_columnValue varchar2(4000);
92 l_colCnt number default 0;
93 l_separator varchar2(10) default '';
94 l_cnt number default 0;
95 l_line long;
96 l_datefmt varchar2(255);
97 l_descTbl dbms_sql.desc_tab;
98 begin
99 select value into l_datefmt
100 from nls_session_parameters where parameter='NLS_DATE_FORMAT';
101 /* 设置日期格式为是一个大数字字符串避免所有的NLS问题并保留时间和日期 */
102 execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';
103 /* 设置一个异常块在出现任何错误时重新设置日期格式 */
104 begin
105 /*
106 解析和描述这个查询.将重设descTbl为了一个空表
107 */
108 dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
109 g_descTbl := l_descTbl;
110 dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );
111 /*
112 创建一个控制文件来重新加载数据到你所期望的表中
113 */
114 dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
115 p_enclosure, p_terminator );
116 /*
117 绑定每一个列转换为一个长度为4000字符串.我们不在乎我们获取是数字还是日期,因为它们都可以
118 看成是一个字符串
119 */
120 for i in 1 .. l_colCnt loop
121 dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);
122 end loop;
123 /*
124 运行这个查询--忽略执行的输出,它只是来验证一个插入/更新或删除操作
125 */
126 l_cnt := dbms_sql.execute(g_theCursor);
127 /*
128 打开文件然后将带有分隔符的数据写入到文件中
129 */
130 l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );
131 loop
132 exit when ( dbms_sql.fetch_rows(g_theCursor) < = 0 );
133 l_separator := '';
134 l_line := null;
135 for i in 1 .. l_colCnt loop
136 dbms_sql.column_value( g_theCursor, i,l_columnValue );
137 l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
138 l_separator := p_separator;
139 end loop;
140 l_line := l_line || p_terminator;
141 utl_file.put_line( l_output, l_line );
142 l_cnt := l_cnt+1;
143 end loop;
144 utl_file.fclose( l_output );
145 /*
146 现在重新设置日期格式并将返回的行数据写入到输出文件中
147 */
148 execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
149 return l_cnt;
150 exception
151 /*
152 如果发现任何错误会重新设置日期并重新触发错误
153 */
154 when others then
155 execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';
156 RAISE;
157 end;
158 end run;
159 end unloader;
160 /
Package body created
要运行这个代码,可以使用以下命令(要注意,当然以下代码需要你将SCOTT.EMP的SELECT权限授予某个角色,或者直接授予你 自己):
jy@JINGYONG> set serveroutput on jy@JINGYONG> create or replace directory dir1 as '/home/oracle'; 目录已创建。 jy@JINGYONG> 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=>'DIR1', 9 p_filename=>'emp', 10 p_separator=>',', 11 p_enclosure=>'"', 12 p_terminator=>'~'); 13 dbms_output.put_line(to_char(l_rows)||' rows extracted to ascii file'); 14 end; 15 / 14 rows extracted to ascii file PL/SQL 过程已成功完成。
由此生成的emp.ctl控制文件显示如下(注意,括号里粗体显示的数字并不是真的包括在文件中:加上这些数字只是为了便于引 用):
[oracle@jy ~]$ cat emp.ctl load data infile 'emp.dat' "str x'7E0A'" into table emp replace fields terminated by X'2c' enclosed by X'22' ( EMPNO char(44 ), ENAME char(20 ), JOB char(18 ), MGR char(44 ), HIREDATE date 'ddmmyyyyhh24miss' , SAL char(44 ), COMM char(44 ), DEPTNO char(44 ) )
关于这个控制文件,要注意以下几点:
第2行:使用了SQLLDR的STR特性.可以指定用什么字符或串来结束一个记录.这样就能很容易地加载有内嵌换行符的数据.串x ‘7E0A’只是换行符后面跟一个波浪号”~”.
第5行:使用了我们的分隔符和包围符.这里没有使用OPTIONALLY ENCLOSED BY,因为我们将把原数据中包围字符的所有出现都 重复两次,再把每个字段括起来.
第11行:使用了一个很大的”数值”日期格式.这有两个作用:可以避免与日期有关的所有NLS问题,还可以保留日期字段的时间分量.
从前面的代码生成的原始数据（emp.dat）文件如下：
[oracle@jy ~]$ cat emp.dat "7369","SMITH","CLERK","7902","19801217000000","800","","20"~ "7499","ALLEN","SALESMAN","7698","19810220000000","1600","300","30"~ "7521","WARD","SALESMAN","7698","19810222000000","1250","500","30"~ "7566","JONES","MANAGER","7839","19810402000000","2975","","20"~ "7654","MARTIN","SALESMAN","7698","19810928000000","1250","1400","30"~ "7698","BLAKE","MANAGER","7839","19810501000000","2850","","30"~ "7782","CLARK","MANAGER","7839","19810609000000","2450","","10"~ "7788","SCOTT","ANALYST","7566","19870419000000","3000","","20"~ "7839","KING","PRESIDENT","","19811117000000","5000","","10"~ "7844","TURNER","SALESMAN","7698","19810908000000","1500","0","30"~ "7876","ADAMS","CLERK","7788","19870523000000","1100","","20"~ "7900","JAMES","CLERK","7698","19811203000000","950","","30"~ "7902","FORD","ANALYST","7566","19811203000000","3000","","20"~ "7934","MILLER","CLERK","7782","19820123000000","1300","","10"~
emp.dat文件中要注意的问题如下:
每个字段都用包围字符括起来.
DATE卸载为很大的数字>
这个文件中的数据行按要求以一个～结束>
现在可以使用SQLLDR很容易地重新加载这个数据,你可以向SQLLDR命令行增加你认为合适的选项.