一下是代码,新建了一个程序包:
create or replace package expfile
authid current_user
as
/*将查询获得的数据导出到平面文件,创建控制文件,用以重新加载数据到oracle表中
p_query = 查询sql语句,该语句的查询的结果会被卸载到平面文件
p_tname = 需要load数据的表
p_mode = 重新加载的模式replace|append|truncate
p_dir = ctl(控制文件)和dat(数据文件)写入的目录
p_filename = 写入的文件名,后缀名.ctl和.dat分别表示控制文件和数据文件
p_separator = 字段分隔符。默认为一个逗号
p_enclosure = 定义要被分装进字段的内容,比如换行符、制表符
p_terminator = 行结束标记,默认为"|\n"on unix或者"|\r\n".你需要将此重写。
*/
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;
end;
create or replace package body expfile
As
/*
g_theCursor 打开一个游标,并且返回一个整型。
g_descTbl 是一个PL/SQL表,将保存dbms_sql.desc_tab调用的输出
g_nl 换行符,在需要内嵌有换行符的串中会使用这个变量。
*/
g_theCursor integer default dbms_sql.open_cursor;
g_descTbl dbms_sql.desc_tab;
g_nl varchar2(2) default chr(10);
/*函数将字符转换为一个十六进制数*/
function to_hex(p_str in varchar2) return varchar2
is
begin
return to_char(ascii(p_str),'fm0x');
end;
/*对系统平台进行判断,如果是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;
/*
创建一个控制文件来重新加载卸载的数据。
*/
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,'fields terminated by X'''|| To_Hex(P_Separator)||''' enclosed by X '''||
to_hex(p_enclosure)||''' ');
utl_file.put_line(l_output,'(');
/*遍历表的desc*/
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;
/*下面是主函数。*/
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保存到一个变量中,从而在将数据转储到磁盘上时可以把它改为一种保留日期和时间的格式*/
select value
into l_datefmt
From Nls_Session_Parameters
where parameter = 'NLS_DATE_FORMAT';
execute immediate 'alter session set nls_date_format = ''ddmmyyyyhh24miss''';
Begin
/*解析并描述这个查询。将G_DESCTBL 设置为L_DESTBL来重置全局表;否则,其中会包含前DESCRIBE生成的数据
而不只是当前查询生成的数据。一旦完成,再调用DUMP_CTL具体创建控制文件
*/
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);
/*
将每一个column绑定成一个varchar2(4000)。不用关心这个数是什么类型,每一个东西都可以变成string
现在可以具体数据转储到磁盘上。首先将每一个列定义为varchar2(4000)来获取数据。所有类型(number
date row)都要转换为 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 expfile;